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
Super Champion

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

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
Super Champion

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
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...