Dashboards & Visualizations

How to maintain the exact sequence of columns in chart

pal4life
Path Finder

Hi,
I currently have this query

source="Splunk-dat-Month-CHML.csv" host="splunk.engine.host" index="security" sourcetype="csv"| table _time, high, medium, low | untable _time severity value| chart first(value) over _time by severity

But for some reason when the chart is drawn, it shows me a bar chart with high then low then medium on it, how can I ensure it maintains the sequence of high, medium and low?

Thanks.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

If the field names are static, you could just add your table command at the end as well.

source="Splunk-dat-Month-CHML.csv" host="splunk.engine.host" index="security" sourcetype="csv"| table _time, high, medium, low | untable _time severity value| chart first(value) over _time by severity | table _time, high, medium, low

After chart/timechart/xyseries type of commands fields name are sorted alphabatically (H,L,M). One workaround would be to add a numeric seq number to field names so that they are sorted numerically and retain their order.

source="Splunk-dat-Month-CHML.csv" host="splunk.engine.host" index="security" sourcetype="csv"| table _time, high, medium, low | untable _time severity value | streamstats count as sno by _time | eval severity=sno.".".severity | chart first(value) over _time by severity 

View solution in original post

woodcock
Esteemed Legend

Like this:

 source="Splunk-dat-Month-CHML.csv" host="splunk.engine.host" index="security" sourcetype="csv"
| table _time high medium low
| rename high AS "  high" medium AS " medium"

Note that high has been renamed with 2 leading spaces and medium with just one (and low not at all).
The whitespace is invisible in the chart but forces the alphabetical order that you desire.
I do not think that you need the untable -> rechart because I am assuming that you did that in an attempt to re-order the fields but if you need it to coalesce values or times, then just add it back in.

woodcock
Esteemed Legend

Hey, you forgot to test mine; it works and is the simplest.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

I like it, for small number of fields.

0 Karma

lguinn2
Legend

Charts are sorted using the fields following the "by". "high, low, medium" is an alphabetic sort. Try this:

source="Splunk-dat-Month-CHML.csv" host="splunk.engine.host" index="security" sourcetype="csv"
| untable _time severity value
| eval severity_sorter = case(severity=="high",3, severity=="medium",2, severity=="low",1,1==1,0)
| chart first(value) by _time severity_sorter
| rename "1" as Low "2" as "Medium" "3" as "High"

pal4life
Path Finder

Seems like a good idea but this gives no output

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Underscore missing on _time was the reason for no data. Unfortunately, the rename reorders the fields, so you have to use either somesoni2's method (append numeric) or woodcock's (append spaces).

0 Karma

somesoni2
SplunkTrust
SplunkTrust

If the field names are static, you could just add your table command at the end as well.

source="Splunk-dat-Month-CHML.csv" host="splunk.engine.host" index="security" sourcetype="csv"| table _time, high, medium, low | untable _time severity value| chart first(value) over _time by severity | table _time, high, medium, low

After chart/timechart/xyseries type of commands fields name are sorted alphabatically (H,L,M). One workaround would be to add a numeric seq number to field names so that they are sorted numerically and retain their order.

source="Splunk-dat-Month-CHML.csv" host="splunk.engine.host" index="security" sourcetype="csv"| table _time, high, medium, low | untable _time severity value | streamstats count as sno by _time | eval severity=sno.".".severity | chart first(value) over _time by severity 

pal4life
Path Finder

The first one worked for me, I will try the 2nd option as well.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...