topic Re: How to sort the items within a stacked bar chart by size? in Splunk Search

How to sort the items within a stacked bar chart by size?

ulrich_track — Tue, 30 Sep 2014 13:44:09 GMT

I have created a search to produce a stacked bar chart:
(each shop sells the same items but in different quantities)

* |
chart count(ItemType) BY Shop ItemType |

and managed to sort the stacked bars by their total size:

addtotals fieldname=total |
sort -total |
fields - total

What I am missing is how to sort the items within each bar by size. It would also be ok, to sort only the largest bar and have the others follow the same order.

Any ideas?

Re: How to sort the items within a stacked bar chart by size?

martin_mueller — Tue, 30 Sep 2014 22:44:52 GMT

This does roughly what you describe, but isn't very pretty.

| tstats count where index=* OR index=_* by sourcetype index | chart sum(count) as count by index sourcetype
| untable index sourcetype count | eventstats sum(count) as sum by sourcetype | sort + sum | streamstats dc(sourcetype) as num
| eval sourcetype = substr("000".num, -3, 3) . "_" . sourcetype | xyseries index sourcetype count
| addtotals fieldname=_total | sort - _total | fields - _total

The first line sets up a dummy chart much like yours, counting events by two fields.

The second like picks that chart apart and numbers the columns by their combined size, smallest first.

The third line prepends that zero-padded number to the column name and reassembles the chart - now the columns are sorted lexicographically again, which happens to be the same as "sorted by combined size" due to the prepended number.

The last line sorts the rows as you already described.

Note, you can remove the ugly numbers prepended to the column names... but then the chart will fall back to lexicographical ordering based on the name alone, disregarding the order we created before.

Re: How to sort the items within a stacked bar chart by size?

ulrich_track — Wed, 01 Oct 2014 13:14:28 GMT

You script works in your case, but I cannot use the tstats command as it ignores my extracted fields. (Splunk does not know them)
So I added your script starting with line 2 between my chart-line and my addtotals line, because then the fields are known.
All works, also the renaming, but when executing the xyseries, the sorting does not work - the items are not sorted.

Which commands should I replace?

Re: How to sort the items within a stacked bar chart by size?

martin_mueller — Wed, 01 Oct 2014 15:24:35 GMT

Replacing tstats is indeed no problem, that's just my dummy data.

For debugging, backtrack from the rear by removing most commands, then adding them back one by one.

Re: How to sort the items within a stacked bar chart by size?

ulrich_track — Thu, 02 Oct 2014 10:52:42 GMT

I did - it works until the xyseries command. I was searching for an alternative like chart, but that doesn't display any chart. xyseries seems to be the solution, but none of the bars are sorted by size. Maybe it's just not possible.

Re: How to sort the items within a stacked bar chart by size?

martin_mueller — Thu, 02 Oct 2014 20:46:52 GMT

What do the column names look like after the xyseries in your search? It'll sort the columns lexicographically based on that, which is why I added the number to its front.

Re: How to sort the items within a stacked bar chart by size?

ulrich_track — Fri, 03 Oct 2014 11:07:27 GMT

I was mistaken. Your script does the job: it checks the total occurrence of each sourcetype and then sorts it on the graph in that order. What confused me, was that sometimes a small bar was in between two large bars, but that's correct because only on this index few sourcetypes showed up - looking at all events, it is correct.

For my script, I adapted it a little in the line
| eventstats sum(count) AS sum BY index
but that's fine, the fields have to be adjusted for the individual case.

Thanks!

Re: How to sort the items within a stacked bar chart by size?

andreafebbo — Mon, 12 Sep 2016 15:26:38 GMT

Is there a way for not renaming the series?
because splunk gives the color basing on the name and I have other charts and the with the same series and i would like to maintain the same colors.