How to get Month and Year in Calendar sorted format in SPLUNK

How to get Month and Year in Calendar sorted format in SPLUNK:

By default, splunk sorts field names in lexicographical order, so it becomes quite a typical task to sort fields on the basis of month and year, But no worries we are here to your rescue.

In case you have a CSV file with a dataset that appears similar to the following:

month1Try out this Query:

| inputlookup test.csv
| transpose
| rex field=column “(?<month>\w+).*”
| eval aa=case(month=”Jan”,”01″,month=”Feb”,”02″,month=”Mar”,”03″,
month=”Apr”,”04″,month=”May”,”05″,month=”Jun”,”06″,month=”Jul”,”07″,
month=”Aug”,”08″,month=”Sep”,”09″,month=”Oct”,”10″,month=”Nov”,”11″,
month=”Dec”,”12″)
| sort aa
| fields – aa,month
| transpose 1000 header_field=column
| rename column as Type
| replace “row 1” with Transaction “row 2” with Revenue in Type

month2

Works like magic, RIGHT!!

Explanation:

The test.csv file contains Transaction and Revenue values corresponding to month & year, using the command “transpose” we are interchanging row and column values (columns appear as rows under the column name ‘column’).

Now we can write a “rex” command to match and populate only the month names under the field ‘month’. After that exhibit the command “eval” with “case” behavior to assign the month-names number so that they can be sorted using the “sort” command, remove the fields ‘aa’,’month’ by using the command “fields” along with argument “-” , next step is to use the command “transpose” again, but with the optional argument “header_field”. Almost done, use the commands “rename” and “replace” to change names of fields and values in fields respectively.

You can also know about :  How To See The UF/HF Server Information and OS Details In Splunk

Hope, this post has helped you in getting Month and Year in Calendar sorted format in SPLUNK.

Happy Splunking!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.