Splunk not sorting Dates properly across year

[reverse]

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 ?

[jpolvino · Accepted solution]

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:

1. 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")
2. 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.

[reverse]

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

[richgalloway]

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

[jpolvino]

@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")

[jpolvino]

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