- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to sort a string time format to show the latest time?
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As somesoni2 suggests, try | sort - sortDate |
to reverse the display order.
If this reply helps you, Karma would be appreciated.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your comment 🙂
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Did it work?
If this reply helps you, Karma would be appreciated.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
++
Only suggestion is that requester wants latest date first so you'd need | sort -sortDate
.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks alot for the hint 🙂
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!!"
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Great Thanks 🙂