Splunk Search

How to sort Pivot "month-year" columns in ascending chronological order (ex: Nov-2014, Dec-2014, Jan-2015, etc)?

Vijaikanth
Path Finder

Requirement: We need the order of Month column names to start with the three letter month followed by year in ascending order.

Request Name    Nov-2014    Dec-2014    Jan-2015    Feb-2015    Mar-2015
Get                50          10          20          20          40
Post               15          5           45          23          34

However, while doing the Pivot, the columns are getting sorted by the string value in alphabetical order and the output is:

Request Name    Dec-2014    Feb-2015    Jan-2015    Mar-2015    Nov-2014
Get                10          20          20          40          50
Post                5          23          45          34          15

How do i rearrange the columns to point in ascending order.

Tags (4)
0 Karma
1 Solution

Vijaikanth
Path Finder

Finally Solved the chronological order in the Format

Month = strftime(strptime(Date,"%d/%m/%Y"),"%Y-%m (%b)")

so the Result will be 2015-01 (Jan), 2015-02 (Feb).....

It Works fine for all year, all month...

View solution in original post

Vijaikanth
Path Finder

Finally Solved the chronological order in the Format

Month = strftime(strptime(Date,"%d/%m/%Y"),"%Y-%m (%b)")

so the Result will be 2015-01 (Jan), 2015-02 (Feb).....

It Works fine for all year, all month...

View solution in original post

Vijaikanth
Path Finder

Yes. If we set the Month as "04-2015" instead of "Apr-2015" we can able to sort it. But after that how to rename the value

"04-2015" to "Apr-2015".

Is it possible to apply wildcard characters in the column names? like Rename 04* as Apr (or) doing some substring on the Column name and rename ?

0 Karma

Vijaikanth
Path Finder

Here Month = strftime(strptime(Date,"%d/%m/%Y"),"%b-%Y")

so Month = "Apr-2015"

When i set Month = strftime(strptime(Date,"%d/%m/%Y"),"%m-%Y")

so Month = "04-2015" and it will sort the columns properly but again i need to rename the "04-2015" to "Apr-2015" like that for all dynamic columns for the month

0 Karma

MuS
SplunkTrust
SplunkTrust

And that's the way you should do it, Splunk is not aware that Feb is equal the 02 months and sorts it based on the alphanumeric value. If you need to sort it like this, you provided the answer yourself 😉

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!