Splunk Search

Conditional Sum

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

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

Revered Legend

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

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

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

Explorer

This is perfect, thanks!

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!