Splunk Search

How to check 10 days prior to an event in Splunk for a failed login attempt?

MM0071
Path Finder

I have a search in Splunk that returns events for failed logins. I want to be able to check for a successful authentication from a user and an IP 10 days prior to the failed login. Is this possible via a query?

index=logins
| where AuthenticationResults="failed"
| sort 0 - _time
| eval successtime = if(AuthenticationResult=="success", _time, null())

Labels (4)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

OK. So there are some things that can be fixed in the first place.

1. Don't do

index=logins
| where AuthenticationResults="failed"

just do

index=logins AuthenticationResults="failed"

With one very small caveat that there are some situations when the field is parsed out from a longer string so that it's not broken as indexed term. But generally it's much better to do it like that.

But in your case since you want all types of results, that condition is not needed anyway.

2. Reverse sorting by _time is completely unneeded since Splunk by default returns data this way.

3. OK. So you want to find all logins, regardless of their state

index=logins (and any further conditions that can narrow your results to
just logins in case you have other data in that index)

Then you want to find the times of failed logins. First create an additional field which will exist only for those failed logins

| eval failedlogintime=if(AuthenticationResult=="failed",_time,null())

Then for each event find when was the latest failed login

| streamstats latest(failedlogintime) as failedlogintime by user IP

Now you can only filter out those which are longer than 10 days before failed login

| where failedlogintime-_time<=864000

 

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

OK. So there are some things that can be fixed in the first place.

1. Don't do

index=logins
| where AuthenticationResults="failed"

just do

index=logins AuthenticationResults="failed"

With one very small caveat that there are some situations when the field is parsed out from a longer string so that it's not broken as indexed term. But generally it's much better to do it like that.

But in your case since you want all types of results, that condition is not needed anyway.

2. Reverse sorting by _time is completely unneeded since Splunk by default returns data this way.

3. OK. So you want to find all logins, regardless of their state

index=logins (and any further conditions that can narrow your results to
just logins in case you have other data in that index)

Then you want to find the times of failed logins. First create an additional field which will exist only for those failed logins

| eval failedlogintime=if(AuthenticationResult=="failed",_time,null())

Then for each event find when was the latest failed login

| streamstats latest(failedlogintime) as failedlogintime by user IP

Now you can only filter out those which are longer than 10 days before failed login

| where failedlogintime-_time<=864000

 

Get Updates on the Splunk Community!

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...