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