https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374060#M160516 <P>When I run my search for a month back I still see May before June.</P> <P>sourcetype=aaaaaaa | eval sortDate=strptime(Date,"%b %d %H:%M:%S %Y") |sort sortDate|fields - sortDate| table Date, ID, COMMAND </P> Wed, 28 Jun 2017 21:24:11 GMT ewise1 2017-06-28T21:24:11Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374056#M160512 <P>Hi,</P> <P>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:</P> <P>May 31 22:06:20 2017<BR /> May 29 22:06:20 2017<BR /> June 28 22:06:20 2017<BR /> June 27 22:06:20 2017</P> Wed, 28 Jun 2017 20:58:44 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374056#M160512 ewise1 2017-06-28T20:58:44Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374057#M160513 <P>You're right, Splunk is performing a lexicographical sort on your dates. To sort them in date order, use a hidden epoch timestamp.</P> <PRE><CODE>... | eval sortDate=strptime(Date,"%b %d %H:%M:%S %Y") | sort sortDate | fields - sortDate </CODE></PRE> Wed, 28 Jun 2017 21:10:41 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374057#M160513 richgalloway 2017-06-28T21:10:41Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374058#M160514 <P>For a more detailed proof that Rich is right:</P> <PRE><CODE>| 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 </CODE></PRE> <P>The dates are in the right order as you can see.</P> Wed, 28 Jun 2017 21:13:30 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374058#M160514 cpetterborg 2017-06-28T21:13:30Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374059#M160515 <P>++<BR /> Only suggestion is that requester wants latest date first so you'd need <CODE>| sort -sortDate</CODE> .</P> Wed, 28 Jun 2017 21:17:18 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374059#M160515 somesoni2 2017-06-28T21:17:18Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374060#M160516 <P>When I run my search for a month back I still see May before June.</P> <P>sourcetype=aaaaaaa | eval sortDate=strptime(Date,"%b %d %H:%M:%S %Y") |sort sortDate|fields - sortDate| table Date, ID, COMMAND </P> Wed, 28 Jun 2017 21:24:11 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374060#M160516 ewise1 2017-06-28T21:24:11Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374061#M160517 <P>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. </P> <P>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.</P> <PRE><CODE>... | eval Date=strptime(Date,"%b %d %H:%M:%S %Y") | sort Date | fieldformat Date=strftime(Date,"%b %d %H:%M:%S %Y") </CODE></PRE> Wed, 28 Jun 2017 21:25:07 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374061#M160517 niketn 2017-06-28T21:25:07Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374062#M160518 <P>As somesoni2 suggests, try <CODE>| sort - sortDate |</CODE> to reverse the display order.</P> Wed, 28 Jun 2017 21:29:46 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374062#M160518 richgalloway 2017-06-28T21:29:46Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374063#M160519 <P>Great Thanks <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Wed, 28 Jun 2017 22:23:32 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374063#M160519 ewise1 2017-06-28T22:23:32Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374064#M160520 <P>Thanks alot for the hint <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Wed, 28 Jun 2017 22:23:58 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374064#M160520 ewise1 2017-06-28T22:23:58Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374065#M160521 <P>Thanks for your comment <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Wed, 28 Jun 2017 22:24:55 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374065#M160521 ewise1 2017-06-28T22:24:55Z https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374066#M160522 <P>Did it work?</P> Thu, 29 Jun 2017 17:19:17 GMT https://community.splunk.com/t5/Splunk-Search/How-to-sort-a-string-time-format-to-show-the-latest-time/m-p/374066#M160522 richgalloway 2017-06-29T17:19:17Z