Splunk Search

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

byeb1264
Explorer

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

byeb1264_0-1594058454898.png

 

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"

Labels (3)
Tags (3)
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...