Splunk Search

Sort 12-hour based date/time

lmarcel
New Member

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.

0 Karma
1 Solution

Ayn
Legend

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

View solution in original post

0 Karma

Ayn
Legend

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
0 Karma

lmarcel
New Member

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.

0 Karma

lmarcel
New Member

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

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...