Splunk Search

Help with search for sum of same field twice over different time periods and set up alert

TylerJVitale
Explorer

I want to set up an alert to trigger if three conditions are met:

  1. Volume of a particular app is above 100 over the last 4 hours
  2. Total volume is above 1000 over the last 4 hours
  3. Total Volume over the last hour is at least 100

Right now my search is:

| stats sum(VOLUME) as VOLUME, count(USERNAME) as AFFECTED_USERS by APPLICATION | eventstats sum(VOLUME) as TOTAL|
 join [ search | stats sum(VOLUME) as TOTAL_VOLUME earliest=-1h ]|where TOTAL > 1000 and VOLUME>100 and TOTAL_VOLUME>100

How can I tweak my search to get what I'm looking for?

0 Karma

woodcock
Esteemed Legend

Like this (I tested by starting with index=_* earliest = -240m | rename component AS APPLICATION, host AS USERNAME:

index=<You Should Always Specify An index> AND sourcetype=<And sourcetype Too> earliest = -240m
| eval time=if(_time >= relative_time(now(), "-60m"), "last_hour", "3_hours_before_last_hour") 
| stats count AS PER_APP_VOLUME, dc(USERNAME) AS AFFECTED_USERS count(USERNAME) AS INCIDENT_COUNT BY APPLICATION time 
| eventstats sum(PER_APP_VOLUME) AS TOTAL_VOLUME BY time 
| foreach last_hour 3_hours_before_last_hour 
    [| eval ALL_APP_VOLUME_<<FIELD>> = if(time == "<<FIELD>>", TOTAL_VOLUME, null()) ] 
| eventstats sum(TOTAL_VOLUME) AS ALL_APP_VOLUME_all_4_hours sum(PER_APP_VOLUME) AS PER_APP_VOLUME_all_4_hours BY APPLICATION 
| fields - TOTAL_VOLUME 
| where time="last_hour" 
| foreach PER_APP_VOLUME AFFECTED_USERS INCIDENT_COUNT 
    [ rename <<FIELD>> AS <<FIELD>>_last_hour ] 
| fields - time 
| table APPLICATION * 
| where PER_APP_VOLUME_all_4_hours > 100 AND ALL_APP_VOLUME_all_4_hours > 1000 AND ALL_APP_VOLUME_last_hour > 100
0 Karma

woodcock
Esteemed Legend

Beware, I re-edited this answer 10 times in the last hour; be sure you reload the page and get the latest version.

0 Karma

tiagofbmm
Influencer
| makeresults 
| eval volume=10, app="app_A", _time=relative_time(now(),"-1h")
| append [ | makeresults 
| eval volume=200, app="app_A", _time=relative_time(now(),"-2h") ]
| append [ | makeresults 
| eval volume=90, app="app_A", _time=relative_time(now(),"-3h") ]
| append [
| makeresults 
| eval volume=30, app="app_B", _time=relative_time(now(),"-1h")
| append [ | makeresults 
| eval volume=600, app="app_B", _time=relative_time(now(),"-2h") ]
| append [ | makeresults 
| eval volume=150, app="app_B", _time=relative_time(now(),"-3h") ] ]
| append [
| makeresults 
| eval volume=40, app="app_B", _time=relative_time(now(),"-1m")
| append [ | makeresults 
| eval volume=12, app="app_B", _time=relative_time(now(),"-2m") ]
| append [ | makeresults 
| eval volume=34, app="app_B", _time=relative_time(now(),"-3m") ] ]
| append [
| makeresults 
| eval volume=10, app="app_A", _time=relative_time(now(),"-1m")
| append [ | makeresults 
| eval volume=20, app="app_A", _time=relative_time(now(),"-2m") ]
| append [ | makeresults 
| eval volume=30, app="app_A", _time=relative_time(now(),"-3m") ] ]
| timechart span=1h sum(volume) as Total by app
| addtotals fieldname=HourlyTotal
| foreach app_* [ eval hourly_<<MATCHSTR>>=if(app_<<MATCHSTR>>>100,1,0)]
| stats last(app_*) as last_hour_app_*, max(hourly_*) as hourly_max_app_*, sum(HourlyTotal) as Total

Try this sample code and see if the logic works for you

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...