Sort 12-hour based date/time

[lmarcel]

I have a dashboard table based on the search:

index=eaccess Card_Name="John*" 
| convert timeformat="%m/%d/%y %I:%M:%S %p" ctime(_time) as Date/Time 
| table Date/Time, Location | sort -Date/Time

The Date/Time field displays correctly but when clicking on the header to sort, the AM/PM part of the date/time is not taken into account, e.g.: 01:59:29 PM comes before 06:45:15 AM which comes before 12:58:10 PM.

If the sort command not able to handle 12-hour time format?

Any help would be appreciated.

[Ayn · Accepted solution]

It's not as much that it's not able to handle it as that it has no idea that the string you're asking it to sort is a timestamp. When you convert() _time to Date/Time, you will get a string that happens to represent a time in a text format, but any sort command you will throw at it just knows that it's some kind of text that it's supposed to sort. It's better to use fieldformat so you can represent the data the way you want but still be able to sort it.

By the way you don't need to sort this because Splunk will already grab the most recent events first.

index=eaccess Card_Name="John*" 
| fieldformat _time=strftime(_time,"%m/%d/%y %I:%M:%S %p")
| table _time, Location

[lmarcel]

For the sake of completion, here is what I ended up using:

index=eaccess Card_Name="John*"
| rename _time as Date/Time
| fieldformat Date/Time = strftime('Date/Time',"%m/%d/%y %I:%M:%S %p")
| table Date/Time, Location
| sort Date/Time

If the sort command is left out, clicking on the table header won't change the sorting order.

[lmarcel]

Thank you very much, I didn't realize that the output of convert() was a string. It's working now. Thanks!