Trying to sort month in chronological order and not getting the desired output?

[chandankr]

was using this below Search, 

***| rex field=_raw "<measResults>\d+\s\d+\s\d+\s\d+\s\d+\s\d+\s\d+\s\d+\s(?<active_state>\d{0,3})\s\d+\s(?<idle_state>\d{0,3})"
| eval date_month=upper(date_month)
| eventstats avg(active_state) as Active_UEs avg(idle_state) as Idle_UEs by date_month
| eval Active_UEs=round(Active_UEs,0), Idle_UEs=round(Idle_UEs,0)
| stats count by date_month,Active_UEs,Idle_UEs
| table date_month,Active_UEs,Idle_UEs

but now  i was trying to sort month in chronological order i used the below Search

***| eventstats avg(active_state) as Active_UEs avg(idle_state) as Idle_UEs by date_month
| eval Active_UEs=round(Active_UEs,0), Idle_UEs=round(Idle_UEs,0)
| eval Month=date_month
| 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 num(Month)
| stats count by Month,Active_UEs,Idle_UEs
| table Month,Active_UEs,Idle_UEs

here also month are sorted in alphabetically order not in chronilogical order.

[Manasa_401]

Hello @chandankr 

The efficient way is to create a new field to get the month number from _time field as below

|strftime month_num=strftime(_time,"%m")

| stats count by Month,month_num,Active_UEs,Idle_UEs

|sort month_num

If this helps, karma would be appreciated.

Thanks,

Manasa

[chandankr]

this is not working in sort month in chronical order

[richgalloway]

Why go through the bother of converting Month into a number and then not use it?  Sorting by orden would solve the problem, except the stats command re-sorts the data.  Try this query.

***| eventstats avg(active_state) as Active_UEs avg(idle_state) as Idle_UEs by date_month
| eval Active_UEs=round(Active_UEs,0), Idle_UEs=round(Idle_UEs,0)
| eval Month=date_month
| eval orden = case(Month="january",1, Month="february",2, Month="march",3, Month="april",4, Month="may",5, Month="june",6, Month="july",7, Month="august",8, Month="september",9, Month="october",10, Month="november",11, 1==1, 12)
| stats count, first(Month) by orden,Active_UEs,Idle_UEs
| table Month,Active_UEs,Idle_UEs

 BTW, the num function will not convert text ("january") into digits (1).

[chandankr]

@richgalloway  also i was going through eval case and show this 

 eval error_msg = case(error == 404, "Not found", error == 500, "Internal Server Error", error == 200, "OK")

eval command examples - Splunk Documentation

so i tried the below Queary 

| eval date_month=upper(date_month)
| eventstats avg(active_state) as Active_UEs avg(idle_state) as Idle_UEs by date_month
| eval Active_UEs=round(Active_UEs,0), Idle_UEs=round(Idle_UEs,0)
| eval Month=date_month
| eval month_seq=case( Month == january, "1", Month == february, "2", Month == march, "3", Month == april, "4" , Month == may, "5" , Month == june, "6" ,
Month == july, "7" , Month == august, "8" , Month == september, "9" , Month == october, "10" , Month == november, "11" , Month == december, "12" )
| stats count, first(Month) by month_seq,Active_UEs,Idle_UEs
| table Month,Active_UEs,Idle_UEs

[richgalloway]

In this query, the case statement is trying to compare the Month field to fields january, february, etc. rather than strings "january", "february", etc.  As a result, none of the cases match so month_seq is set to NULL.  When stats is told to group by a null field it always returns no results.

[chandankr]

got this 

[chandankr]

@richgalloway  tried that but no luck. Below is the screenshot for the same 

[richgalloway]

Oops!  I left out an as clause in the stats command.  Try this revision.

***| eventstats avg(active_state) as Active_UEs avg(idle_state) as Idle_UEs by date_month
| eval Active_UEs=round(Active_UEs,0), Idle_UEs=round(Idle_UEs,0)
| eval Month=date_month
| eval orden = case(Month="january",1, Month="february",2, Month="march",3, Month="april",4, Month="may",5, Month="june",6, Month="july",7, Month="august",8, Month="september",9, Month="october",10, Month="november",11, 1==1, 12)
| stats count, first(Month) as Month by orden,Active_UEs,Idle_UEs
| table Month,Active_UEs,Idle_UEs

[chandankr]

still no luck

[bowesmana]

I am not sure why you are doing eventstats as well as stats. Just use bin _time to group by a monthly time span and then use _time in the stats. As stats sorts automatically it will sort by time and then you can just format time as the Month, so get rid of all the stuff from eventstats up to stats and replace with

| bin _time span=1mon
| stats avg(active_state) as Active_UEs avg(idle_state) as Idle_UEs by _time 
| eval Month=strftime(_time, "%B")
| table Month,Active_UEs,Idle_UEs
| eval Active_UEs=round(Active_UEs,0), Idle_UEs=round(Idle_UEs,0)

[Manasa_401]

Hello @chandankr 

Try using sort after stats.

|sort orden

Thanks

Manasa

[chandankr · Accepted solution]

Finally got this done using below

| eval month=strftime(_time,"%m")
| eval month_name=date_month
| eval monthyear=date_year
| stats count by month,month_name,monthyear,AvgCPU,MaxCPU
| sort num(monthyear), num(month)
| table month_name,AvgCPU,MaxCPU