Splunk Search

How to sort the display order of data inside a bar on a stacked bar chart in Splunk 6.2.1?

Path Finder

We have a search like this:

... 
| eval week_start=relative_time(_time,"@w")
| eval week_label=strftime(week_start, "Week of %m-%d")
| chart sum(activityTime) AS hours BY customer week_label

Which gives a table like this:

customer    Week of 11-29  Week of 12-06
--------    -------------  -------------
Customer-A              8             10
Customer-B             15              7

And a stacked bar chart that looks like this:

Customer-A [ 12-06    ][ 11-29  ]
Customer-B [ 12-06 ][ 11-29         ]

However, we want the data inside the bars to appear in date order as follows:

Customer-A [ 11-29  ][ 12-06    ]
Customer-B [ 11-29         ][ 12-06 ]

Using | sort I can change the order of customers but I can't budge the display order of the elements inside each bar. How can I change the elements inside the bar to appear in date (also happens to be alpha) order?

We are on 6.2.1

0 Karma
1 Solution

Splunk Employee
Splunk Employee

The problem is that the chart will return the data with the columns headers sorted alphabetically,
_time week of 1-10, week of 1-17, week of 01-24 ...
but the visualization with stacked columns will put the last ones on the bottom,. and the firs ones on the top

A trick is to rename the title to add a number that will be sorted in the reverse order

index="_internal" admin source="*scheduler.log"  | eval week_start=relative_time(_time,"@w")
| eval week_number=strftime(week_start, "%U")
| eval year_number=strftime(week_start, "%Y")
| convert num(week_number) AS week_number num(year_number) AS year_number
| eval title_sort=10000-year_number-week_number
| eval week_label="(".title_sort.") ".strftime(week_start, "Week of %m-%d")
| eval hours=run_time/60/60
| chart sum(hours) by app week_label

View solution in original post

0 Karma

Splunk Employee
Splunk Employee

The problem is that the chart will return the data with the columns headers sorted alphabetically,
_time week of 1-10, week of 1-17, week of 01-24 ...
but the visualization with stacked columns will put the last ones on the bottom,. and the firs ones on the top

A trick is to rename the title to add a number that will be sorted in the reverse order

index="_internal" admin source="*scheduler.log"  | eval week_start=relative_time(_time,"@w")
| eval week_number=strftime(week_start, "%U")
| eval year_number=strftime(week_start, "%Y")
| convert num(week_number) AS week_number num(year_number) AS year_number
| eval title_sort=10000-year_number-week_number
| eval week_label="(".title_sort.") ".strftime(week_start, "Week of %m-%d")
| eval hours=run_time/60/60
| chart sum(hours) by app week_label

View solution in original post

0 Karma

Path Finder

Thank you. Since I am constrained to column-name alphabetical sort order inside the bar I ended up doing it like this (our search goes back up to 4 weeks which explains the magic 4 in eval title_sort below):

... earliest=@w-3d
...
| eval week_start=relative_time(_time,"@w")
| eval sort_start=relative_time(now(),"@w-3w")
| eval title_sort=4-round((week_start-sort_start) / (60*60*24*7), 0)
| eval week_label="(".title_sort.") ".strftime(week_start, "Week of %m-%d")
...

Which gives column titles like this that sort chronologically (reverse alphabetically) inside the bars:

(1) Week of 12-06
(2) Week of 11-29
...

The title_sort index numbers are just a bit more palatable that the numbers generated by 10000-year_number-week_number.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!