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.

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, Karma 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
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...