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
Path Finder

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
Tags (2)
1 Solution

ngatchasandra
Builder

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  

View solution in original post

woodcock
Esteemed Legend

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

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

0 Karma

fdi01
Motivator

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" ]
0 Karma

sideview
SplunkTrust
SplunkTrust

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

stephanefotso
Motivator

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

SGF
0 Karma

ngatchasandra
Builder

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  

Norling80
Path Finder

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

0 Karma

ngatchasandra
Builder

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]
0 Karma

Norling80
Path Finder

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,

sourcetype=access_combined | timechart span=1m count as requests_per_minute | where requests_per_minute <100 | stats count as Downtime_Duration | join [search sourcetype=access_combined | table HostName]

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

0 Karma

ngatchasandra
Builder

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
0 Karma

ngatchasandra
Builder

Please, don't forget to vote me!

0 Karma

Norling80
Path Finder

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).

0 Karma

ngatchasandra
Builder

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