How to write this Splunk Query for alert?

[jakeoftrades]

hi,

Can someone help to correct the query provided below which will send alert if detected a STOPPED status for 3 consecutive times within a specific time range like for ex. from 7am-8pm.

index="hcg_ph_t2_bigdataservices_prod" sourcetype="be:streaming-services"
| search kafka_count="STOPPED"
| stats count by _time,sourcetype,STOPPED
| sort count desc
| eval threshold=3
| where count >=threshold

[venky1544]

Hi @jakeoftrades 

try this  first your search alone would not trigger alert you need to save the search as alert and configure the schedule and alert conditions and setup the desired notifications of your choice 

index="hcg_ph_t2_bigdataservices_prod" sourcetype="be:streaming-services" kafka_count="STOPPED"
|bin _time span=1h | stats count by kafka_count

save this search for alert

schedule it for every hour in the alert and setup the trigger condition search count >=3 

[jakeoftrades]

thanks it's showing the stat I needed to set alert

[gcusello]

Hi @jakeoftrades,

please try something like this:

index="hcg_ph_t2_bigdataservices_prod" sourcetype="be:streaming-services" kafka_count="STOPPED" 
| stats count
| where count>=3

if you have results in this search youcan trigger the alert (triggering conditions in alert results>0).

You don't need to put a search condition after the main search: it's better to put all the conditions ad left as possible.

then you don't need the other conditions in the stats command.

Ciao.

Giuseppe

[jakeoftrades]

I can't figure out where and how I should set the alert only to detect results specifically 7am-8pm in the trigger alert Time Range or should it be included in the query 

[jakeoftrades]

Thank you.. It shows the specific status that is STOPPED with it's count.

By that then I can set the alert only to trigger for that status only between 7am-8pm ?

[gcusello]

Hi @jakeoftrades,

you have two choices:

• put a limit in the time period of your search as @VatsalJagani hinted,
• add an additional filter to your main search, al the following.

index="hcg_ph_t2_bigdataservices_prod" sourcetype="be:streaming-services" kafka_count="STOPPED" (date_hour>7 date_hour<21)
| stats count
| where count>=3

if you haven't date_hour field, you have to extract it using an eval command:

index="hcg_ph_t2_bigdataservices_prod" sourcetype="be:streaming-services" kafka_count="STOPPED" 
| eval date_hour=strftime(_time,"%H")
| where date_hour>7 AND date_hour<21
| stats count
| where count>=3

Ciao.

Giuseppe

[VatsalJagani · Accepted solution]

Use time range as earliest=-1h@h & latest=@h

And cron for the alert as 

2 8-20 * * *

- cron basically means your alert will run 8:02 to 20:02 (8:02 PM) for the last 1 hour of time.

- You can run it at the 0th minute but it's good to run it 1 or 2 minute past the hour. So I'm running at the 2nd minute of every hour.

[jakeoftrades]

Is this right .I did this: 

[jakeoftrades]

I think I should have change the number of results which  is = 3 instead of 0 by the condition that it will only send alert.. it has been sending alert even without result.

[VatsalJagani]

yes, that looks alright.

[jakeoftrades]

Thank you @VatsalJagani  I appreciate it !