Splunk Search

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

SQservicedesk
Explorer

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?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

SQservicedesk
Explorer

Thanks - I tried this but received the same result.

0 Karma

javiergn
SplunkTrust
SplunkTrust

You can use fieldformat:

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

SQservicedesk
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...