topic Re: sorting date in Splunk Search

How can I sort data into a graph?

karthikTIL — Fri, 28 Apr 2023 13:50:42 GMT

I have a table like below.
Date, count
22/12/2014,9
23/12/2014,19
24/12/2014,16
6/01/2015,4
7/01/2015,7
4/02/2015,42
5/02/2015,17

when i use below query, i don't see date in sorted order in my graph. Could you please help how to get graph in sorted order by date.
source=abcd.csv|fields Date,count|stats by Date,count

the output is like below, which is not what i want
22/12/2014,9
23/12/2014,19
24/12/2014,16
4/02/2015,42
5/02/2015,17
6/01/2015,4
7/01/2015,7

Re: sorting date

MuS — Wed, 11 Feb 2015 08:09:33 GMT

Hi karthikTIL,

the problem here is that Splunk is not aware that your Date field represents a time value; for Splunk it is a simple numeric value and therefore it sorts the value based on the first digits before the first /. You need to tell Splunk that this is a time based field, sort it and revert it back to your human readable date value like this:

... | fields Date,count | stats by Date,count | eval Date=strptime(Date, "%d/%m/%Y") | sort Date | eval Date=strftime(Date, "%d/%m/%Y")

Hope this helps ...

cheers, MuS

Re: sorting date

DavidHourani — Wed, 11 Feb 2015 08:28:29 GMT

Hello Karth,

Try this:

 source=abcd.csv | fields Date,count | eval _time=strptime(Date,"%d/%m/%Y") | stats count by _time,count

Regards,
David

Re: sorting date

karthikTIL — Wed, 11 Feb 2015 22:38:36 GMT

Thanks MuS,

It works

Re: sorting date

karthikTIL — Wed, 11 Feb 2015 22:38:59 GMT

Thanks David

Re: sorting date

karthikTIL — Tue, 24 Mar 2015 02:10:10 GMT

HI MuS,

with the above query, is it possible to get total count by week and month?
say,
16/03/2015,10
18/03/2015,20
23/03/2015,5
24/03/2015,15

I want the count for week ending 22/mar as 30 and week ending 29/mar as 20. Like wise, need to monthly for february/march,etc

Re: sorting date

revathy1993 — Mon, 06 Jan 2020 16:13:01 GMT

Hi MuS,

After sorting it using the above way, once we create a dashboard how to restrict it from sorting on the screen because that arrow sorts as normal numeric again.

Thank you in advance,
Revathy.

Re: sorting date

MuS — Mon, 06 Jan 2020 19:53:53 GMT

Hi revathy1993,

Check this answer https://answers.splunk.com/answers/714651/tableview-in-javascript-prevent-sorting-or-capture.html this depending on your Splunk version this might work. There are older examples for older Splunk version on answers as well, just search for them :

https://www.google.com/search?q=site:answers.splunk.com+disable+sort+in+table

cheers, MuS

Re: sorting date

ohbuckeyeio — Thu, 27 Apr 2023 18:54:56 GMT

Old post but this is how I fixed it in a dashboard.

Convert to epoch, rename what you want the final field to be, then apply fieldformat to the result.

| eval admin_appcreatedon=strptime(admin_appcreatedon,"%m/%d/%Y") | rename admin_appcreatedon as "Created On" | fieldformat "Created On" = strftime('Created On', "%m/%d/%Y")