topic how to sort date in header level in Splunk Search

how to sort date in header level

avikc100 — Thu, 04 Jan 2024 22:54:16 GMT

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

Re: how to sort date in header level

yuanliu — Thu, 04 Jan 2024 23:21:01 GMT

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

Re: how to sort date in header level

dtburrows3 — Fri, 05 Jan 2024 00:10:42 GMT

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

Re: how to sort date in header level

inventsekar — Fri, 05 Jan 2024 00:51:27 GMT

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.