Splunk Search

How to sort a string time format to show the latest time?

ewise1
New Member

Hi,

I have a string date format that shows up when I do a search; what I did was did a field extraction and named that string as Date, and create a table and sort -Date to show the latest date, but apparently it doesn't work since it acts as a text. Please advice. Date formats are as below:

May 31 22:06:20 2017
May 29 22:06:20 2017
June 28 22:06:20 2017
June 27 22:06:20 2017

Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

You're right, Splunk is performing a lexicographical sort on your dates. To sort them in date order, use a hidden epoch timestamp.

... | eval sortDate=strptime(Date,"%b %d %H:%M:%S %Y") | sort sortDate | fields - sortDate
---
If this reply helps you, Karma would be appreciated.

ewise1
New Member

When I run my search for a month back I still see May before June.

sourcetype=aaaaaaa | eval sortDate=strptime(Date,"%b %d %H:%M:%S %Y") |sort sortDate|fields - sortDate| table Date, ID, COMMAND

0 Karma

richgalloway
SplunkTrust
SplunkTrust

As somesoni2 suggests, try | sort - sortDate | to reverse the display order.

---
If this reply helps you, Karma would be appreciated.
0 Karma

ewise1
New Member

Thanks for your comment 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Did it work?

---
If this reply helps you, Karma would be appreciated.
0 Karma

somesoni2
Revered Legend

++
Only suggestion is that requester wants latest date first so you'd need | sort -sortDate .

ewise1
New Member

Thanks alot for the hint 🙂

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

For a more detailed proof that Rich is right:

| makeresults 
| eval raw="May 31 22:06:20 2017,
May 29 22:06:20 2017,
June 28 22:06:20 2017,
June 27 22:06:20 2017" | makemv raw delim="," | mvexpand raw 
| eval sortbytime=strptime(raw, "%b %d %H:%M:%S %Y") 
| sort sortbytime | fields - sortbytime

The dates are in the right order as you can see.

0 Karma

niketn
Legend

Slightly different version than @richgalloway. For sorting you either need epochtime (number of ticks) or else string time in YYYY/MM/DD HH:MM:SS format so that older date are smaller event with string comparison.

However, since you string time is not in above format, you would anyways need to first convert to epochTime. So 2nd approach is beating around the bush. The following approach lets you sort based on epoch time however, it does not create an additional field since the same epoch time is formatted as string time only for displaying in table.

... 
| eval Date=strptime(Date,"%b %d %H:%M:%S %Y") 
| sort Date 
| fieldformat Date=strftime(Date,"%b %d %H:%M:%S %Y") 
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ewise1
New Member

Great Thanks 🙂

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...