How to sort a time field in a 12hr time format (AM/PM)?

[SQservicedesk]

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?

[somesoni2]

I would suggest to sort first (using TIME) and then change the format.

[SQservicedesk]

Thanks - I tried this but received the same result.

[javiergn]

You can use fieldformat:

| fieldformat _time=strftime(_time,"%I:%M:%S %p")

[SQservicedesk]

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