Splunk Search

How to write this Splunk Query for alert?

jakeoftrades
Explorer

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

 

Labels (3)
Tags (2)
0 Karma
1 Solution

VatsalJagani
Champion

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.

View solution in original post

Tags (1)

venky1544
Contributor

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 

0 Karma

jakeoftrades
Explorer

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

0 Karma

gcusello
Legend

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
Explorer

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 

Tags (1)
0 Karma

jakeoftrades
Explorer

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 ?

0 Karma

gcusello
Legend

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
Champion

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.

Tags (1)

jakeoftrades
Explorer

Is this right .I did this: 

jakeoftrades_1-1648454173744.png

 

jakeoftrades_0-1648453920759.png

 

Tags (2)
0 Karma

jakeoftrades
Explorer

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.

 

0 Karma

VatsalJagani
Champion

yes, that looks alright.

0 Karma

jakeoftrades
Explorer

Thank you @VatsalJagani  I appreciate it !

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...