How to write a search to compare results from two tables, and show the results that only exist in the first table?

[jpringle03]

Background: I'm trying to create a search that will let me know if something about a user is true within the last 7 days. I am then trying to run a search to see if that username has existed ever within the time before that first search. If they have, I don't want to display a count for them.

I have two different searches that I want to run:

First Search:

index=security extracted_eventtype=authentication something=true earliest=-7d | dedup username | table username

Second Search:

index=security extracted_eventtype=authentication latest=-8d earliest=-90d | dedup username | table username

Is there a way to compare these two searches and return the results of things that don't match, so basically only things that exist in the first search?

Example:

First search returns: Bob, Jim, Steve

Second Search returns: Bob, Fred, Mark, Todd

Ideally I would want a query that spits out: Jim and Steve because they are only unique things from the first search.

[somesoni2 · Accepted solution]

Give this a try. Will show user names who have only appeared in last 7 days and never before in last 90 days (from 8-90 days period)

index=security extracted_eventtype=authentication something=true earliest=-90d | eval Period=if(_time>=relative_time(now(),"-7d"),1,2)  | stats max(Period) as periods by username | where periods=1

[jpringle03]

That did the trick! Thanks so much! 🙂