Splunk Search

Creating alert when *any* past hour count is x standard deviations from the average of the past 24 hours

jojopup123
Explorer

I have Splunk logs with data that is roughly like this:

TimestampadapterNameresponseCode
xxA1
xxA2
xxB1
xxB2

  

For each combination of (adapterName, responseCode), I want to:

1. determine its average count for the last 24 hours (excluding the most recent 1 hour)

2. determine its count for the most recent hour

3. create alerts if its last hour count exceeds 1 standard deviation from the average counts of the last 24 hours

There are other similar questions, and I have referenced them (e.g. https://community.splunk.com/t5/Alerting/Alert-when-sample-is-2-standard-deviations-from-moving-aver...). However, the difference is that I have multiple (adapterName, responseCode) combinations, so the query is not as straightforward and I cannot use `time chart` etc easily.

Here's my own attempt so far:

 

index=itms "searchTerm" earliest=-25h@h latest=@h
| rex "\"adapterName[\":]*(?<adapter>[A-Z]+)\""
| rex "\"name[\":]*(?<responseCode>[\w]+)\""
| eval StartTime=relative_time(now(), "-1h@h")
| eval series=if(_time>=StartTime, "lastHour", "last24Hours")
| bin _time span=1h
| stats count As hourlyCount BY adapter, responseCode, series, _time
| stats avg(hourlyCount) AS averageCount stdev(hourlyCount) as standardDev by adapter, responseCode, series

 

And I am very stuck with a table like this:

adapterNameresponseCodeseriesaverageCountstandardDev
A1lastHour...0
A1last24Hours......
A2lastHour...0
A2last24Hours......
B1lastHour...0
B1last24Hours......
B2lastHour...0
B2last24Hours......

And I have no idea how to proceed. I think the query also made the table more complicated, but I am not sure. Can someone give some advice here?

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

ITWhisperer
SplunkTrust
SplunkTrust
index=itms "searchTerm" earliest=-25h@h latest=@h
| rex "\"adapterName[\":]*(?<adapter>[A-Z]+)\""
| rex "\"name[\":]*(?<responseCode>[\w]+)\""
| eval StartTime=relative_time(now(), "-1h@h")
| eval series=if(_time>=StartTime, "lastHour", "last24Hours")
| bin _time span=1h
| stats count As hourlyCount BY adapter, responseCode, series, _time
| stats avg(hourlyCount) AS averageCount stdev(hourlyCount) as standardDev by adapter, responseCode, series
``` as you had it ```
``` keep 24hour std ```
| eval standardDev=if(series="last24Hours", standardDev, null())
``` save 24hour avg ```
| eval average24Count=if(series="last24Hours", averageCount, null())
``` lose original 24hour avg ```
| eval averageCount=if(series="last24Hours", null(), averageCount)
``` gather values by adapter and response code (there should only be one value for each) ```
| stats values(standardDev) as standardDev values(averageCount) as averageCount values(average24Count) as average24Count by adapter, responseCode
``` calculate if averageCout (last hour) is more than 1 std from 24h avg ```
| eval alert=if(averageCount < average24Count - standardDev OR averageCount > average24Count + standardDev, 1, 0)
| where alert=1

View solution in original post

0 Karma

jojopup123
Explorer

Thanks so much! Appreciate the step-by-step comments as well

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=itms "searchTerm" earliest=-25h@h latest=@h
| rex "\"adapterName[\":]*(?<adapter>[A-Z]+)\""
| rex "\"name[\":]*(?<responseCode>[\w]+)\""
| eval StartTime=relative_time(now(), "-1h@h")
| eval series=if(_time>=StartTime, "lastHour", "last24Hours")
| bin _time span=1h
| stats count As hourlyCount BY adapter, responseCode, series, _time
| stats avg(hourlyCount) AS averageCount stdev(hourlyCount) as standardDev by adapter, responseCode, series
``` as you had it ```
``` keep 24hour std ```
| eval standardDev=if(series="last24Hours", standardDev, null())
``` save 24hour avg ```
| eval average24Count=if(series="last24Hours", averageCount, null())
``` lose original 24hour avg ```
| eval averageCount=if(series="last24Hours", null(), averageCount)
``` gather values by adapter and response code (there should only be one value for each) ```
| stats values(standardDev) as standardDev values(averageCount) as averageCount values(average24Count) as average24Count by adapter, responseCode
``` calculate if averageCout (last hour) is more than 1 std from 24h avg ```
| eval alert=if(averageCount < average24Count - standardDev OR averageCount > average24Count + standardDev, 1, 0)
| where alert=1
0 Karma
Get Updates on the Splunk Community!

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...