topic How to sort dynamic column names after timechart and transpose in Splunk Search

How to sort dynamic column names after timechart and transpose

gnoriega — Wed, 08 Jul 2020 23:14:16 GMT

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.

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

Re: How to sort dynamic column names after timechart and transpose

bowesmana — Thu, 09 Jul 2020 00:10:34 GMT

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

Re: How to sort dynamic column names after timechart and transpose

gnoriega — Thu, 09 Jul 2020 15:07:35 GMT

Thanks @bowesmana this is precisely what I needed.