Splunk Search
Highlighted

sorting date

Path Finder

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

Tags (2)
0 Karma
Highlighted

Re: sorting date

SplunkTrust
SplunkTrust

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

View solution in original post

Highlighted

Re: sorting date

Path Finder

Thanks MuS,

It works

0 Karma
Highlighted

Re: sorting date

Path Finder

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

0 Karma
Highlighted

Re: sorting date

SplunkTrust
SplunkTrust

Hello Karth,

Try this:

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

Regards,
David

0 Karma
Highlighted

Re: sorting date

Path Finder

Thanks David

0 Karma
Highlighted

Re: sorting date

New Member

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.
alt text

0 Karma
Highlighted

Re: sorting date

SplunkTrust
SplunkTrust

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

0 Karma