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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...