Why is sort by day of the week not working while sort by number is?

[net1993]

Hi

This drives me crazy.

Splunk is sorting results from friday monday... instead of monday tuesday...

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

[somesoni2 · Accepted solution]

Yup... date_wday is string to string sort is applied after chart. Try like this

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
|chart limit=0 useother=f usenull=f count over date_wday
| 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 sort_field
| fields - wd sort_field

[net1993]

Many thanks @somesoni2
But I cannot understand why that behaviour by chart command? So Chart command is re-sorting on its own and also changes all fields to string ?

[somesoni2]

All aggregator commands (stats, chart, timechart) does sorting on row aggregating field. The field date_wday is already a string (string day of week).