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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...