How to trigger an alert if the condition remains for a certain period of time?

[splunking1]

I am trying to create an alert when the field toState changes to OPEN and stays in that OPEN state for 5 minutes. I have tried the following but it is not working. Would appreciate if I get some pointers. 

... CB_STATE_TRANSITION | timechart span=5m count(toState="OPEN") as state | stats count | where count > 1

I have the alert run every 5 minutes and triggers when the number of results > 0. 

[richgalloway · Accepted solution]

Perhaps this will help.

... CB_STATE_TRANSITION 
``` Get the most recent time and state ```
| stats latest(_time) as _time, latest(toState) as toState
``` Keep only the "OPEN" events with a timestamp at least 5 minutes old ```
| where (toState="OPEN" AND _time < relative_time(now(), "-5m")

[splunking1]

I appreciate the reply. I am curious about the issue with my approach. Conceptually, it makes sense. What is the problem with that approach? Just trying to understand. 

[richgalloway]

You said your query was "not working" without qualification so I offered a query that should work.

Let's take a closer look at the original query.  The transaction command counts the number of "OPEN" events in each 5-minute period of the search time window.  Depending the time window chosen, this will produce one or more results.

| timechart span=5m count(toState="OPEN") as state

_time	state
01/29/2023 14:30	0
01/29/2023 14:35	0
01/29/2023 14:40	0

The stats command then counts the number of results produced by timechart.

| stats count | where count > 1

Giving us "3", which is greater than 1.  That will erroneously trigger an alert.

Let's say we run the query over the previous 5 minutes with known "OPEN" events.  The timechart command might produce something like this

_time	state
01/29/2023 14:45	3

This time the stats command will return "1", which is not greater than 1 and so the alert will erroneously NOT trigger.

Like a broken clock, the query will occasionally work, but the false positives and false negatives make it unreliable.

The query could be improved by removing the stats command.

... CB_STATE_TRANSITION | timechart span=5m count(toState="OPEN") as state | where count > 1

This will give us only the 5-minute periods where an "OPEN" event occurred and the alert can be triggered if there are results.  That doesn't mean, however, that the "OPEN" was present for all 5 of those minutes.

[splunking1]

That makes a lot of sense. Thank you so much. One final question: If I were to extend your query to different host; would it still work?

....CB_STATE_TRANSITION | stats latest(_time) as _time, latest(toState) as toState by host | where (toState="OPEN" AND _time < relative_time(now(), "-5m"))

I have different hosts and it is possible that the alert does not trigger for one of them even though the state was set to open for the last 5 minutes due to the state transitioning to closed. That would be a false positive so I want to account for each host separately. I will accept your answer once this thread is closed so don't worry 🙂

[richgalloway]

Yes, adding "by host" should work as you expect.