Splunk Search

Order Column Headers in reverse alphabetical order after chart command

bclarke5765
Explorer

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?

0 Karma

somesoni2
Revered Legend

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.

bclarke5765
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...