topic Re: Order Column Headers in reverse alphabetical order after chart command in Splunk Search

Order Column Headers in reverse alphabetical order after chart command

bclarke5765 — Mon, 21 Mar 2016 21:40:30 GMT

I have a search that ends with the following commands:

| eval qtr=strftime(_time,"%Y")."-Q".(floor((tonumber(strftime(_time,"%m"))-1)/3)+1)
| chart dc(userDay) as userDays, dc(userID) as distinctUsers over groupName by qtr

I really want to display this data so that the most recent quarter is the leftmost column. In other words:

distinctUsers: 2015-Q4 | distinctUsers: 2015-Q3 | userDays: 2015-Q4 | userDays: 2015-Q3

Unfortunately, for any given quarter that I run this search, I won't know what the field names are to sort by. So, I can't use a table command. The chart command orders in the opposite direction since it does a default sort alphabetically.

Thoughts on how I can work around this?

Re: Order Column Headers in reverse alphabetical order after chart command

somesoni2 — Mon, 21 Mar 2016 22:15:04 GMT

It's a work around where an sorting index will be added to the column names (quarters). Try something like this

| eval qtr=floor((tonumber(strftime(_time,"%m"))-1)/3)+1 | eval qtr=tostring(5-qtr).strftime(_time," %Y")."-Q".(qtr)
| chart dc(userDay) as userDays, dc(userID) as distinctUsers over groupName by qtr

Update
Since you column names can be generated from the selected timerange, give this a try

...| eval qtr=floor((tonumber(strftime(_time,"%m"))-1)/3)+1 | eval qtr=strftime(_time," %Y")."-Q".(qtr)
   | chart dc(userDay) as userDays, dc(userID) as distinctUsers over groupName by qtr 
   | table groupName  [| gentimes start=10/1/2010  | search [| gentimes start=-1 | addinfo | where starttime>info_min_time AND starttime<info_max_time| table starttime ] | eval temp=1 | rename starttime as _time | sort - _time  | eval qtr=floor((tonumber(strftime(_time,"%m"))-1)/3)+1 | eval qtr=strftime(_time," %Y")."-Q".(qtr) | dedup qtr | eval qtr="\"*".qtr."\"" | stats list(qtr) as qtr | nomv qtr | rename qtr as search]

Explanation: The subsearch after the table command is generating the list of Year-Quarter combination for the selected timed range, sorting it in descending order and returning as string to be used in table command.

Re: Order Column Headers in reverse alphabetical order after chart command

bclarke5765 — Tue, 22 Mar 2016 14:30:44 GMT

This works, but when I display more than four quarters I have an issue. It displays 2014-Q4 before 2015-Q3 which I don't want. If I could do this in reverse alphabetical order it would work.