topic Re: Splunk not sorting Dates properly across year in Splunk Search

Splunk not sorting Dates properly across year

reverse — Mon, 13 Jan 2020 18:51:07 GMT

1/5/2020
1/12/2020
6/16/2019
6/23/2019
6/30/2019
7/7/2019
7/14/2019
7/21/2019
7/28/2019
8/4/2019
8/11/2019
8/18/2019
8/25/2019
9/1/2019
9/8/2019
9/15/2019
9/22/2019
9/29/2019
10/6/2019
10/13/2019
10/20/2019
10/27/2019
11/3/2019
11/10/2019
11/17/2019
11/24/2019
12/1/2019
12/8/2019
12/15/2019
12/22/2019
12/29/2019

Any solution ?

Re: Splunk not sorting Dates properly across year

jpolvino — Mon, 13 Jan 2020 19:35:11 GMT

It is sorting correctly based upon the lexicographic ordering.

If you want to sort by a section of the string, in this case the year, then you have a couple options:

Change the format of your field values so that the year is first. For example, 2019-06-16 will come before 2020-01-12. You can use something like | eval Time=strftime(your_field,"%Y-%m-%d")
Keep the current field format as-is, create a sorting field called something like dateSort which has the format in the previous item, sort by that, then remove the dateSort field.

Re: Splunk not sorting Dates properly across year

reverse — Mon, 13 Jan 2020 20:24:23 GMT

thanks .. how can I create a sorting field called something like dateSort which has the format in the previous item, sort by that, then remove the dateSort field.?

Re: Splunk not sorting Dates properly across year

jpolvino — Mon, 13 Jan 2020 20:32:30 GMT

Here is one way to do it:

| makeresults
| eval dateList="1/5/2020 1/12/2020 6/16/2019 6/23/2019 6/30/2019 7/7/2019"
| eval dateList=split(dateList," ") | mvexpand dateList | fields - _time
| eval dateSort=strftime(strptime(dateList,"%m/%d/%Y"),"%Y-%m-%d")
| sort + dateSort | fields - dateSort

Re: Splunk not sorting Dates properly across year

richgalloway — Mon, 13 Jan 2020 20:33:49 GMT

... | eval datasort = strptime(your_field, "%m/%d/%Y") | sort datasort | fields - datasort | ...

Re: Splunk not sorting Dates properly across year

jpolvino — Mon, 13 Jan 2020 20:43:18 GMT

@richgalloway made a good point, my 4th line has an unnecessary strftime that was used for visualization purposes. You could easily just use this: | eval dateSort=strptime(dateList,"%m/%d/%Y")