I am looking for a query based on my below scenario use case :
user passwords shall comply with minimum complexity requirements. These requirements only partially protect from the risk that weak passwords are used and exploited. Several databases of commonly used password exist and contain passwords that are compliant with DB password complexity policy. An attacker may test a large number of user accounts to find a user that has adopted a common password. This attack is known under the name of password spraying. Due to the large number of users and passwords to be tested, the attack must be automated with scripts that repeatedly test one password on multiple accounts. This monitoring use case should be implemented to enhance protection against weak passwords.
The detection rule shall be based on the following filter criteria:
•Select events with Log Event ID 4771 - Kerberos pre-authentication failed (all these events refer to a failed log in attempt)
•Remove duplicates: Filter out all events where the client address belongs to one of the DB Domain Controller servers (list of DC servers required!!)
•Count the number of failed logins per day per client address
•Count the number of subject account names per client address.
•Raise an alert for when a given client addresses both counts exceeding the defined thresholds X and Y
Sensible thresholds should be defined while testing the use case, an initial suggestion is:
X = 100 -> More than 100 failed log in per day
Y = 80 -> Failed logins correspond to a minimum of 80 different subject account name
index=win* EventCode="4771" AND NOT [|inputlookup DomainControllers.csv | table host] | dedup Your Duplication Fields Listed Here | bin _time span=1d | stats values(user) dc(user) AS num_users count span=1d BY dest _time | search count>X AND num_users>Y
You can download the app from Splunkbase. It has 400+ Use Cases with prebuilt SPL queries & data onboarding guides and lots of other information around how to implement these Use Cases. A must have app in your repository.