Splunk Search

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

horsefez
SplunkTrust
SplunkTrust

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.

alt text

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

0 Karma
1 Solution

lguinn2
Legend

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.

View solution in original post

twinspop
Influencer

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.

0 Karma

horsefez
SplunkTrust
SplunkTrust

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

0 Karma

lguinn2
Legend

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
SplunkTrust
SplunkTrust

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.

0 Karma

lguinn2
Legend

Thanks - let's just hope it works!

0 Karma

horsefez
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...