Simple Column sorting (with variable column name)

[christianhuber]

Hi Guys

context:
i want a table grouped by region, count per region and quarter in a table
for example
Region, Cases 02/2017, Cases 01/2017
1, 200456, 30489
2, 3208342, 9123123

search (label_q1 and label_q2 are created at runtime in my_nasty_search and containing the label for the last quarters):

my_nasty_search 
 | stats first(label_q1) as label_q1, first(label_q2) as label_q2, sum(total1) as total1, sum(total2) as total2, count(region) as count_region by region
 | eval Cases {label_q1} = total1
 | eval Cases {label_q2} = total2

This gives me as an result a table with the following columns

Region, count_region, label_q1, label_q2, total1, total2, Cases Q1/2017, Cases Q2/2017

which is absolutly okay, but i prefer to have the last two columns sorte by my predefined order (Q2/2017; Q1/2017;Q4/2016 ... ) but all new fields get arange by splunk. Because of the variable columnname i can't just resort them with the fields command, as fields doesn't accept variable column names.

I am happy for any suggestions, also if looking at the context I'am just on the wrong path.

Thx
Christian

[DalJeanis]

Label the columns 2017_Q1 etc and they will naturally sort into order. (Ascending order.) Get yourself into the habit of using ISO date format (yyyy-mm-dd) and you will save yourself eons of time, since they can be compared directly and sorted without translation to epoch time.

[christianhuber]

Hi,

I'am aware of the date format, the translation to epoch time is desired, input date contains various time formats and converting it to epoch has some nice advanteges as you get a integer value and can easily calculate.

Unfortunatly the column name is defined and i can't just give them another name.

thanks for your reply