this query showing date &time haphazardly, how to sort it like 1/4/2024, 1/3/2024, 1/2/2024....
index="*" source="*" |eval
timestamp=strftime(_time, "%m/%d/%Y")
| chart limit=30
count as count
over DFOINTERFACE
by timestamp
There is no good way to sort column using mm/dd/yyyy format. What's wrong with yyyy-mm-dd?
index="*" source="*" |eval
timestamp=strftime(_time, "%F")
| chart limit=30
count as count
over DFOINTERFACE
by timestamp
Hi @avikc100
Basically in Splunk the time and date operations should be done like this:
1) Splunk has an event's timestamp in some format (dd-mm-yy aa:bb:cc dddd).
2) convert that to epoch timestamp (use strptime)
----- strptime(<str>, <format>)
------Takes a human readable time, represented by a string, and parses the time into a UNIX timestamp using the format you specify.
3) then do sorting, comparison operations on the epoch timestamp.
4) and then convert back to human readable timestamp (use strftime)
------strftime(<time>,<format>)
------This function takes a UNIX time value and renders the time as a string using the format specified.
if any reply helped you, then, karma / upvotes appreciated, thanks.
Something like this should sort your column in the intended order with the time format requested.
<base_search>
``` bucket _time into each respective day ```
| bucket span=1d _time
``` transform data in a normal Splunk friendly timeseries format ```
| chart
count as count
over _time
by DFOINTERFACE
``` ensure ascending order of _time field ```
| sort 0 +_time
``` format timestamp as desired ```
| eval
timestamp=strftime(_time, "%m/%d/%Y")
``` remove _time field (no longer needed) ```
| fields - _time
``` transpose table (this should retain the sort order of date ```
``` note: transpose has default limits on number of columns that will display. The 25 here is saying allow at the most 25 columns to be available before truncation occurs. ```
| transpose 25 header_field=timestamp column_name=DFOINTERFACE
Example output:
This is basically the same question that was asked here.
https://community.splunk.com/t5/Splunk-Search/how-to-report-based-on-date/m-p/673054
There is no good way to sort column using mm/dd/yyyy format. What's wrong with yyyy-mm-dd?
index="*" source="*" |eval
timestamp=strftime(_time, "%F")
| chart limit=30
count as count
over DFOINTERFACE
by timestamp