Compare 2 Multivalue Fields

[pkohn117]

I am looking to run a search and filter out whitelisted exceptions in a lookup file.  2 of the fields could contain multiple values though.

Here's the search I'm using:

index=microsoft365 sourcetype IN (azure:aad:signin, o365:management:activity) (action=success OR status=success) NOT Operation=UserLoginFailed 
| eval user_id=lower(user_id)
| dedup src user_id date_month
| iplocation src
| search NOT Country IN ("United States", "Canada")
| lookup local=t asn ip AS src
| lookup nonUSlogins.csv ca.user_id AS user_id OUTPUT a.country a.ticket a.user_id
| table user_id src date_month Country Region City asn autonomous_system a.user_id a.country

I tried using a match but found you can't use match if there are multiple values in a single field.

Here is an example result currently:

user_id	src	date_month	Country	Region	City	asn	autonomous_system	a.user_id	a.country
user1	1.1.1.1	june	Albania	Tirana District	Tirana			user1 user1	Albania Canada
user1	2.2.2.2	june	Germany	Land Berlin	Berlin			user1 user1	Albania Canada

I'm trying to eliminate results where the value for user_id matches a.user_id (values in this filed will be the same when there are multiple) AND the value of Country matches one of the countries listed in a.country

I would expect to see this in the end:

user_id	src	date_month	Country	Region	City	asn	autonomous_system	a.user_id	a.country
user1	2.2.2.2	june	Germany	Land Berlin	Berlin			user1 user1	Albania Canada

[kamlesh_vaghela · Accepted solution]

@pkohn117 

Try by adding below condition.

| where 'a.user_id'=user_id AND 'a.country'=Country

My Sample Search :

| makeresults 
| eval _raw ="user_id,src,date_month,Country,Region,City,asn,autonomous_system,a.user_id,a.country
user1,1.1.1.1,june,Albania,Tirana District,Tirana,,,user1|user1,Albania|Canada
user1,2.2.2.2,june,Germany,Land Berlin,Berlin,,,user1|user1,Albania|Canada" 
| multikv forceheader=1 | eval a_user_id=split(a_user_id,"|"),a_country=split(a_country,"|")
| rename a_user_id as a.user_id,a_country as a.country
| table user_id,src,date_month,Country,Region,City,asn,autonomous_system,a.user_id,a.country
| where 'a.user_id'=user_id AND 'a.country'=Country

 Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[pkohn117]

Ignore my last post.  I am getting the results but it's backwards - what is showing is actually what I want to filter out. 

I tried a couple things but they didn't work:

| where 'a.user_id'!=user_id AND 'a.country'!=Country

| where NOT ('a.user_id'=user_id AND 'a.country'!=Country)

[kamlesh_vaghela]

@pkohn117 

Try this

| where NOT ('a.user_id'=user_id AND 'a.country'=Country)

[pkohn117]

Ah, I can't believe I missed that.  Thank you for your help!!

[pkohn117]

Thank you for your reply.  Adding that line made the search produce 0 results.