Natural sort order instead of lexicographical order in search?

[splunknoob408]

I've got a date field that I extracted from log messages, and it is pulled from two different sources. One source zero-pads the numbers, so I get dates like 12/08/17, while the other does not and gives me 12/8/17.

The problem is during sort, where I want a natural sort order, but the lexicographical sorting swaps the date entries around.

Is there a way in splunk to force it to zero-pad the dates so they are sorted properly? I have seen references to using a strptime() function, but don't know how to use it in my particular example. Here is my search, which works, except that the X axis (date) has its columns swapped around due to the lexicographical sorting:

source=OrderEventsTable service="tracking" event_details="shipped on*" | regex event_details="\d+/\d+/\d+" | dedup order_id | stats count as "shipped number" by shipped_date

I only know that I would want to pass strptime %m/%d/%y, since %d zero-pads the day. I tried this, unsuccessfully, because it changed shipped_date into a decimal number.

source=OrderEventsTable service="tracking" event_details="shipped on*" | regex event_details="\d+/\d+/\d+" | dedup order_id | stats count as "shipped number" by shipped_date | eval shipped_date=strptime(shipped_date,"%m/%d/%y")

[MonkeyK · Accepted solution]

splunknoob408,
When sorting by time, epoch time (decimal number version) works best. You can convert to a human readable format after.

Try this:

source=OrderEventsTable service="tracking" event_details="shipped on*" 
| regex event_details="\d+/\d+/\d+" 
| dedup order_id 
| eval shipped_date=strptime(shipped_date,"%m/%d/%y") 
| stats count as "shipped number" by shipped_date 
| sort shipped_date
| eval shipped_date=strftime(shipped_date,"%m/%d/%y") 

[splunknoob408]

Awesome answer, thank you so much for the help! This totally worked.

[niketn]

@MonkeyK, after sorting epoch date, I would use fieldformat instead of the last eval. So that field remains epoch date and value converts to string date.

 | fieldformat shipped_date=strftime(shipped_date,"%m/%d/%y") 

[MonkeyK]

a new command! thank you! This will be useful in a number of my existing queries