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

View solution in original post

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
0 Karma

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)"
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.