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

[net1993]

Hi

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

[net1993]

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

[whrg]

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.

[net1993]

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

[whrg]

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

[net1993]

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