topic Re: 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?

karthikTIL — Fri, 26 Sep 2014 07:07:04 GMT

HI,

For my below query, i get months in alphabetical order like april-2014, august-2014, february-2014, January-2014.

But i want this to be sorted like January-2014, February-2014. Please let me know.

source="test.csv"| eval Month=date_month."-".date_year|stats count(Incidents) by Month

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

Ayn — Fri, 26 Sep 2014 07:22:32 GMT

Splunk has no idea that "January" corresponds to month "1" and "February" corresponds to month "2". You need to tell it. One simple way of doing that is creating a numerical field to sort by and use that:

source=test.csv | strftime month_num=strftime(_time,"%m") | eval Month=date_month."-".date_year | stats count(Incidents) by month_num,Month | sort month_num | fields - month_num

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

karthikTIL — Fri, 26 Sep 2014 07:35:05 GMT

Thank you, it works

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

agoriawala_splu — Tue, 23 Jun 2015 18:03:12 GMT

I have a field Month which has values as [Jan,Feb,Mar ....]. I tried the above solution but it didn't work for me.

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

pbarbuto — Mon, 11 Jun 2018 23:08:18 GMT

I used the following for fiscal years.

 | eval sort=case(
     Month=="AUG","01",
     Month=="SEP","02",
     Month=="OCT","03",
     Month=="NOV","04",
     Month=="DEC","05",
     Month=="JAN","06",
     Month=="FEB","07",
     Month=="MAR","08",
     Month=="APR","09",
     Month=="MAY","10",
     Month=="JUN","11",
     Month=="JUL","12")
| sort sort     
| fields - sort

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

sunilsk1 — Sat, 29 Sep 2018 05:18:54 GMT

The following converts the month into Y-m format and the numerical sorting helps out.

base search |convert ctime(_time) as Time timeformat=%Y-%m|chart avg(yourfield) over Time by some_other_field|sort Time

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

frsouza — Thu, 07 Nov 2019 16:57:05 GMT

You can use timechart (what do you want to correlate with months). In your case, if you want to know the Incidents per month, would be | timechart count by Incidents