Splunk Search
Highlighted

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

Re: Sorting multi-series column chart by count field

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

Re: Sorting multi-series column chart by count field

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

0 Karma
Highlighted

Re: Sorting multi-series column chart by count field

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, field2subtotal, field2 sub total, Total

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

Thanks

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.