Splunk Search

Counting & Grouping Field Values

MikeElliott
Communicator

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

Uploaded Image

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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

MikeElliott
Communicator

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

0 Karma

Grumpalot
Communicator

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

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

MikeElliott
Communicator

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

Grumpalot
Communicator

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
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...