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!

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