topic Re: Sorting multi-series column chart by count field in Splunk Search

Sorting multi-series column chart by count field

Cuyose — Fri, 26 Jul 2013 21:30:34 GMT

Not sure why this is so perplexing, but or the life of me I can't get this to sort how I want.

The following chart syntax:
|chart count(C) as Count by B,C

where B is a Month field, C represents 5 separate values and Count is the count of those values as they occur by Month.

The resulting multi-series chart displays with the correct data, but regardless of how I try and sort, the Month is sorted correctly, but within each month the columns representing the 5 counts are always sorted by the alpha value of "C" and not the count.

Basically I wanted to do this
| chart count(C) as Count by B,C |sort 0 B,Count

But that doesn't work.

Re: Sorting multi-series column chart by count field

lguinn2 — Fri, 26 Jul 2013 22:02:02 GMT

The reason that this doesn't work is: the columns in the resulting chart are named by the VALUES of C. So if the events had values of C such as "red", "yellow", "green", "blue", "orange" - then the columns would be named red, yellow, green, blue, and orange. So you would have to do something like this:

| chart count(C) as Count by B,C |sort 0 B,red

And that probably doesn't make any sense. Perhaps what you should do is to sort by the overall count. Here is how to do that:

yoursearchhere
| chart count by field1, field2
| addtotals totalCount
| sort 0 totalCount
| fields - totalCount

This works well as long as field1 does not contain numeric values. If it does, then you can do this:

yoursearchhere
| chart count by field1, field2
| addtotals totalCount
| eval totalCount = totalCount - field1
| sort 0 totalCount
| fields - totalCount

Re: Sorting multi-series column chart by count field

Cuyose — Fri, 26 Jul 2013 22:13:46 GMT

Im still unable to get this to do anything different than the original chart. Perhaps I am misunderstanding your instructions. In the form above would it be:
| chart count(C) as Count by B,C
| addtotals totalCount
| eval totalCount = totalCount - B
| sort 0 totalCount
| fields - totalCount

I tried this and it doesn't do anything different, C is still the 2nd sort, not the count of C

Re: Sorting multi-series column chart by count field

kennyja — Tue, 29 Sep 2020 08:41:12 GMT

Thanks! I used this and it did EXACTLY what I wanted .... save one minor detail. The Total field at the far right is blank. I was expecting it to sum all of the values left to right on that row.

example
field1, field2_subtotal, field2_ sub total, Total

Any ideas what I need to do to fix the "Totals" column?

Thanks

Re: Sorting multi-series column chart by count field

Machine247 — Wed, 27 Jul 2022 21:04:01 GMT

Not to necro this thread, but...

This page still turns up in Google so let's update it with an answer!

The following seems to fix the suggested code (at least here in the year 2022...)

yoursearchhere
| chart count by field1, field2
| addtotals fieldname=totalCount
| sort 0 totalCount
| fields - totalCount

Alternatively,

you should be able to accept the default field created by addtotals (i.e. it is named "Total")

Which simplifies the code to...

yoursearchhere
| chart count by field1, field2
| addtotals
| sort 0 Total
| fields - Total

For more information, refer to the addtotals documentation:

addtotals - Splunk Documentation

refer to the sort documentation also

sort - Splunk Documentation

the sort 0 above is discussed there

<count>
Syntax: <int> | limit=<int>
Description: Specify the number of results to return from the sorted results. 
If no count is specified, the default limit of 10000 is used. 
If 0 is specified, all results are returned.           *** NOTE ***
You can specify the count using an integer or precede the count with a label,
for example limit=10.

*** NOTE ***
Using sort 0 might have a negative impact performance, 
depending on how many results are returned.

If you want for example, the top 10 "results" in descending order then you do the following ...

Note the minus character ("-") in front the Total field, this reverses the sort order iirc.

Below I used the "limit=10" rather than "10" just because it makes the code more readable

yoursearchhere
| chart count by field1, field2
| addtotals
| sort limit=10 -Total
| fields - Total

PS.

You should be able to adapt the other example from lguinn2 in the same way

"This works well as long as field1 does not contain numeric values. If it does, then you can do this..."

yoursearchhere
| chart count by field1, field2
| addtotals fieldname=totalCount
| eval totalCount = totalCount - field1
| sort 0 totalCount
| fields - totalCount