Splunk Search

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

rgsage
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

yannK
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

yannK
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

rgsage
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
Get Updates on the Splunk Community!

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

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