topic Re: Keep Greatest 5 Counts by Date in Splunk Search

Keep Greatest 5 Counts by Date

ErikaE — Tue, 30 May 2017 13:40:53 GMT

I'm counting exceptions over a 24 hour period. My search looks like this:

index=exceptionsindex 
| bin _time span=24h
| stats sum(Exception) as TotalE by Area, _time 
| sort by _time -TotalE

The output of this search is ~40 events for each day, one for each Area, with a sum of TotalE.

What I'd like to be able to do is keep only the top 5 offending areas per day. Ideally something like | head 5 TotalE by _time , but head doesn't accept arguments like that.

Thoughts?

Re: Keep Greatest 5 Counts by Date

cmerriman — Tue, 30 May 2017 13:43:57 GMT

EDITED:

add |streamstats count by _time |search count<6
and see if that works.

Re: Keep Greatest 5 Counts by Date

ErikaE — Tue, 30 May 2017 13:59:43 GMT

Does not appear to work. After running streamstats, I get a table with count=1 for each row.

Re: Keep Greatest 5 Counts by Date

cmerriman — Tue, 30 May 2017 14:01:34 GMT

try taking _time out of the streamstats. i thought with that being binned by every 24 hours, the streamstats would work.

if that doesn't work, can i get a sample of what the data looks like?

Re: Keep Greatest 5 Counts by Date

ErikaE — Tue, 30 May 2017 14:18:08 GMT

It had the same output.

The data table looks like this:

I'd like to "keep" the 5 areas with the Highest TotalE by date.

Area TotalE _time
3  15  t1 
5  14  t1
6  12  t1
2  11  t1
4  10  t1
9  9   t1
1  8   t1 
3  17  t2 
5  13  t2
6  11  t2 
2  10  t2 
4  9   t2 
1  8   t2 
8  4   t2 
7  2   t2

Re: Keep Greatest 5 Counts by Date

DalJeanis — Tue, 30 May 2017 14:23:06 GMT

@cmerriman - You left the Area in, so all counts will be 1. Also, just on general principles, always rename count to something else.

| streamstats count as daycount by _time | search daycount<6

Re: Keep Greatest 5 Counts by Date

cmerriman — Tue, 30 May 2017 14:29:56 GMT

sorry, look at my edited syntax in original answer. i misunderstood question. with provided data, using this syntax:

|makeresults|eval data=" Area=3 TotalE=15 _time=t1, Area=5 TotalE=14 _time=t1, Area=6 TotalE=12 _time=t1, Area=2 TotalE=11 _time=t1, Area=4 TotalE=10 _time=t1, Area=9 TotalE=9 _time=t1, Area=1 TotalE=8 _time=t1, Area=3 TotalE=17 _time=t2, Area=5 TotalE=13 _time=t2, Area=6 TotalE=11 _time=t2, Area=2 TotalE=10 _time=t2, Area=4 TotalE=9 _time=t2, Area=1 TotalE=8 _time=t2, Area=8 TotalE=4 _time=t2, Area=7 TotalE=2 _time=t2"|makemv data delim=","|mvexpand data| rename data as _raw|kv|fields Area TotalE time|fields - _raw _time|sort  time  -TotalE|streamstats count by time|search count<6

my results are:

Area TotalE time count 3 15 t1 1
5 14 t1 2 6 12 t1 3 2 11 t1 4
4 10 t1 5 3 17 t2 1 5 13 t2 2
6 11 t2 3 2 10 t2 4 4 9 t2 5

Re: Keep Greatest 5 Counts by Date

woodcock — Tue, 30 May 2017 16:10:42 GMT

Like this, with the dedup command:

index=exceptionsindex 
| bin _time span=24h
| stats sum(Exception) AS TotalE BY Area _time 
| sort _time -TotalE
| dedup 5 Date

Re: Keep Greatest 5 Counts by Date

ErikaE — Tue, 30 May 2017 18:09:47 GMT

I should have thought of that. Thanks!