Charting top 5 events as a percentage of all events
I need to add a pie chart to a dashboard that shows the top 5 event ID's from my Windows servers as a percentage of all the events(I don't want every event ID in the chart).
Pulling the top 5 events is simple enough with this query:
_sourceCategory=CategoryICareAbout
| count event_id
| sort _count desc
| limit 5
However, I need to somehow add 1 additional count to the result which is the count of all event ID's that are NOT in the output of my top 5 query above. I can't figure out how to get the count of all event ID's that are NOT in the output of that query and still get all the results in query output for the chart.
The desired output would be as follows:
# event_id _count
1 4624 4,003
2 4627 3,936
3 4634 4,033
4 4674 8,386
5 4688 2,015
6 Other 100,000
Any help would be appreciated.
-
Official comment
try this out, not sure if it's the most efficient way, but worked for me, of course using a different source category matching our test data.
_sourceCategory=CategoryICareAbout
| count event_id
| total(_count)
| sort _count
| limit 6
| 1 as rownum
| accum rownum
| _count/_total as percent
| total(percent) as totalpercent
| 1 - totalpercent as remainingpercent
| if ((_accum=6), "others", event_id) as event_id
| if ((_accum=6), remainingpercent+percent, percent) as percent
| fields event_id, percentComment actions -
A version that does counts as well as %, as I just realized that was desired:
_sourceCategory=CategoryICareAbout
| count as occurrences by event_id
| total(occurrences) as totaloccurrences
| sort occurrences
| limit 6
| total(occurrences) as top6occurrences
| 1 as rownum
| accum rownum
| if ((_accum=6), "others", event_id) as event_id
| if ((_accum=6), totaloccurrences-top6occurrences+occurrences, occurrences) as occurrences
| fields event_id, occurrencesSample Pie Results:
Please sign in to leave a comment.
Comments
4 comments