Splunk Search

How to get the top 10 values using timechart?

dbcase
Motivator

Hi,

I have this query and it works just fine

index=blah1 OR index=blah2 OR index=blah3 host=*media* "/fileUpload/image" |rex "(?<ip>(?:[0-9]{1,3}\.){3}[0-9]{1,3})[\s,]"|eval index=if(index="blah3","beta",index)|eval ii=index+" - "+ip|timechart  usenull=f count by ii |sort -count

What I'd like to have the time chart do is capture the top 10 ii values from the eval command.

Any thoughts?

1 Solution

mayurr98
SplunkTrust
SplunkTrust

hey you can try something like this

 index=blah1 OR index=blah2 OR index=blah3 host=*media* "/fileUpload/image" |rex "(?<ip>(?:[0-9]{1,3}\.){3}[0-9]{1,3})[\s,]"|eval index=if(index="blah3","beta",index)|eval ii=index+" - "+ip|timechart  usenull=f count by ii where max in top10 

max in top10 means top 10 ii values
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Timechart#Where_clause_Examples

let me know if this helps!

View solution in original post

madrum
Explorer

I have over 100 values and the value for the "OTHER" data point was overshadowing the data I wanted to see. I was able to hide the data with a hack that set the value for OTHER to 0 and hide "OTHER" from the legend by renaming it to underscore which will not be displayed.

I copied the answer by mayurr98 and added the hack to the end.

index=blah1 OR index=blah2 OR index=blah3 host=media "/fileUpload/image" |rex "(?(?:[0-9]{1,3}.){3}[0-9]{1,3})[\s,]"|eval index=if(index="blah3","beta",index)|eval ii=index+" - "+ip|timechart usenull=f count by ii where max in top10
|eval OTHER = 0
|rename OTHER = "_"

rey123
Explorer

It is exactly as @madrum mentioned. For me too, value for "NULL" and "OTHER" always overshadows the data. This is surprising because these two categories never overshadow the values when executed as a stats (instead of a timechart) command.

Additionally, '..max in topN' did NOT restrict my timechart unique values to N. It didn't seem to have any effect on teh command AT ALL. Not sure why.

0 Karma

to4kawa
SplunkTrust
SplunkTrust
| makeresults count=2
| streamstats count
| eval _time=relative_time(_time,(-1*count)."d@d") 
| makecontinuous _time span=1min
| eval counts=random() % 100, host=mvindex(split("ABCDEFGHIJKLMNOPQRSTUVWXYZ",""),random() % 26)
| timechart cont=f max(counts) by host where max in top26

in default, timechart displays 5 fields and OTHER.
but as this result, where handles to display the results.

0 Karma

rey123
Explorer

i) "in default, timechart displays 5 fields and OTHER". Would you be able to point to the Splunk documentation where the limit of '5' fields is mentioned? Because I couldn't find this in the documentation.

ii) What about "NULL" and "null". Why do those appear? When I follow @madrum's recommendation above, I do not see any nulls. Not sure if NULLs gets added as extra counts if not removed, or filter out actual results, if removed.

iii) The thing to remember with the 'where' clause is that, the count of that parameter is across the ENTIRE series of the timechart and not across INDIVIDUAL time epocs. So, for example, taking your example above, if one time-interval had 40 'max' values, with only 10 of them falling within the top 26 values in the series, this 40-'max'-value-time-interval will show only those 10 values. It will not show 26 out of the 40 values. So, the '26' is a TOTAL number and applies ACROSS the series.

0 Karma

to4kawa
SplunkTrust
SplunkTrust
  1. the comparison | timechart cont=f max(counts) by host where max in top26 and | timechart cont=f max(counts) by host
  2. In your search, if event don't have the searching field , null is appear. If you use stats count (event count) , the result will be wrong result.
  3. ENTIRE series yes. the requirement. not across INDIVIDUAL time epochs. why do you use timechart where for this? your usage is wrong.
0 Karma

mayurr98
SplunkTrust
SplunkTrust

hey you can try something like this

 index=blah1 OR index=blah2 OR index=blah3 host=*media* "/fileUpload/image" |rex "(?<ip>(?:[0-9]{1,3}\.){3}[0-9]{1,3})[\s,]"|eval index=if(index="blah3","beta",index)|eval ii=index+" - "+ip|timechart  usenull=f count by ii where max in top10 

max in top10 means top 10 ii values
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Timechart#Where_clause_Examples

let me know if this helps!

View solution in original post

ppablo
Community Manager
Community Manager

Just to add to this, here's the link to the specific portion in the documentation that covers the where in examples which is much higher up on that page:
http://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Timechart#where_clause

0 Karma

elliotproebstel
Champion

Ditto what @micahkemp said. Neat trick.

0 Karma

dbcase
Motivator

ooooohhhh now that is really slick! Thank you mayurr98!

micahkemp
Champion

Nice! I was unaware of the where in functionality in timechart.

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!