Splunk Search

Keep Greatest 5 Counts by Date

ErikaE
Communicator

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?

Tags (3)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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

ErikaE
Communicator

I should have thought of that. Thanks!

0 Karma

cmerriman
Super Champion

EDITED:

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

ErikaE
Communicator

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

0 Karma

cmerriman
Super Champion

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?

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@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
0 Karma

ErikaE
Communicator

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
0 Karma

cmerriman
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...