Charting top 5 events as a percentage of all events

Follow

Nicholas Sandmann

• April 25, 2019 16:30

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.

0

• Official comment

  

  Matt Sullivan

  • April 26, 2019 20:01
  • Edited

  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, percent

  Comment actions Permalink
• 

  Matt Sullivan

  • April 26, 2019 19:55

  hopefully no one used the first version I posted above. Just edited to replace the 2nd to last line as follows:

  | if ((_accum=6), remainingpercent+percent, percent) as percent

  1

  Comment actions Permalink
• 

  Matt Sullivan

  • April 26, 2019 20:13

  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, occurrences

  Sample Pie Results:

  

   

  1

  Comment actions Permalink
• 

  Nicholas Sandmann

  • April 29, 2019 19:53

  Thank you!

   

  Actually the 1st one works fine for my needs.  Relying on Sumo's charting functionality to provide the percentages is fine for my needs since this is just needed for a dashboard. 

  Actually calc'ing the percentages in the raw data is nice to have too though!

  0

  Comment actions Permalink

Please sign in to leave a comment.