Splunk Search

How to sort data in chronological order by month, not alphabetically?

Explorer

Hello, in my query below I get the months in numerical format, I use a the chart command to obtain a chart divided into 12 months with values for different years. I would like to know how to show the months with their respective names, sorted chronologically. Thank you.

source="test.csv"
| eval Year=strftime(_time,"%Y") 
| eval month_num=strftime(_time, "%m") 
| chart distinct_count(ticket_number) as "Cantidad tickets" by month_num Year
Tags (4)
0 Karma

Explorer

Finally I solved it with this query:

source="test.csv"
| eval Year=strftime(_time,"%Y") 
| eval Month=date_month 
| chart distinct_count(ticket_number) as "Cantidad tickets" by Month Year 
| eval orden = if(Month="january",1,if(Month="february",2,if(Month="march",3,if(Month="april",4,if(Month="may",5,if(Month="june",6,if(Month="july",7,if(Month="august",8,if(Month="september",9,if(Month="october",10,if(Month="november",11,12))))))))))) 
| sort orden 
| fields - orden

Champion

You could have also probably done | eval orden = stftime(_time, "%m")

0 Karma

Explorer

Thanks but that does not work, that way the months are sorted alphabetically.

0 Karma

Champion

I mean only swapping out line 5 in your search for the line I gave:

 source="test.csv"
 | eval Year=strftime(_time,"%Y") 
 | eval Month=date_month 
 | chart distinct_count(ticket_number) as "Cantidad tickets" by Month Year 
 | eval orden = strftime(_time, "%m")
 | sort orden 
 | fields - orden
0 Karma

Path Finder

these queries sorts data either by month or year.
My requirement is to sort by both month and year.
for eg May 2017,June 2018 , May 2018,July 2018,June 2019
Thanks in advance.

0 Karma

Super Champion

Hi @JuliDeza,
try this:

source="test.csv"
 | eval Year=strftime(_time,"%Y") 
 | eval month=strftime(_time, "%B") 
 |sort - _time
 | chart distinct_count(ticket_number) as "Cantidad tickets" by month Year
0 Karma

Explorer

Thanks for your answer @493669. It also changes the graph completely, showing only two months of the last year. The query should show the same graph, but changing the numbers by the names of the months. For that, the chart command counts and divides the data by month number and paints the data for different years in different colors.
I had thought to use that query and then somehow change the numbers of the months by their corresponding name.
or
Use this other query:

source="test.csv"
| eval Year=strftime(_time,"%Y") 
| eval month=strftime(_time, "%B") 
| chart distinct_count(ticket_number) as "Cantidad tickets" by month Year

And sort the months chronologically.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!