Splunk Search

how to sort date in header level

avikc100
Path Finder

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

 

avikc100_0-1704408791144.png

 

Labels (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

inventsekar
SplunkTrust
SplunkTrust

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. 

 

dtburrows3
Builder

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:

dtburrows3_0-1704411303205.png

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

yuanliu
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...