topic Re: trying to sort month in chronical order. Not getting the desired output in Splunk Enterprise

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

chandankr — Wed, 11 Jan 2023 17:23:25 GMT

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.

Re: trying to sort month in chronical order. Not getting the desired output

richgalloway — Tue, 10 Jan 2023 13:25:07 GMT

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).

Re: trying to sort month in chronical order. Not getting the desired output

Manasa_401 — Tue, 10 Jan 2023 13:27:53 GMT

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

Re: trying to sort month in chronical order. Not getting the desired output

chandankr — Tue, 10 Jan 2023 16:09:14 GMT

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

Re: trying to sort month in chronical order. Not getting the desired output

chandankr — Tue, 10 Jan 2023 16:16:08 GMT

this is not working in sort month in chronical order

Re: trying to sort month in chronical order. Not getting the desired output

richgalloway — Tue, 10 Jan 2023 16:27:28 GMT

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

Re: trying to sort month in chronical order. Not getting the desired output

chandankr — Tue, 10 Jan 2023 16:35:29 GMT

@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

Re: trying to sort month in chronical order. Not getting the desired output

chandankr — Tue, 10 Jan 2023 16:39:34 GMT

still no luck

Re: trying to sort month in chronical order. Not getting the desired output

richgalloway — Tue, 10 Jan 2023 18:42:41 GMT

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.

Re: trying to sort month in chronical order. Not getting the desired output

chandankr — Tue, 10 Jan 2023 17:02:16 GMT

got this

Re: trying to sort month in chronical order. Not getting the desired output

Manasa_401 — Wed, 11 Jan 2023 05:15:03 GMT

Hello @chandankr

Try using sort after stats.

|sort orden

Thanks

Manasa

Re: trying to sort month in chronical order. Not getting the desired output

bowesmana — Wed, 11 Jan 2023 05:40:42 GMT

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)

Re: trying to sort month in chronical order. Not getting the desired output

chandankr — Wed, 18 Jan 2023 11:23:07 GMT

Finally got this done using below