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