How do I sort a column of time in 12 hour format with AM / PM on the end? I have tried using eval with the _time field (which gives a standard output like: 2016-01-13 13:23:38
and my sourcetype is a standard Windows Security Event Log.
The following syntax displays a column called TIME, with the time displayed in 24hr format. I don't need to sort it as it's sorted automatically from earliest to latest.
... | eval TIME = strftime(_time, "%H:%M:%S")
... | table TIME
However, when changing the time to 12hr format (%I instead of %H) and the trailing AM /PM ( by adding %p), the auto-sort ignores the AM/PM and uses the values as numbers, not 'time-aware' values so to say.
... | eval TIME = strftime(_time, "%I:%M:%S %p")
... | table TIME
How can the earliest to latest sort be achieved using 12hr time?
I would suggest to sort first (using TIME) and then change the format.
Thanks - I tried this but received the same result.
You can use fieldformat:
| fieldformat _time=strftime(_time,"%I:%M:%S %p")
Thanks javiergn, but I have tried using fieldformat already. It shows the exact same result as eval - it shows the information, but does not sort it correctly.
Example, if I use:
| fieldformat _time=strftime(_time,"%I:%M:%S %p")
| table _time
| sort _time
The results are close, but still not correct:
07:57:50 AM
07:58:20 AM
09:52:06 AM
09:52:34 AM
08:09:17 AM
08:09:20 AM
08:09:35 AM
11:42:41 AM
11:43:07 AM
04:02:13 PM