Find Consecutive Results in a Time Span

[tkwaller]

Hello

I have created a search that monitors a queue and the number of transactions in a 10 minute span:

index=tt ListingQueue earliest=-2h | timechart span=10min count | eval hour=strftime(_time,"%H:%M") | stats sum(count) as count by hour | fillnull value=NULL | eval status=if(count = 0, "TRUE", "FALSE") |

I had to fillnull since the results that are null is what I am looking for but the blank field is not NULL. This search returns results like this:

hour    count   status
08:40   14884   FALSE
08:50   19544   FALSE
09:00   30952   FALSE
09:10   18558   FALSE

I have this setup to run every 30 minutes. The problem is that every 30 minutes the alert condition is met as the 10 minute span changes and the count = 0 before it starts counting again in the 10 minute time frame.

What I am trying to get it to do it alert me if status = TRUE 2 times in a row. For example:

hour    count   status
09:20   14884   TRUE
09:30   19544   TRUE

Any ideas on how I can accomplish this?
Thanks for the help

[acharlieh · Accepted solution]

You could use streamstats to get the previous and current value of count onto each event

Let's refine your search:

index=tt ListingQueue earliest=-2h 
| timechart span=10min count
| eval hour=strftime(_time,"%H:%M")
| streamstats current=f window=1 last(count) as last_count 
| table hour count last_count

Now your alert condition could be where count = 0 AND last_count=0

[tkwaller]

Ah yes this works. I knew I was going about it wrong. Thanks for the help sir!