Splunk Search

How can I sort data into a graph?

karthikTIL
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

Labels (1)
Tags (2)
0 Karma
1 Solution

MuS
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

revathy1993
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

ohbuckeyeio
Path Finder

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")

 

0 Karma

MuS
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

DavidHourani
Super Champion

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

karthikTIL
Path Finder

Thanks David

0 Karma

MuS
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

karthikTIL
Path Finder

Thanks MuS,

It works

0 Karma

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

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

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

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...