topic Re: How to sort a chart based on a sum? in Splunk Search

How to sort a chart based on a sum?

jeck11 — Wed, 30 Sep 2020 03:19:06 GMT

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!

Re: How to sort a chart based on a sum?

oscar84x — Thu, 12 Dec 2019 14:55:41 GMT

Did you try:

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

Re: How to sort a chart based on a sum?

jeck11 — Thu, 12 Dec 2019 15:13:31 GMT

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"

Re: How to sort a chart based on a sum?

vnravikumar — Thu, 12 Dec 2019 15:45:43 GMT

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

Re: How to sort a chart based on a sum?

jeck11 — Thu, 12 Dec 2019 15:50:12 GMT

Nope. That didn't appear to change anything.

Re: How to sort a chart based on a sum?

oscar84x — Thu, 12 Dec 2019 16:33:28 GMT

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

Re: How to sort a chart based on a sum?

jeck11 — Thu, 12 Dec 2019 16:43:40 GMT

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

Re: How to sort a chart based on a sum?

oscar84x — Thu, 12 Dec 2019 16:46:10 GMT

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

Re: How to sort a chart based on a sum?

oscar84x — Thu, 12 Dec 2019 16:46:41 GMT

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

Re: How to sort a chart based on a sum?

oscar84x — Thu, 12 Dec 2019 16:49:19 GMT

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

Re: How to sort a chart based on a sum?

oscar84x — Fri, 13 Dec 2019 14:27:01 GMT

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

Re: How to sort a chart based on a sum?

jeck11 — Fri, 13 Dec 2019 14:45:08 GMT

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

Re: How to sort a chart based on a sum?

vnravikumar — Fri, 13 Dec 2019 14:50:40 GMT

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

Re: How to sort a chart based on a sum?

oscar84x — Fri, 13 Dec 2019 15:05:59 GMT

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

Re: How to sort a chart based on a sum?

oscar84x — Fri, 13 Dec 2019 15:06:24 GMT

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

Re: How to sort a chart based on a sum?

oscar84x — Fri, 13 Dec 2019 15:40:31 GMT

@jeck11 Thank you.

Re: How to sort a chart based on a sum?

jeck11 — Fri, 13 Dec 2019 15:51:23 GMT

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