Splunk Search

Splunk not sorting Dates properly across year

reverse
Contributor

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 ?

0 Karma
1 Solution

jpolvino
Builder

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.

View solution in original post

jpolvino
Builder

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.

View solution in original post

reverse
Contributor

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.

jpolvino
Builder

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

0 Karma

jpolvino
Builder

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
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.