Splunk Search
Highlighted

How do you sort dates from newest to oldest in a drop down?

Builder

I have a drop down which populates the dates in MM/DD/YYYY format, which is an extracted field in the raw data. I want this field in the drop down to populate from newest to oldest. I tried using sort, but that is not working. Are there any other commands that would sort this?

Thanks,
Vineeth

0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

SplunkTrust
SplunkTrust

Assuming you're populating your dropdown from "string" version of dates, the sorting will be done alphabetically. In order to retain the sorting chronologically, retain/convert the date values in epoch format, sort it per your need and then convert it to string format.

0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

Builder

Sorry somesoni2 I made a change in the question the field using the dropdown is an extracted field from raw data

0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

SplunkTrust
SplunkTrust

Assuming you're extracting it in string format. The convert it to epoch format using eval-strptime , sort and then convert it back to required format using eval-strftime function.
https://docs.splunk.com/Documentation/Splunk/7.2.3/SearchReference/DateandTimeFunctions

0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

Builder

Maybe before converting the date to MM/DD/YY first do a sort 0 or instead you can try using

| reverse

before parsing the date

0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

Contributor

Hi,

Here is the run anywhere code,

<form>
  <label>demo</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="field1">
      <label>field1</label>
      <fieldForLabel>date</fieldForLabel>
      <fieldForValue>date</fieldForValue>
      <search>
        <query>| makeresults count=3
| streamstats count as id 
| eval date = case(id=1,"01-10-2019 00:00:00",id=2,"01-11-2019 00:00:00",id=3,"01-12-2019 00:00:00")
| eval date_epoch = strptime(date,"%m-%d-%Y %H:%M:%S")
| table date,date_epoch
| sort - date_epoch</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
    </input>
  </fieldset>
</form>
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

Builder

Thank you for your reply.I had edited the question as the field that populates is not from _time it is a field in the raw data .sorry for the confusion

0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

Contributor

My answer is still going to be the same. Basically the idea is to you need to extract the date from _raw using rex command then convert it to epoch and then sort on the epoch.

0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

Champion

Hi @vrmandadi

I hope the below code will help you

index="_internal" 
 | eval date = strftime(_time,"%m/%d/%Y") 
 | eval date_month=strftime(_time,"%m") 
 | eval date_year=strftime(_time,"%Y") 
 | eval date_date=strftime(_time,"%d") 
 | stats values(date) as date by date_month,date_date,date_year 
 | sort - date_year,date_month,date_date | fields - date_year,date_month,date_date
0 Karma
Highlighted

Re: How do you sort dates from newest to oldest in a drop down?

Esteemed Legend

You need the fieldformat command; do it like this:

| makeresults 
| eval date = "01-10-2019 00:00:00:::01-11-2019 00:00:00:::01-12-2019 00:00:00" 
| makemv delim=":::" date 
| mvexpand date 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval date = strptime(date,"%m-%d-%Y %H:%M:%S") 
| fieldformat date = strftime(date,"%m-%d-%Y %H:%M:%S") 
| sort - date
0 Karma