topic Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0? in Splunk Search

How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

Norling80 — Mon, 13 Apr 2015 07:32:31 GMT

Hi guys.

I need some help to to understand what's wrong with this search. I want this one to return one count for each minute that has < 10 events, it does but not for the minutes where the event count is 0. Any idea what to change in order to get it working?

sourcetype = access_combined | bucket span=1m _time | stats count as requests_per_minute by _time | where requests_per_minute  = <10 | stats count as downtime_duration

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

ngatchasandra — Mon, 13 Apr 2015 07:46:17 GMT

HI,
Try with this!

This query display all count by minute!

sourcetype = access_combined   |  stats count as requests_per_minute by _time | where requests_per_minute <=10 | timechart span=1m count as downtime_duration

Or try with
This query display all count (also 0) by minute, but the lines that contain count Not equal 0 are display with value = 1

 sourcetype = access_combined  | bucket span=1m _time  |  stats count as requests_per_minute by _time | where requests_per_minute <=10 | timechart span=1m count as downtime_duration

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

stephanefotso — Mon, 13 Apr 2015 07:47:32 GMT

Are you shure it is working? Replace the = < by <=, and let me know if this ok.

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

Norling80 — Mon, 13 Apr 2015 12:21:43 GMT

Thanks, but unfortunately is still does not count the minutes with 0 events, any ideas on how to include that as-well? And I also want this in a statistical output with a simple | stats count that returns the amount on minutes that had <10 events (including the minutes with 0 events).

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

sideview — Mon, 13 Apr 2015 17:54:26 GMT

To see the problem you have to remember that stats count as requests_per_minute by _time is really nice and dumb. Rows come into it from the left and for each value of _time that stats sees, it'll make an output row, and count up how many input rows had that value of time. However for _time values for which no events come in, it will do nothing. In other words it has no idea that _time is a continuous value and that it should be making other rows with count=0 for _time values that it has never seen.

Fortunately there is a function that is excellent at creating these blank rows for you, timechart. So this should give you what you need.

sourcetype = access_combined | timechart count as requests_per_minute span=1min |  where requests_per_minute <=10 | stats count as downtime_duration

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

ngatchasandra — Mon, 13 Apr 2015 21:28:16 GMT

This is the query that display the count of events for each minute :

index=_ineternal | timechart  span=1m count as requests_per_minute

let us filter:

index=_ineternal | timechart  span=1m count as requests_per_minute |  where requests_per_minute  <10 | stats count as downtime_duration

Let us count the amount on minutes that had <10 events (including the minutes with 0 events).

index=_internal | timechart  span=1m count as requests_per_minute  |  where requests_per_minute  <10 | stats count as downtime_duration

Your final request will count also the minutes with 0 events

sourcetype=access_combined | timechart  span=1m count as requests_per_minute   |  where requests_per_minute  <10 | stats count as downtime_duration

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

fdi01 — Tue, 14 Apr 2015 08:12:04 GMT

If you want count the minutes that have zero events try like this :
...| timechart span=1m count as requests_per_minute by _time | where requests_per_minute = 0 | stats count as " count of minutes that have zero events"

if else see answer of Mr sideview.

but if you want count the minutes with 0 events and the amount on minutes that had <10 events try like this:

sourcetype = access_combined | timechart count as requests_per_minute span=1min |  where requests_per_minute <=10 | stats count as downtime_duration | appendcols [search sourcetype = access_combined | timechart count as requests_per_minute span=1min |  where requests_per_minute =0 | stats count as " count of minutes that have zero events" ]

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

Norling80 — Mon, 28 Sep 2020 19:32:28 GMT

Thanks Ngatchasandra, that did the trick. one more question about this

The downtime_duration is not displayed as a field in the log, so how do I do if I want to see the downtime_duration on different HostNames in my Jboss accesslog, something like this...

index=jboss_accesslog | timechart count as requests_per_minute by _time span=1m | where requests_per_minute <10 | stats count as downtime_duration by HostName

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

ngatchasandra — Thu, 16 Apr 2015 03:33:32 GMT

You can do this with streamstats command and join command (allow you to macth to his downtime_duration): try to run query that follow:

sourcetype=access_combined| timechart span=1m count as requests_per_minute| where requests_per_minute <10 | streamstats count as downtime_duration by HostName |join  [search sourcetype=access_combined | table HostName]

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

Norling80 — Mon, 28 Sep 2020 19:32:41 GMT

Thanks, almost there I think. I did not get any result in return when i did as you suggested so I tried a couple of different things and did some minor modifications to it,

gave this result in the dashboard table view:
Downtime_Duration HostName
13 hostname.domain.com

So the only problem right now is that I only see one HostName in the table and not all of them, worth to mention is that the hostnames are added in the base search like this HostName="hostname1" OR HostName="Hostname2" etc....

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

ngatchasandra — Thu, 16 Apr 2015 21:12:29 GMT

Hi,
I think that i resolved your probllem now with untable command which let me to obtain that you want! Only run the query that folllow:

 sourcetype=access_combined | timechart span=1m count as requests_per_minute by Hostname |untable _time  Hostname requests_per_minute | where requests_per_minute <100 | stats count as Downtime_Duration by Hostname

This is work fine.

I tried to test it with _internal index like follow:

 index=_internal | timechart span=1m count as requests_per_minute by sourcetype |untable _time  sourcetype requests_per_minute | where requests_per_minute <100 | stats count as Downtime_Duration by sourcetype

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

ngatchasandra — Fri, 17 Apr 2015 17:27:37 GMT

Please, don't forget to vote me!

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

woodcock — Mon, 28 Sep 2020 19:39:34 GMT

I believe that y'all are way overthinking this; what is wrong with this?



sourcetype = access_combined | bucket _time span=1m | stats count AS requests_per_minute BY _time,HostName | stats count(eval(requests_per_minute<10)) AS downtime_duration BY HostName

Re: How to edit my search to return one count for each minute that has < 10 events, including minutes where event count is 0?

sideview — Wed, 22 Apr 2015 04:28:36 GMT

It will end up only counting times when the requests per minute is less than 10 but also greater than 0. Basically if you think about time periods in which there are zero events, for these _time values there will be no rows at all coming into your stats command. See my answer for more details.