topic Re: Counting & Grouping Field Values in Splunk Search

Counting & Grouping Field Values

MikeElliott — Fri, 21 Jul 2017 08:49:04 GMT

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

Re: Counting & Grouping Field Values

Grumpalot — Fri, 21 Jul 2017 12:34:10 GMT

@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

Re: Counting & Grouping Field Values

Grumpalot — Fri, 21 Jul 2017 12:39:46 GMT

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

Re: Counting & Grouping Field Values

MikeElliott — Tue, 29 Sep 2020 14:59:52 GMT

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

Re: Counting & Grouping Field Values

Grumpalot — Fri, 21 Jul 2017 14:35:35 GMT

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

Re: Counting & Grouping Field Values

woodcock — Sun, 23 Jul 2017 02:07:58 GMT

Just add this to the bottom of your existing search:

| stats values(*) AS * BY Account_Name

| stats sum(Authentication_Failures) AS Authentication_Failures list(Source_Network_Address) AS Source_Network_Address BY Account_Name

Re: Counting & Grouping Field Values

MikeElliott — Mon, 24 Jul 2017 07:11:25 GMT

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