How to sort a chart based on a sum?

[jeck11]

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:

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!

[oscar84x · Accepted solution]

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)"

[oscar84x]

@jeck11 Thank you.

[jeck11]

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

[vnravikumar]

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]

Nope. That didn't appear to change anything.

[oscar84x]

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]

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)"

[oscar84x]

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

[oscar84x]

Did you try:

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

[jeck11]

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"

[oscar84x]

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

[oscar84x]

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

[jeck11]

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

[vnravikumar]

Hi

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

[oscar84x]

@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.

[oscar84x]

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)"