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
Try this:
...your query (supposing your date field name is "date")...
| eval date = strptime(date,"%m/%d/%Y")
| sort - date
| eval date = strftime(date,"%m/%d/%Y")
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
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
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>
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
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.
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
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.
Sorry somesoni2 I made a change in the question the field using the dropdown is an extracted field from raw data
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