How to get SPL to exclude results that do not contain a string in multiple fields?

[byeb1264]

I am trying to tune an alert but need to only exclude if 2 of three fields do not contain a string.  My goal is too tune out improbable access alerts where certain users log in from two locations within the united stats.  

The search results are below

The SPL without the exclusion is below

`m365_default_index` sourcetype="o365:management:activity" Operation=UserLoggedIn
| rename ClientIP AS src_ip
| sort 0 UserId, _time
| streamstats window=1 current=f values(_time) as last_time values(src_ip) as last_src_ip by UserId
| iplocation last_src_ip
| rename Region as State
| eval last_location = if(isnotnull(City) AND City!="", City . ", ", "") . if(isnotnull(State) AND State!="", State . ", ", "") . if(isnotnull(Country) AND Country!="", Country . ", ", "")
| rename lat as last_lat lon as last_lon Country as last_Country
| iplocation src_ip
| rename Region as State
| eval location = if(isnotnull(City) AND City!="", City . ", ", "") . if(isnotnull(State) AND State!="", State . ", ", "") . if(isnotnull(Country) AND Country!="", Country . ", ", "")
| foreach *location
[ | eval <<FIELD>> = replace(replace(<<FIELD>>, "^\s*,\s*", ""), "\s*,\s*$$", "")]
| eval rlat1 = pi()*last_lat/180, rlat2=pi()*lat/180, rlat = pi()*(lat-last_lat)/180, rlon= pi()*(lon-last_lon)/180
| eval a = sin(rlat/2) * sin(rlat/2) + cos(rlat1) * cos(rlat2) * sin(rlon/2) * sin(rlon/2) | eval c = 2 * atan2(sqrt(a), sqrt(1-a))
| eval distance = 6371 * c, time_difference_hours = round((_time - last_time) / 3600,2), speed=round(distance/ ( time_difference_hours),2) | fields - rlat* a c
| eval day=strftime(_time, "%m/%d/%Y")
| search last_Country!=Country distance!=0 speed>1000
| stats values(time_difference_hours) as time_difference_hours values(speed) as speed first(last_location) as location_one first(location) as location_two values(*src_ip) as *src_ip min(_time) as firstTime by UserId distance day
| eval firstTime=strftime(firstTime, "%m/%d/%Y %H:%M:%S")
| sort - distance
| search NOT UserId="Unknown"
| search distance>500 AND speed>500
| lookup Executives.csv Email as UserId OUTPUTNEW Title
| lookup CriticalUsers.csv Email as UserId OUTPUTNEW Title
| eval Severity=case(isnotnull(Title),"Critical",isnull(Title),"Medium")

I have tried just about every combination of search NOT, search fielda!=blah, etc.  Does anyone know how to do this?  An example of what I have tried is below

| search NOT UserId="someuser" AND NOT location_one="*United States" AND NOT location_two="*United States"

[bowesmana · Accepted solution]

Not sure if this will help, but using NOT searches with leading wildcards may be a problem. What I would do is rather than use search, use the stricter 'where' clause, e.g.

| where !(UserId="someuser" OR match(location_one,"United States") OR match(location_two,"United States"))

which is using regex in the match statement.

I always wonder if it's better to use search or where part way through the pipeline and now fall on the side of where, as it allows for a more precise definition through the use of the eval statements.

Hopefully this will help.

[byeb1264]

After a minor logic tweak, it works.  Thank you so much!

| where !(UserId="someuser" AND match(location_one,"United States") AND match(location_two,"United States"))