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?
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
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
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
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