Splunk Search

How to sort a chart based on a sum?

jeck11
Path Finder

I dump Splunk daily indexing into a summary index for long term retention and quicker searching. But now I'm trying to chart the data and I'm stuck trying to get the summed data to sort properly. Not a huge deal but does make it more visually appealing.

Here's my search:
index=corp_splunk_license_details day="12-11-2019" | eval Volume_gb = round(volume_gb,3) | chart sum(Volume_gb) as "Amount Indexed (GB)" by day, orig_index

And here are my results:
unsorted result chart

Here is an example of the raw events in the index as well:

12/11/2019 00:00:00 -0500, search_name=corp_splunk_license_index_per_day, search_now=1576130400.000, info_min_time=1576040400.000, info_max_time=1576126800.000, info_search_time=1576131051.720, day="12-11-2019", orig_index=index1, volume_gb="0.21123013552278280000000"

I would really like to have the chart sorted from largest to smallest.

Thanks for the help as usual!

0 Karma
1 Solution

oscar84x
Contributor

Do you need the by day clause if you're already filtering by day in the search? Are you doing that for the different bar colors?
If you remove it and use the sort command it should sort the bars. I think that second clause in your stats sum() is preventing the sorting. Like this:

 index=corp_splunk_license_details day="12-11-2019" 
| eval Volume_gb = round(volume_gb,3)  
| chart sum(Volume_gb) as "Amount Indexed (GB)" by orig_index  
| sort  "Amount Indexed (GB)"

View solution in original post

0 Karma

oscar84x
Contributor

Do you need the by day clause if you're already filtering by day in the search? Are you doing that for the different bar colors?
If you remove it and use the sort command it should sort the bars. I think that second clause in your stats sum() is preventing the sorting. Like this:

 index=corp_splunk_license_details day="12-11-2019" 
| eval Volume_gb = round(volume_gb,3)  
| chart sum(Volume_gb) as "Amount Indexed (GB)" by orig_index  
| sort  "Amount Indexed (GB)"
0 Karma

oscar84x
Contributor

@jeck11 Thank you.

0 Karma

jeck11
Path Finder

@oscar84x - no, thank you. Sorry for the confusion.

0 Karma

vnravikumar
Champion

Hi

Try this

index=corp_splunk_license_details day="12-11-2019" | eval Volume_gb = round(volume_gb,3)  
| chart sum(Volume_gb) as "Amount Indexed (GB)" by day, orig_index  | addtotals 
| sort Total 
| fields - Total

jeck11
Path Finder

Nope. That didn't appear to change anything.

0 Karma

oscar84x
Contributor

Do you need the by day clause if you're already filtering by day in the search? Are you doing that for the different bar colors?
If you remove it and use the sort command it should sort the bars. I think that second clause in your stats sum() is preventing the sorting. Like this:

 index=corp_splunk_license_details day="12-11-2019" 
| eval Volume_gb = round(volume_gb,3)  
| chart sum(Volume_gb) as "Amount Indexed (GB)" by orig_index  
| sort  "Amount Indexed (GB)"

jeck11
Path Finder

Perfect! TY.

The color-coding is nice for quickly identifying which index is which but using your code sorted it. The only tweak I made was to reverse it.

  index=corp_splunk_license_details day="12-11-2019" 
 | eval Volume_gb = round(volume_gb,3)  
 | chart sum(Volume_gb) as "Amount Indexed (GB)" by orig_index  
 | sort  - "Amount Indexed (GB)"
0 Karma

oscar84x
Contributor

Great! Just realized that I responded under the wrong answer. Could you please mark my answer below as the right one?

0 Karma

oscar84x
Contributor

Did you try:

query...
| sort "Amount Indexed (GB)"
0 Karma

jeck11
Path Finder

I've tried a few things but none have worked out so far. I did just try your solution as well and couldn't get it to sort either.

index=corp_splunk_license_details day="12-11-2019" | eval Volume_gb = round(volume_gb,3)  | chart sum(Volume_gb) as "Amount Indexed (GB)" by day, orig_index  | sort "Amount Indexed (GB)"

and

index=corp_splunk_license_details day="12-11-2019" | eval Volume_gb = round(volume_gb,3)  | chart sum(Volume_gb) as "Amount Indexed (GB)" by day, orig_index  | sort "Volume_gb"
0 Karma

oscar84x
Contributor

@jeck11 Great! Just realized that I responded under the wrong answer. Could you please mark this as the correct answer? Thank you.

0 Karma

oscar84x
Contributor

@jeck11 Hi. Could you please correct the right answer?
Thank you.

0 Karma

jeck11
Path Finder

Hi @oscar84x - I've been trying to change it. How can I unselect the other one and switch it to yours?

vnravikumar
Champion

Hi

Unaccept my answer and accept his answer 🙂
@oscar84x - update/move your comments to answer

0 Karma

oscar84x
Contributor

@jeck11 @vnravikumar haha Sorry for the confusion. It had already been sorted out by one of the admins and you actually just undid it. I'm just going to add it as a separate answer and if you can mark that one as correct that would be great. I won't pressure you.

Thank you.

0 Karma

oscar84x
Contributor

Do you need the by day clause if you're already filtering by day in the search? Are you doing that for the different bar colors?
If you remove it and use the sort command it should sort the bars. I think that second clause in your stats sum() is preventing the sorting. Like this:

 index=corp_splunk_license_details day="12-11-2019" 
| eval Volume_gb = round(volume_gb,3)  
| chart sum(Volume_gb) as "Amount Indexed (GB)" by orig_index  
| sort  "Amount Indexed (GB)"
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

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

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...