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")
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,
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")
Awesome answer, thank you so much for the help! This totally worked.
@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")
a new command! thank you! This will be useful in a number of my existing queries