Splunk Search

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

ulrich_track
Path Finder

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?

Tags (4)
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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.

andreafebbo
Communicator

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.

0 Karma

ulrich_track
Path Finder

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!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

ulrich_track
Path Finder

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

ulrich_track
Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...