Counting & Grouping Field Values

[MikeElliott]

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

[woodcock · Accepted solution]

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

[MikeElliott]

Much appreciated @woodcock! Thank you for taking the time to help me out 🙂

[Grumpalot]

@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

[Grumpalot]

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

[MikeElliott]

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

[Grumpalot]

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