topic Re: How to maintain the exact sequence of columns in chart in Dashboards & Visualizations

How to maintain the exact sequence of columns in chart

pal4life — Mon, 27 Mar 2017 15:20:12 GMT

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.

Re: How to maintain the exact sequence of columns in chart

somesoni2 — Mon, 27 Mar 2017 15:25:50 GMT

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

Re: How to maintain the exact sequence of columns in chart

lguinn2 — Mon, 27 Mar 2017 15:31:22 GMT

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"

Re: How to maintain the exact sequence of columns in chart

woodcock — Mon, 27 Mar 2017 17:12:07 GMT

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.

Re: How to maintain the exact sequence of columns in chart

pal4life — Mon, 27 Mar 2017 18:50:02 GMT

Seems like a good idea but this gives no output

Re: How to maintain the exact sequence of columns in chart

pal4life — Mon, 27 Mar 2017 18:51:47 GMT

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

Re: How to maintain the exact sequence of columns in chart

woodcock — Mon, 27 Mar 2017 18:56:28 GMT

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

Re: How to maintain the exact sequence of columns in chart

DalJeanis — Wed, 17 May 2017 16:59:50 GMT

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

Re: How to maintain the exact sequence of columns in chart

DalJeanis — Wed, 17 May 2017 17:00:13 GMT

I like it, for small number of fields.