Splunk Search

## What is the best way to get the running average and standard deviations for external port connects?

Contributor

So I am looking at cisco asa logs and wondering what the best way method would be to create an alert when the number of external connection attempts to port 23 (in my network) is +/- 2 standard deviations from the daily average.

Thank you

Tags (5)
1 Solution
Legend

There are lots of ways to approach this - but the first question is: how do you define the daily average? Do you want to take into account the variability throughout the day? For example, perhaps the number of attempts averages 1000 at 09:00 but 500 at 22:00
Depending on your answer to this question, establishing your alert threshold on-the-fly could get pretty expensive. One solution would be to create a lookup table of the thresholds, perhaps like this:

``````index=x sourcetype=asa your search here earliest=-30d@d latest=@d
| eval hour=strftime(_time,"%H")
| bin _time span=d
| stats count by _time hour
| stats avg(count) as Average stdev(count) as StdDev by hour
| outputlookup thresholds_lookup
``````

Schedule this search to run once a day, to update the thresholds. Or just manually create a lookup table where you specify what you want for a threshold. Now you don't need to calculate the average or std deviation repeatedly, you can just look it up.
Then, create a search that actually alerts you - maybe run it once an hour:

``````index=x sourcetype=asa your search here earliest=-1h@h latest=@h
| bin _time span=1h
| stats count by _time
| eval hour=strftime(_time,"%H")
| lookup thresholds_lookup hour OUTPUT Average StdDev
| where count < (Average - (2*StdDev)) OR count > (Average + (2*StdDev))
| table _time count Average StdDev
``````

And alert when the number of results > 0

I hope this gives you some ideas. Oh and here is the documentation for the outputlookup command to get you started...

Legend

There are lots of ways to approach this - but the first question is: how do you define the daily average? Do you want to take into account the variability throughout the day? For example, perhaps the number of attempts averages 1000 at 09:00 but 500 at 22:00
Depending on your answer to this question, establishing your alert threshold on-the-fly could get pretty expensive. One solution would be to create a lookup table of the thresholds, perhaps like this:

``````index=x sourcetype=asa your search here earliest=-30d@d latest=@d
| eval hour=strftime(_time,"%H")
| bin _time span=d
| stats count by _time hour
| stats avg(count) as Average stdev(count) as StdDev by hour
| outputlookup thresholds_lookup
``````

Schedule this search to run once a day, to update the thresholds. Or just manually create a lookup table where you specify what you want for a threshold. Now you don't need to calculate the average or std deviation repeatedly, you can just look it up.
Then, create a search that actually alerts you - maybe run it once an hour:

``````index=x sourcetype=asa your search here earliest=-1h@h latest=@h
| bin _time span=1h
| stats count by _time
| eval hour=strftime(_time,"%H")
| lookup thresholds_lookup hour OUTPUT Average StdDev
| where count < (Average - (2*StdDev)) OR count > (Average + (2*StdDev))
| table _time count Average StdDev
``````

And alert when the number of results > 0

I hope this gives you some ideas. Oh and here is the documentation for the outputlookup command to get you started...

Contributor

I initially started with:

``````index=main sourcetype=cisco:asa  dest_port=23 action=blocked  direction=Inbound | timechart  span=1d count as D_Count | appendpipe [stats avg(D_Count) as D_AVG] | appendpipe [stats stdev(D_Count) as SDev by _time] | table *
``````

and could not figure out the alert....
but your code is way better....

Thank you Lisa!!

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 ...

#### Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

#### SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...