I am trying to remove certain logs from a base query of a certain type based on the results of another query of a different type of log. Both are connected by the user field.
Specifically, I have identified instances where a user has 4 or more failed login attempts, and am trying to remove instances where they successfully changed their password after. This leaves a list of users, and their associated logs, who have a large number of failed logins but did not update their password.
Here is the base query:
index=1234 logger_name=auth message="user failed to login" earliest=-24h latest=now
| stats count by user
| search count>=4
| join user [search index=1234 logger_name=auth message="user failed to login*" earliest=-24h latest=now]
Here is the query I am essentially trying to include. However, SPL only handles left, right, and inner joins
| MINUS user [search index=1234 logger_name=passwordchange message="Update Password:Success" earliest=-24h latest=now]
How might I accomplish this?
Thank you for your help.
Hi @ahendler1,
it's actually not that difficult.
Go for something like this:
index=1234 logger_name=auth message="user failed to login" earliest=-24h latest=now
| stats count by user
| search count>=4
| join user [search index=1234 logger_name=auth message="user failed to login*" earliest=-24h latest=now]
| search NOT [search index=1234 logger_name=passwordchange message="Update Password:Success" earliest=-24h latest=now | fields user | format]
Tell me if it works.
Hi @ahendler1,
it's actually not that difficult.
Go for something like this:
index=1234 logger_name=auth message="user failed to login" earliest=-24h latest=now
| stats count by user
| search count>=4
| join user [search index=1234 logger_name=auth message="user failed to login*" earliest=-24h latest=now]
| search NOT [search index=1234 logger_name=passwordchange message="Update Password:Success" earliest=-24h latest=now | fields user | format]
Tell me if it works.
@pyro_wood
Works great, thank you!
@ahenler1,
great!
Please accept the answer then. 🙂