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 |
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.
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.
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)
Ah, I can't believe I missed that. Thank you for your help!!
Thank you for your reply. Adding that line made the search produce 0 results.