How to return results in sub-search only if it does not match the results in the main search? o365 AD logs

[SausagePizzza]

Hello,

Using the o365:management:activity logs, I'm trying to create a search where I:

1. Get a list of users and their IP addresses, who failed MFA
2. Get a list of successful login IP addresses we seen before for the users identified in Step 1 and compare the results.
3. Return the list of users and their IP addresses which were not in Step 2. The idea is to return the IP addresses that failed MFA which never had a successful log in before. 

What's the best way to develop this search?

I initially thought about doing a search where the sub-search would be the following (return a list of users and their IPs who failed MFA):

index=o365 sourcetype=o365:management:activity Workload=AzureActiveDirectory Operation=UserLoginFailed (LogonError=UserStrongAuthClientAuthNRequiredInterrupt OR LogonError=DeviceAuthenticationRequired OR LogonError=DeviceAuthenticationFailed) earliest=-2h@h latest=now 
| stats count by UserId ClientIP

and then the main search would grab the successful login IP addresses for the users and compare the results. If the IP address that failed MFA was not in the list of successful login IPs, return that IP and user. 

I wasn't able to get that to work. Is this possible? Is this the best approach?

This appeared to work but the performance is painful:

index=o365 sourcetype=o365:management:activity Workload=AzureActiveDirectory Operation=UserLoginFailed (LogonError=UserStrongAuthClientAuthNRequiredInterrupt OR LogonError=DeviceAuthenticationRequired OR LogonError=DeviceAuthenticationFailed) earliest=-1h@h latest=now NOT 
    [ search index=o365 sourcetype=o365:management:activity Workload=AzureActiveDirectory Operation=UserLoggedIn earliest=-7d latest=-4h@h 
    | dedup ClientIP UserId 
    | table ClientIP UserId] 
| stats count by ClientIP UserId Operation

Thanks in advance.

[bowesmana]

@SausagePizzza 

Subsearches can cause the overall search to be slow, in this case, you're searching 7 days of successful login data to just get a list of IPs to act as a constraint to the failed login search.

Typically, the way to address these problems is to search for both data sets in a single search, then aggregate and check counts, like this.

index=o365 sourcetype=o365:management:activity Workload=AzureActiveDirectory
  (Operation=UserLoginFailed 
    (LogonError=UserStrongAuthClientAuthNRequiredInterrupt OR
     LogonError=DeviceAuthenticationRequired OR
     LogonError=DeviceAuthenticationFailed)
    earliest=-1h@h latest=now)
  OR
  (Operation=UserLoggedIn earliest=-7d latest=-4h@h)
| stats values(Operation) as Operations count by ClientIP UserId
| where mvcount(Operations)=1 AND Operations="UserLoginFailed"

So, the search will get 

a) all failed logins from the start of the last hour to now

b) all successful logins from the last 7 days to -4 hours ago

then, the stats command just collects all Operation types seen (either failed, success or both) for each clientIP, userId

The where test is then filtering by saying

• Must have seen just a single operation for this user/ip and 
• It must be a login failure

An alternative to avoid having to do the 7 day search each time this runs is to run that search as a saved search and collect the results to a lookup and then use that in the lookup. That will depend on when and how this search is designed to run.

I hope this helps