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!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...