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

[ewise1]

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

[richgalloway]

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

[ewise1]

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

[richgalloway]

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

[ewise1]

Thanks for your comment 🙂

[richgalloway]

Did it work?

[somesoni2]

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

[ewise1]

Thanks alot for the hint 🙂

[cpetterborg]

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.

[niketn]

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

[ewise1]

Great Thanks 🙂