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:
Try 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
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!!
