[search] Total Login Failures along with Failures per user

[neerajs_81]

Hi All,
Can someone help to build a search to check for Total_login_Failures  > 10 (per 24H) OR  Number of Failures per user > 5?  Both conditions need to be in same search and an alert will fire with either one is met.
My search so far 

( index = index1 "Failed password") earliest=-1d
| eventstats count as Per_User_failures by user
| stats latest(_time) as _time, values(host), values(dest_ip), values(src_ip), dc(src_ip) as srcIpCount, values(user), dc(user) as userCount, count as Total_failures by src_ip dest
| rename values(*) as *
| where Total_failures>=10 AND Per_user_Failures>5

[richgalloway]

It would help if we knew how the query fails to meet expectations.  As a start, I offer this revision that should better meet the stated requirements.

( index = index1 "Failed password") earliest=-24h
| eventstats count as Per_User_failures by user
| stats latest(_time) as _time, values(host), values(dest_ip), values(src_ip), dc(src_ip) as srcIpCount, list(user), dc(user) as userCount, list(Per_User_failures) as Per_User_failures count as Total_failures by src_ip dest
| rename values(*) as *
| where Total_failures>10 OR Per_user_Failures>5

If the "Failed password" string is in a specific field then that field should be specified to improve search performance.

[neerajs_81]

Thank you for responding. Yes "Failed Password" is a specific string in the log files.

The "Per_user_Failures" in the above search is not showing any value. I am assuming that is because this was used in EventStats command which further piped to stats command. So this field was lost.

I ran the search you suggested,  here is what i am getting in the output table in some cases . Note:  I am just copying the last 5 columns of my search results.  Notice the Total_failures is count of total failures inclusive of all users.    Is there any way to make it display the failures per user level  as well ? I want to see how many failures did John have, how many did Steve have etc.    Can we have an additional field that also keeps track of failuers per user level ? 

I have a hard condition that i need to group it by "src_ip" and "dest"

src_ip	srcIPCount	user	userCount	Total_failures
x.x.x.x	1	John S Steve S Ashley	3	12

[richgalloway · Accepted solution]

You're right.  The Per_User_failures field is stripped out by the stats command.  I revised my answer to include it.

One thing the query does handle ATM is the test where Per_User_failures > 5.  The current where command does not work with multi-value fields.  We can use mvfilter() to test Per_User_failures, but there is no link to the user with those failures so we won't know who is responsible.

[neerajs_81]

Thank you