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

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

JuliDeza — Wed, 07 Feb 2018 16:08:56 GMT

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

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

493669 — Wed, 07 Feb 2018 16:20:36 GMT

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

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

JuliDeza — Wed, 07 Feb 2018 19:38:58 GMT

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.

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

JuliDeza — Thu, 08 Feb 2018 19:11:11 GMT

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

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

micahkemp — Thu, 08 Feb 2018 20:04:39 GMT

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

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

JuliDeza — Thu, 08 Feb 2018 20:12:43 GMT

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

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

micahkemp — Thu, 08 Feb 2018 20:15:56 GMT

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

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

abhishekroy168 — Tue, 06 Mar 2018 09:11:16 GMT

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.