Splunk Search

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

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

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

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

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