Spread our blog

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.

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

Happy Splunking!!

What’s your Reaction?
+1
+1
+1
+1
+1
+1
+1

Spread our blog
Previous articleUsage of Splunk EVAL Function : LEN
Next articleHow to Find the Missing HOSTS in SPLUNK
Passionate content developer dedicated to producing result-oriented content, a specialist in technical and marketing niche writing!! Splunk Geek is a professional content writer with 6 years of experience and has been working for businesses of all types and sizes. It believes in offering insightful, educational, and valuable content and it's work reflects that.

LEAVE A REPLY

Please enter your comment!
Please enter your name here