Splunk Search

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

JuliDeza
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

JuliDeza
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

micahkemp
Champion

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

0 Karma

JuliDeza
Explorer

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

0 Karma

micahkemp
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

abhishekroy168
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

493669
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

JuliDeza
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
Get Updates on the Splunk Community!

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Pack your bags (and maybe your dancing shoes)—Cisco Live is heading to San Diego, June 8–12, 2025, and Splunk ...

Splunk App Dev Community Updates – What’s New and What’s Next

Welcome to your go-to roundup of everything happening in the Splunk App Dev Community! Whether you're building ...

The Latest Cisco Integrations With Splunk Platform!

Join us for an exciting tech talk where we’ll explore the latest integrations in Cisco + Splunk! We’ve ...