topic Re: In the following searches, how come the days of the week are not sorting correctly? in Splunk Search

In the following searches, how come the days of the week are not sorting correctly?

net1993 — Wed, 05 Dec 2018 14:53:05 GMT

This is driving me crazy.

Splunk is sorting results from friday — monday... instead of monday, tuesday, etc...

Search:

(earliest="11/25/2018:00:00:00" index="_audit" sourcetype="audittrail" (action="rtsearch" OR action="search" OR action="accelerate_search" ) action=*  )
OR (earliest="11/25/2018:00:00:00" index="_internal"  sourcetype="scheduler" search_type="scheduled" (status="skipped" OR status="success") )
|fields index, date_wday, status
| eval wd=lower(date_wday) 
| eval sort_field=case(wd=="monday",1, wd=="tuesday",2, wd=="wednesday",3, wd=="thursday",4, wd=="friday",5, wd=="weekend",6)
|chart limit=0 useother=f usenull=f count  over date_wday
|sort sort_field

and

(earliest="11/25/2018:00:00:00" index="_audit" sourcetype="audittrail" (action="rtsearch" OR action="search" OR action="accelerate_search" ) action=*  )
OR (earliest="11/25/2018:00:00:00" index="_internal"  sourcetype="scheduler" search_type="scheduled" (status="skipped" OR status="success") )
|fields index, date_wday, status
| eval wd=lower(date_wday) 
| eval sort_field=case(wd=="monday",1, wd=="tuesday",2, wd=="wednesday",3, wd=="thursday",4, wd=="friday",5, wd=="weekend",6)
|sort 0 sort_field
|chart limit=0 useother=f usenull=f count  over date_wday

Re: In the following searches, how come the days of the week are not sorting correctly?

whrg — Tue, 29 Sep 2020 22:21:10 GMT

Hello @net1993,

Regarding your first search: You are losing the sort_field field after running the chart command.
In the second search, the chart command does not care about the ordering of events.

Try replacing the last two lines of your first search with this:

 | stats count by date_wday,sort_field
 | sort sort_field

This will keep the sort_field available.

Alternatively, run the chart/stats command first and afterwards the eval+sort commands:

| ...
| stats count by date_wday
| eval sort_field=case(date_wday=="monday",1, date_wday=="tuesday",2, date_wday=="wednesday",3, date_wday=="thursday",4, date_wday=="friday",5, date_wday=="weekend",6)
| sort sort_field | fields - sort_field

On a side note: Are you sure that events such as date_wday="weekend" exist? Because I am seeing date_wday="sunday" in my Splunk.

Re: In the following searches, how come the days of the week are not sorting correctly?

net1993 — Wed, 12 Dec 2018 19:22:32 GMT

not sure for last question. anyway I solved the problem as I do sorting at last after chart, a way more efficient.

Re: In the following searches, how come the days of the week are not sorting correctly?

whrg — Tue, 29 Sep 2020 22:21:13 GMT

I meant that the condition date_wday=="weekend" will never be met.
You might want to change it to case(..., date_wday=="friday",5, date_wday=="saturday",6, date_wday=="sunday",7).

Re: In the following searches, how come the days of the week are not sorting correctly?

net1993 — Thu, 13 Dec 2018 07:20:57 GMT

Your're correct. I had change it and no idea where it did come from. Think I have coppied from somewhere else and probably there was not needed to have weekenend days..

Re: In the following searches, how come the days of the week are not sorting correctly?

net1993 — Thu, 13 Dec 2018 07:24:48 GMT

Solution to that problem are so far 2:
1. eval and sort after chart as chart is changing to string
2. use another function instead of case to convert the week number to char because when already in char, the sorting is correct.

tip sorting after chart is way more efficient