Validate success versus failed logins

[bworrellZP]

I was asked if we can run a report / create an alert to act on the following:

Accounts that have had failed logins, but never a successful login, within a defined time window. The goal is to determine is an account has ever been used successfully, and if it has not, then it can be sent to the access management team to review.

In my data, I have the default _time but also have a time field from the source of "LoginAttemptDateTime". For login status, I have a field called "LoginStatus", and of course user field of "User".

Looking at some of the Brute force posts offered ideas, but nothing that gave me exactly what I was asked for. Hoping someone has another idea.

Thanks in advance

[damien_chillet]

I would run a distinct count on LoginStatus per user and retrieve the LoginStatus values then keep the one with distinct count equal to one (only Successful or Failed logins) and values set to "Failed":

index=yourindex sourcetype=yoursourcetype 
| stats dc(LoginStatus) as dc,  values(LoginStatus) as LoginStatus by User
| search dc=1 AND LoginStatus="Failed"

[bworrellZP]

Okay, this is good, they (management) liked it. They had two questions. Can we add the amount of times the login was tried, and can we also add the ones that did login successfully, and show the failed counts for those.

I am going to try and mess with this some, as the distinct count option was not one I had thought of.

[HeinzWaescher]

I would try something like:

 index=yourindex sourcetype=yoursourcetype 
| stats count(eval(LoginStatus="succeeded")) as succeeded_logins,  count(eval(LoginStatus="failed")) as failed_logins, count(LoginAttemptDateTime) AS total_attempts BY User

With this you can set filters like:

1. | search succeeded_logins=0 AND failed_logins>0

2.
| search succeded_logins>0
| stats count avg(total_attempts) AS avg_attempts

[bworrellZP]

Excellent, your suggestion was able to get me where I needed you go. Thank you