Splunk Search

How to sort using formatted time

rakeshyv0807
Explorer

Hi,

I have a result table with two columns "formattedTime" and "Unsuccessful logins". I am displaying time in the format like " eval formattedTime = strftime(_time, "%b %d, %Y %I:%M:%S %p") ". I see the following result in the output:

formattedTime Unsuccessful Logins
Mar 20, 2018 01:00:00 AM 1799
Mar 20, 2018 01:00:00 PM 3741
Mar 20, 2018 02:00:00 AM 1785
Mar 20, 2018 02:00:00 PM 3703
Mar 20, 2018 03:00:00 AM 1694
Mar 20, 2018 03:00:00 PM 3651
Mar 20, 2018 04:00:00 AM 1764
Mar 20, 2018 04:00:00 PM 3683
Mar 20, 2018 05:00:00 AM 2515

What I need is to display the results from 12 AM - 4 PM is ascending format.

This is the current logic I am using to display those results:

| bucket _time span=1h | eval formattedTime = strftime(_time, "%b %d, %Y %I:%M:%S %p") |stats count(id) as "Unsuccessful Logins" by formattedTime| sort formattedTime

Any one please help me!

Thanks in advance.

Tags (1)
0 Karma
1 Solution

elliotproebstel
Champion

My recommendation is to not convert the _time field with an eval but instead to use the command fieldformat to change how the value is displayed while retaining the original epoch string, which is easily sortable. That search would look like this:

your base search that filters down to events in the desired time range
| bucket _time span=1h 
| stats count(id) as "Unsuccessful Logins" by _time
| sort _time 
| fieldformat _time=strftime(_time, "%b %d, %Y %I:%M:%S %p") 

View solution in original post

elliotproebstel
Champion

My recommendation is to not convert the _time field with an eval but instead to use the command fieldformat to change how the value is displayed while retaining the original epoch string, which is easily sortable. That search would look like this:

your base search that filters down to events in the desired time range
| bucket _time span=1h 
| stats count(id) as "Unsuccessful Logins" by _time
| sort _time 
| fieldformat _time=strftime(_time, "%b %d, %Y %I:%M:%S %p") 

rakeshyv0807
Explorer

@elliotproebstel - Thank you for the suggestion. It worked as expected.

0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...