Splunk Search
Highlighted

Search using Stats, String Dates, Counts and Sorting

Path Finder

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?

Tags (4)
0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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

View solution in original post

Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Path Finder

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

0 Karma