topic Trying to create a search that will use the results to feed another search and output the results in a table. in Splunk Search

Trying to create a search that will use the results to feed another search and output the results in a table.

bworrellZP — Tue, 05 Jul 2016 15:14:42 GMT

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.
index=Account Status=Locked

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?

Thanks

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

woodcock — Tue, 05 Jul 2016 15:20:53 GMT

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.

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

sundareshr — Tue, 05 Jul 2016 15:36:36 GMT

Your second search should give you the desired output. You can save this search as an alert and if count>0 send an email

http://docs.splunk.com/Documentation/Splunk/6.4.1/Alert/Emailnotification

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

bworrellZP — Tue, 05 Jul 2016 16:30:11 GMT

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)

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

bworrellZP — Tue, 05 Jul 2016 16:34:31 GMT

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.

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

woodcock — Tue, 05 Jul 2016 16:42:07 GMT

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]

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

bworrellZP — Tue, 05 Jul 2016 17:53:41 GMT

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?

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

sundareshr — Tue, 05 Jul 2016 18:08:51 GMT

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

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

bworrellZP — Tue, 05 Jul 2016 18:29:04 GMT

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

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

woodcock — Tue, 05 Jul 2016 18:46:12 GMT

The lookup file should have _time UserID and 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.

Re: Trying to create a search that will use the results to feed another search and output the results in a table.

bworrellZP — Tue, 05 Jul 2016 19:20:05 GMT

I think I have it now, had to go find another example. I was running into a name error.

Thank you for the suggestion.