Splunk Search

Filter on a subsearch

acwardjr
Engager

Hello all,

I am trying to compare logins between two systems in our environment where a user failed login to one, but successfully logged into another.

index=login result=allow server_region=us [search failed_password us_login | rename us_accountid as accountid | table accountid] | stats count, values(accountid) as Accounts by ip | where count>2

First, in my inner query I looked for all failed logins via password in the US region failed_password us_login and then rename us_accountid to accountid, since once system calls them us_accountid, and the other just calls them accountid. I then pass those results to the outer query.

I currently have the stats and where clause on the outer query, but I would like them on the inner query so I can't find anyone who fails 3 or more times on a password and THEN gets a success on the other system (And not just anyone who fails, but makes 3 or more logins). However you can't "stats" on an inner query as the results cannot be tabled out and passed to the outer query.

Thoughts?

Tags (1)
0 Karma
1 Solution

lguinn2
Legend

Actually, you can do anything you want in a subsearch, as long as you understand what you are passing back to the outer search:

index=login result=allow server_region=us 
    [ search failed_password us_login | stats count by us_accountid 
    | where count > 2 | rename us_accountid as accountid
    | fields accountid ]
| stats count as SuccessfulLogins, values(accountid) as Accounts by ip

You should realize that in both the original search and the search that I have presented here, the count represents successful logins, not failed logins.

View solution in original post

lguinn2
Legend

Actually, you can do anything you want in a subsearch, as long as you understand what you are passing back to the outer search:

index=login result=allow server_region=us 
    [ search failed_password us_login | stats count by us_accountid 
    | where count > 2 | rename us_accountid as accountid
    | fields accountid ]
| stats count as SuccessfulLogins, values(accountid) as Accounts by ip

You should realize that in both the original search and the search that I have presented here, the count represents successful logins, not failed logins.

Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...