How do I sort bars in descending order based on count in my timechart?

[horsefez]

Hello fellow splunkers,

I'm currently charting around with webserver access logs.

My current search string looks like this:

index="webservers" sourcetype="access_combined_wcookie" | timechart span=1d count by referer_domain

Thanks to the amazing splunkengine, this graph gets generated. Which is pretty nice.

The visualization would be even nicer, if I would be able to sort the bars in a descending order based on the count. (red bar first, etc.)

Any suggestions on how to pull that off would be highly apprechiated! 🙂

Regards,
pyro_wood

[twinspop]

Is timechart really what you want? In the example above, you only have a single day's data. Maybe stats would suit you better? Then it's just a simple matter of using sort:

index="webservers" sourcetype="access_combined_cookie" | 
stats count by referer_domain |
sort - count

I'm not sure there's a way to sort each of timechart's time bins contents individually. For readability's sake, when using multiple series in timechart, maybe use the stack option under the formatting menu.

[horsefez]

Sorry, but this is only an example... there are of course more days of data

[lguinn2 · Accepted solution]

Here is the question: what if the red bar is highest in the first time period, but lowest in the second time period? Whatever sort criteria you choose, it will sort the same across all time periods. You can do this

index="webservers" sourcetype="access_combined_wcookie" 
| bin _time span=1d
| chart count by referer_domain _time
| addtotals 
| sort -Total
| fields - Total
| untable referer_domain  _time count
| xyseries _time referer_domain count

This calculates the total of of all the counts by referer_domain, and sorts them in descending order by count (with the largest referer_domain first). You have to flip the table around a bit to do that, which is why I used chart instead of timechart. And then you have to flip the table around a bit more so that it looks like a timechart in the end.
You would think that the transpose command would work here as well, but I haven't tried it and it appears to have limits.

[horsefez]

Wow, this looks like a sophisticated, highly amazing searchcommand. I'll try it out. Big thanks!
I would've never been able to come up with this one.

[lguinn2]

Thanks - let's just hope it works!

[horsefez]

It chart did not sort itself in descending order every time, but its a good start for me to improve this search further.
Thanks!

[somesoni2]

Do you run the report just one day OR multiple day? Problem is that you can't be sure if a referer_domain with max count on day1 will have the max count on day2.