Splunk Search

Conditional Sum

rackersmt
Explorer

I'm trying to create a report of domain accounts locked out by caller_computer_name. However, I want to alert if the total lockout count exceeds a threshold for a given account. The problem is that one computer can lockout an account 5 times, and another 16 times, and that exceeds the threshold, but not individually. The threshold is by account, not by account and computer, but I also want to see the caller computer. Any ideas? The last command in the string below doesn't actually work, but it gives you an idea of what I'm trying to do.

EventCode=4740 earliest=-24h | rex field=_raw "(?s)(?i)Account\sThat\sWas\sLocked\sOut.+?Account\sName:\s+(?\S*)" | stats count by account_name,Caller_Computer_Name | where sum(count) by SSO > 20

Thanks!

Tags (3)
0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

Try the following (not tested):

EventCode=4740 earliest=-24h 
| rex field=_raw "(?s)(?i)Account\sThat\sWas\sLocked\sOut.+?Account\sName:\s+(?\S*)" 
| stats count by account_name,Caller_Computer_Name 
| eventstats sum(count) as account_count by account_name
| where account_count > 20

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

When you say individual lockout count for a caller_computer_name should exceed your threshold, you already have count of generated in that way only. You can just add a "where count > 20" and that should do it.

0 Karma

rackersmt
Explorer

I need the threshold by account, not account and computer. Say the threshold is 20. If computer A locked out an account 17 times, I wouldn't want to alert on that. But if computer B also locked out the account 5 times, I would want to alert. javiergn actually posted an answer that's exactly what I was looking for.

0 Karma

javiergn
SplunkTrust
SplunkTrust

Try the following (not tested):

EventCode=4740 earliest=-24h 
| rex field=_raw "(?s)(?i)Account\sThat\sWas\sLocked\sOut.+?Account\sName:\s+(?\S*)" 
| stats count by account_name,Caller_Computer_Name 
| eventstats sum(count) as account_count by account_name
| where account_count > 20

rackersmt
Explorer

This is perfect, thanks!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...