How to sort using formatted time

[rakeshyv0807]

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.

[elliotproebstel · Accepted solution]

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]

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