topic Search using Stats, String Dates, Counts and Sorting in Splunk Search

Search using Stats, String Dates, Counts and Sorting

htkhtk — Wed, 12 Jan 2011 00:12:58 GMT

What I am trying to do is to get a listing of the last 7 days (that logs were entered - not necessarily the last 7 calendar days) and how many completed requests the logs have seen during those days.

Here is the query I am using:

sourcetype="server_log" complete* | convert timeformat="%m-%d" ctime(_time) as compact_date | stats dc(requestid) as "NumRequests" by compact_date | rename compact_date as RequestDate | tail 7

The problem I am seeing is that the compact_date values are not sorted correctly. I am assuming this is because they are strings.

How can I sort the days correctly?

Re: Search using Stats, String Dates, Counts and Sorting

Stephen_Sorkin — Wed, 12 Jan 2011 00:26:08 GMT

I'd recommend binning the dates by their UTC time representation rather than by convert. You could use either timechart or bin+stats to acheive this:

sourcetype="server_log" complete* earliest=-7d@d latest=@d
| timechart span=1d dc(requestid) as NumRequests
| convert timeformat="%m-%d" ctime(_time) as compact_date
| table compact_date NumRequests

Or:

sourcetype="server_log" complete* earliest=-7d@d latest=@d
| bin span=1d _time
| stats dc(requestid) as NumRequests by _time
| convert timeformat="%m-%d" ctime(_time) as compact_date
| table compact_date NumRequests

Re: Search using Stats, String Dates, Counts and Sorting

htkhtk — Wed, 12 Jan 2011 00:35:51 GMT

I wrote this question wrong.. I don't need the latest 7 days. I need the last 7 days that logs were written. So for example, it could be 11-27, 11-28, 12-02, 01-03

Re: Search using Stats, String Dates, Counts and Sorting

htkhtk — Wed, 12 Jan 2011 00:38:46 GMT

This helped me solve my problem though! I didn't know about bin and span. See this:

sourcetype="server_log" complete* | bin span=1d _time | dedup requestid | stats count by _time | sort _time desc