So I have a search that tells me is someones account is locked. I have been asked to create an alert or search that will tell me when someone's account is locked and has tried to login again. Then output this as a table in an email.
Search I use to find the locked accounts.
Once I find the users that are locked, I want to feed those users into the second search, so that if they attempt tp login again, after being locked, I get a report with a table of the details I have been asked to provide.
Search I use to create the table, when doing a manual search
index=Account Status=Locked | table LoginTime, LoginStatus, FailureReason, Status, UserID
How do I take the first search to feed the second with just locked accounts that are trying to login?
It appears that you are already doing so. Your question does not make sense; you should edit it and make it much more clear. It usually helps a great deal if you show a few sample events and a mockup of your desired final output.
Sorry about that, left out a key piece there. Updated the post.
Short version, I need to track login attempts to locked accounts in an app we have. Accounts lock after X failed attempts for Y time frame.
The output requirements are managed by another department.
Need to get the locked accounts from the logs, then feed that user info to the other search in the event they log in again.
Thought about that, only thing I could not figure out, is how to make the alert per user. If I have 5 people lock out, and none of them try to get in again, that's fine. But if 5 lock out and only 1 tries to get in again, do not want that 1 to show up with the other 4. (if that makes sense)
See if this works (use the appropriate status for successful login)
index=Account Status=Locked OR Status=Login | stats latest(_time) as time by Status | where isnotnull(Locked) AND Login<Locked
So the Locked status field is called Status. The Login status is called Loginstatus.
With that said, would this be the correct search?
index=Account Status=Locked OR LoginStatus=Login | stats latest(_time) as time by Status | where isnotnull(Locked) AND Login
Have 2 searches: 1 scheduled that updates a lookup file (or KV Store) for all UserIDs with "locked out" status.
Then use a
subsearch to pull in that data to limit an outer search for login attempts like this:
Your search that shows failed login attempts here [|inputlookup YourLockedOutLookupHere | table UserID]
Okay, so I can create a report to run every hour, and update the CSV with the User names that have failed to login. That part I understand.
Still a little foggy on how to use that lookup table for my search to pull usernames for.
Using this search, index=Account Status=Locked | table LoginTime, LoginStatus, FailureReason, Status, UserID, how to I feed the look up into it?
The lookup file should have
Status (you can get by without
Status but it will help make things clear to keep it at first). You would do something like this to maintain it (scheduled every hour or whatever you need for granularity on your alert):
index=Account (Status="Locked" OR Status="Unlocked") | appendpipe [|inputlookup YourLockedOutLookupHere ] | sort 0 - _time | dedup UserID | search Status="Locked" | table _time Status UserID | outputlookup YourLockedOutLookupHere
Now you have an up-to-date list of everyone whose last status is "Locked" and you can use this lookup as described in the original answer.