Attempting to exclude based on UserId/City/Country from inputlookup csv file but City/Country are not matching because iplocation is run after. How can I fix this?
index="o365data" eventtype="user_logins" NOT [| inputlookup exemptusers |fields UserId,City,Country | format]
| spath input=AuditData
| eval User = coalesce(UserId, UserIds)
| search Workload=AzureActiveDirectory User!=Unknown User!="*@emailaddress.org"
| rename ExtendedProperties{}.* as *
| eval ExtendedProperties=mvzip(Name,Value, "=")
| mvexpand ExtendedProperties
| eval Key=mvindex(split(ExtendedProperties,"="),0), Value=mvindex(split(ExtendedProperties,"="),1)
| rename ExtendedProperties{}.* as *
| eval ExtendedProperties=mvzip(Name,Value, "=")
| mvexpand ExtendedProperties
| eval Key=mvindex(split(ExtendedProperties,"="),0), Value=mvindex(split(ExtendedProperties,"="),1)
| iplocation ClientIP
| eval checkfail1=if(like(LogonError, "FaultDomainRedirect"), "true", "false")
| eval checkfail2=if(like(LogonError, "UserAccountNoFound"), "true", "false")
| where Country != "Canada" AND checkfail1 != "true" AND checkfail2 != "true"
| stats values(ClientIP) as "ClientIP", values(City) as "City", values(Country) as "Country", latest(_time) as "Latest Timestamp" by User
| convert ctime("Latest Timestamp")
hi @joeybroesky,
You can remove the exclude sub search in the base search and search it after iplocation command:
| iplocation ClientIP
| search NOT [| inputlookup exemptusers | fields UserId,City,Country | format]
hi @joeybroesky,
You can remove the exclude sub search in the base search and search it after iplocation command:
| iplocation ClientIP
| search NOT [| inputlookup exemptusers | fields UserId,City,Country | format]
Thanks for your help manjunathmeti!