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!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...