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...

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
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...