How to sort dynamic column names after timechart and transpose

[gnoriega]

Hi,

I'm creating a report with the following search that runs each month covering the past 3 months of data.

It works and I can display the results in a bar chart but it gets sorted alphabetically by sourcetype. 

index=* | timechart span=1mon count by sourcetype 
 | eval _time = strftime(_time,"%B")
 | rename _time as Time
 | fields - _*
 | transpose header_field=Time column_name="sourcetype"

I want to sort it by count of last month. So for example if I run the report in July I get columns "sourcetype", "April", "May", "June". Each month that I run the report the column names will change. I can get the results I want this month  by adding:

 | sort - "June"

How can I set this up automatically  so that the results are sorted by the last column (previous month)?

[bowesmana · Accepted solution]

This works by giving the previous month a fixed field name and then sorting by that before naming it correctly at the end

index=*
| timechart span=1mon count by sourcetype 
| eval _time = if(_time=relative_time(now(),"-1mon@mon"), "Last Month", strftime(_time,"%B")) 
| rename _time as Time 
| fields - _* 
| transpose header_field=Time column_name="sourcetype"
| sort - "Last Month"
| eval name=strftime(relative_time(now(),"-1mon@mon"),"%B")
| eval {name}='Last Month'
| fields - "Last Month" name

which is simply just calling the relative -1 month from now as last month rather than the name itself and then fixing up the name after the sort.

"Last Month" could be anything temporary

[gnoriega]

Thanks @bowesmana  this is precisely what I needed.