Splunk Search

Statistical analysis of failed logins

maxywalker1
Explorer

I have been trying to look at statistical figures for failed login attempts over a 30 day period for each user by the hostname. I can get a table showing every failed attempt but want to condense that down to show a total count of failed attempts and an avg/day, my thinking being that it could be useful to identify attempts to do slow brute forcing from credential stuffing attacks.

This is what I have tried so far:

index=wineventlog EventCode=4625
| search signature="User name is correct but the password is wrong"
| eventstats count(TargetUserName) by hostname as Total_Count
| eventstats avg(Total_Count) as Avg_Count
| table TargetUserName, hostname, Total_Count, Avg_Count
| sort TargetUserName

but this ends up giving me the username and hostname but the total and avg fields are blank.

Any ideas on how to do this better?

 

Thanks,

Maxy

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

If you are searching a 30 day window then you would want something like this

index=wineventlog EventCode=4625 signature="User name is correct but the password is wrong"
| bin _time span=1d
| stats count by _time, TargetUserName hostname 
| eventstats avg(count) as Avg_Count sum(count) as Total_Count by _time
| table TargetUserName, hostname, Total_Count, Avg_Count
| sort TargetUserName

However it's not clear if your total is the total of all across the entire time range or the total per day or the total per user per day or total per  user/host per day.

But essentially the above is creating a daily bucket (bin command) and the getting the count by day/user/host and then getting average per day and total per day.

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

If you are searching a 30 day window then you would want something like this

index=wineventlog EventCode=4625 signature="User name is correct but the password is wrong"
| bin _time span=1d
| stats count by _time, TargetUserName hostname 
| eventstats avg(count) as Avg_Count sum(count) as Total_Count by _time
| table TargetUserName, hostname, Total_Count, Avg_Count
| sort TargetUserName

However it's not clear if your total is the total of all across the entire time range or the total per day or the total per user per day or total per  user/host per day.

But essentially the above is creating a daily bucket (bin command) and the getting the count by day/user/host and then getting average per day and total per day.

 

maxywalker1
Explorer

Thanks heaps, that worked perfectly, still need to get my head around some of these commands and how to put them all together.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

but what you might want to do is rather than search the 30 days every time you search, is to create a saved search that runs every night that just collects the daily totals to a summary index and then to run the search on the summary index, which would be much faster.

 

0 Karma

renjith_nair
Legend

@maxywalker1,

Are you looking for something similar to ?

index=wineventlog EventCode=4625
| search signature="User name is correct but the password is wrong"
| bucket span=1d _time
| stats count by TargetUserName,hostname,_time
| eventstats sum(count) as total,avg(count) as avg by _time
---
What goes around comes around. If it helps, hit it with Karma 🙂
Get Updates on the Splunk Community!

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...