Splunk Search

## Sorting multi-series column chart by count field

Builder

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.

Tags (3)
Engager

Not to necro this thread, but...

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

```yoursearchhere
| chart count by field1, field2
| 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
| sort 0 Total
| fields - Total```

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
| 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
| eval totalCount = totalCount - field1
| sort 0 totalCount
| fields - totalCount```

Legend

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
| 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
| eval totalCount = totalCount - field1
| sort 0 totalCount
| fields - totalCount
``````
Explorer

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

Builder

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

Get Updates on the Splunk Community!

#### The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

#### Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

#### Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...