Filter on a subsearch

[acwardjr]

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?

[lguinn2 · Accepted solution]

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.