Splunk Search

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

bworrellZP
Communicator

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

1 Solution

woodcock
Esteemed Legend

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]

View solution in original post

woodcock
Esteemed Legend

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]

bworrellZP
Communicator

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?

0 Karma

bworrellZP
Communicator

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

Thank you for the suggestion.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

sundareshr
Legend

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

0 Karma

bworrellZP
Communicator

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)

0 Karma

sundareshr
Legend

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
0 Karma

bworrellZP
Communicator

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

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

bworrellZP
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...