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
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
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
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.
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" ]
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
Are you shure it is working? Replace the = < by <=, and let me know if this ok.
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
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
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]
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....
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
Please, don't forget to vote me!
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).
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