Hi All,
I am currently attempting to write a Splunk search that will count the amount of failed authentications for a multiple user accounts, grouped by Source IP. I have provided my current (basic) search string below and have attached an image of sanitised search results, detailing current output vs the output I would like to achieve.
I have attempted various different combinations of stats count() and stats values(), but just cannot seem to get it right.
Any assistance would be greatly appreciated.
index=Windows sourcetype=WinEventLog:Security EventCode=4625
| eval Account_Name=mvindex(Account_Name, 1)
| stats count(EventCode) as Authentication_Failures by Account_Name, Source_Network_Address
Just add this to the bottom of your existing search:
| stats values(*) AS * BY Account_Name
OR
| stats sum(Authentication_Failures) AS Authentication_Failures list(Source_Network_Address) AS Source_Network_Address BY Account_Name
Just add this to the bottom of your existing search:
| stats values(*) AS * BY Account_Name
OR
| stats sum(Authentication_Failures) AS Authentication_Failures list(Source_Network_Address) AS Source_Network_Address BY Account_Name
Much appreciated @woodcock! Thank you for taking the time to help me out 🙂
@MikeElliott try something like this
index=Windows sourcetype=WinEventLog:Security EventCode=4625
| eval Account_Name=mvindex(Account_Name, 1)
| stats list(EventCode) as Authentication_Failures list(Source_Network_Address) as Source_Network_Address by Account_Name
I was using a csv with the generated data so you can sum Authentication_Failures if your totaling the actual times failed login also
index=Windows sourcetype=WinEventLog:Security EventCode=4625
| eval Account_Name=mvindex(Account_Name, 1)
| stats sum(EventCode) as Authentication_Failures list(Source_Network_Address) as Source_Network_Address by Account_Name
A superb solution, my thanks @Grumpalot.
I have made a few modifications to the search string - Mainly just to adjust the end output. I was wondering if I could be so bold as to ask for help with one last thing?
How would I modify the below search string to only provide results where Source_Network_Address field has >= 2 Values? I have attempted to use a where statement, as well as a separate count() | where statement and can't seem to get it to work.
index=Windows sourcetype=WinEventLog:Security EventCode=4625 earliest=-25h@h latest=-1h@h
| eval Account_Name=mvindex(Account_Name, 1)
| search Source_Network_Address!="-"
| stats count(EventCode) as Authentication_Failures values(Source_Network_Address) as Source_Network_Address by Account_Name
| where Authentication_Failures > 10
| sort -Authentication_Failures
Go ahead and change your where to search
index=Windows sourcetype=WinEventLog:Security EventCode=4625 earliest=-25h@h latest=-1h@h
| eval Account_Name=mvindex(Account_Name, 1)
| search Source_Network_Address!="-"
| stats count(EventCode) as Authentication_Failures values(Source_Network_Address) as Source_Network_Address by Account_Name
| search Authentication_Failures > 10
| sort -Authentication_Failures