topic Re: Compare 2 Multivalue Fields in Splunk Search

Compare 2 Multivalue Fields

pkohn117 — Fri, 16 Jul 2021 13:25:10 GMT

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

Re: Compare 2 Multivalue Fields

kamlesh_vaghela — Fri, 16 Jul 2021 14:51:18 GMT

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

Re: Compare 2 Multivalue Fields

pkohn117 — Fri, 16 Jul 2021 17:44:59 GMT

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

Re: Compare 2 Multivalue Fields

pkohn117 — Fri, 16 Jul 2021 17:55:47 GMT

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)

Re: Compare 2 Multivalue Fields

kamlesh_vaghela — Sat, 17 Jul 2021 04:58:08 GMT

@pkohn117

Try this

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

Re: Compare 2 Multivalue Fields

pkohn117 — Mon, 19 Jul 2021 11:48:42 GMT

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