Alerting

Search inputlookup city & country exclusion before iplocation problem

joeybroesky
Path Finder

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")

Labels (2)
0 Karma
1 Solution

manjunathmeti
Champion

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]

 

View solution in original post

manjunathmeti
Champion

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]

 

View solution in original post

joeybroesky
Path Finder

Thanks for your help manjunathmeti!

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!