Splunk Search

Search using Stats, String Dates, Counts and Sorting

htkhtk
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
1 Solution

Stephen_Sorkin
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

Stephen_Sorkin
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

htkhtk
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

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...