topic Re: Sorting Date in Splunk Search

Sorting Date

dsmeerkat — Thu, 03 Apr 2014 13:28:48 GMT

Okay so I missing something...

Here's my searches:

index=_internal source=*license_usage.log type=Usage | eval GB=b/1024/1024/1024 | timechart span=1d sum(GB) by pool useother=false | fieldformat Total=round(Total, 2) |rename auto_generated_pool_enterprise AS Total | tscollect namespace=License_Daily_Usage_7d keepresults=true

AND

| tstats sum(GB) AS Total, values(Total), values(Date) FROM License_Daily_Usage_7d groupby Date |  rename values(Total) AS Total_GB | convert timeformat="%a,  %m/%d/%y" ctime(_time) AS Date | sort _time | fields - _time | table Date, Total_GB

And when the show up in the dashboard they are not being sorted by "Date" correctly...its doing:

Fri, 03/28/14 943.270143

Mon, 03/31/14 900.663402

Sat, 03/29/14 836.616432

Sun, 03/30/14 779.676332

Thu, 03/27/14 487.159979

Thu, 04/03/14 514.808743

Tue, 04/01/14 965.568267

Wed, 04/02/14 1031.553619

I've tried sorting by everything I can think of and it just won't sort by %m/%d/%y

Re: Sorting Date

linu1988 — Thu, 03 Apr 2014 13:43:26 GMT

Hello,
Add a dummy column and do the sort and hide it

| tstats sum(GB) AS Total, values(Total), values(Date) FROM License_Daily_Usage_7d groupby Date |  rename values(Total) AS Total_GB |convert timeformat="%a,  %m/%d/%y" ctime(_time) AS Date|eval a=strptime(Date,"%a,  %m/%d/%y") | table Date, Total_GB,a| sort a | fields - a

Thanks

Re: Sorting Date

somesoni2 — Thu, 03 Apr 2014 13:46:13 GMT

You second search (tstats) is not retrieving field _time.

Re: Sorting Date

dsmeerkat — Thu, 03 Apr 2014 13:50:19 GMT

Thanks for the response but its still not working....

Re: Sorting Date

dsmeerkat — Thu, 03 Apr 2014 13:50:35 GMT

Thanks for the response but its still not working....

Re: Sorting Date

linu1988 — Thu, 03 Apr 2014 13:51:38 GMT

Try now, i didn't include the field in table column. This happens due to the date field not being actual date field rather a string..

Re: Sorting Date

dsmeerkat — Thu, 03 Apr 2014 13:59:28 GMT

Still no luck 😞

Re: Sorting Date

aelliott — Thu, 03 Apr 2014 14:07:48 GMT

if you keep the field _time, does it sort?

Re: Sorting Date

dsmeerkat — Thu, 03 Apr 2014 14:08:57 GMT

No 😞 .....

Re: Sorting Date

aelliott — Thu, 03 Apr 2014 14:12:55 GMT

try

|eval MyDate=strptime(Date,"%a, %m/%d/%y") | sort MyDate | table MyDate, Total_GB

Re: Sorting Date

linu1988 — Thu, 03 Apr 2014 14:13:44 GMT

Okay now check the edited one?

Re: Sorting Date

dsmeerkat — Thu, 03 Apr 2014 16:05:44 GMT

index=_internal source=*license_usage.log type=Usage | eval GB=b/1024/1024/1024 | timechart span=1d sum(GB) by pool useother=false |convert ctime(_time) AS Time | tscollect namespace=License_Daily_Usage_7d keepresults=true


| tstats sum(GB) AS Total, values(Total), values(Date) FROM License_Daily_Usage_7d groupby Date |  rename values(Total) AS Total_GB | convert timeformat="%a,  %m/%d/%y" ctime(_time) AS Date | sort _time | fields - _time | eval MyDate=strptime(Date,"%a, %m/%d/%y") | sort MyDate | convert timeformat="%a,  %m/%d/%y" ctime(_time) AS Date| table Date, Total_GB

Works like a charm!