Archive

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

Path Finder

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
Tags (1)
0 Karma
1 Solution

Revered Legend

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

View solution in original post

0 Karma

Revered Legend

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

View solution in original post

0 Karma

Path Finder

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 ?

0 Karma

Revered Legend

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

0 Karma