Splunk Search

Comparing Multivalue Fields

wilcomply13
Explorer

I've done a fair amount of searching over the forums and am still having issues with comparing multi-value fields. I'm attempting to compare src_ip for events against MV field user_known_ip.

Below are the results I expect:

src_user_ipsrc_ipKnownIP

192.168.1.1

192.168.1.2

192.168.1.1

 

Yes

192.168.1.3

172.16.1.3

No

192.168.1.4

192.168.1.5

192.168.1.6

172.16.1.4

 

 

No

 

My current logic pulls in the necessary events, and does a lookup for user_known_ip:

 

index=myindex action=user_login 
| lookup known_user.csv user AS src_user OUTPUT user_ip as src_user_ip  
| makemv delim=" " src_user_ip
| mvexpand src_user_ip
| eval KnownIP = if(match(src_ip, src_user_ip),"Yes", "No")  
| search KnownIP="No" 
| stats values(src_user_ip) values(src_ip) values(KnownIP) by sAMAccountName

 

 

Despite this logic, I'm still having results returned from the base search that contain src_ip values that match values in the MV field src_user_ip:

src_user_ipsrc_ipKnownIP

192.168.1.1

192.168.1.2

192.168.1.1

172.16.1.1

172.16.1.2

No

192.168.1.3

172.16.1.3

No

192.168.1.4

192.168.1.5

192.168.1.6

172.16.1.4

172.16.1.5

192.168.1.6

 

No


src_user_ip is multi value and will have an indeterminate number of values.

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The match function doesn't work with multivalue fields.  Try using mvfind, instead.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

Jason
Motivator

I had to deal with this today - more in the context of "what was added or dropped between multivalue (MV) field A and MV field B", but the solution also lets you find the intersection between two MV fields.

This approach avoids the expensive mvexpand command.

The trick is to use mvmap() to do an operation on each value of one of the values in the MV field, and test to see if that value is in the other MV field. Technically, mvfind() takes a regex, so be careful with MV field values with regex-special characters in them.

Here's an example:

| makeresults | eval A="a, b, c", B="z, c, e, d" | makemv delim=", " A | makemv delim=", " B
| eval
 ``` loop through each value of B. if the value is not found in A, add the value to a resulting mv field returned. ```
added=mvmap(B, if(isnull(mvfind(A, B)), B, null())),
 ``` loop through each value of A. if the value is not found in B, add the value to a resulting mv field returned. ```
removed=mvmap(A, if(isnull(mvfind(B, A)), A, null())),
 ``` loop through each value of A. if the value IS found in B, add the value to a resulting mv field returned. ```
same=mvmap(A, if(isnotnull(mvfind(B, A)), A, null))

 

0 Karma

theChain
Explorer

Note my response and included command would do this and more with 5/6 less commands in an inexpensive manner.

0 Karma

theChain
Explorer

I would recommend a more direct approach using something meant to solve the issue without extra SPL such as parsing, expanding, etc.

 

Additionally, while the given accepted answer provides a solution to your particular use case in that you just need a yes/no to the existence of overlapping values. A command such as this one could also provide you with what those values actually are.

 

MVCompare | Splunkbase

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The match function doesn't work with multivalue fields.  Try using mvfind, instead.

---
If this reply helps you, Karma would be appreciated.
0 Karma

wilcomply13
Explorer

Appears that mvfind will only match against a regular expression and not a provided field:

"This function tries to find a value in the multivalue field MVFIELD that matches the regular expres...

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The match function has a similar description and yet you still tried it with a field.  Did you try mvfind with a field?  Note, in both functions the value of the provided field will be treated as a regular expression.  This means certain special characters may need to be escaped.

---
If this reply helps you, Karma would be appreciated.

wilcomply13
Explorer

My apologies, you are correct. I was misinterpreting the output. This is a working solution for this issue.

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It looks like src_user_ip is a space delimited set of ip so convert it to a regex with alternates and escaping the dots.

 

| makeresults
| eval _raw="192.168.1.1
172.16.1.3
172.16.1.4"
| rex max_match=0 "(?<src_ip>.+)"
| fields - _*
| mvexpand src_ip
| fields src_ip
| join type=left src_ip 
    [| makeresults
    | eval _raw="src_ip,src_user_ip
192.168.1.1,192.168.1.1 192.168.1.2
192.168.1.3,192.168.1.3
192.168.1.4,192.168.1.4 192.168.1.5 192.168.1.6"
    | multikv forceheader=1
    | fields - _* linecount ]
| eval src_user_ip=replace(replace(src_user_ip,"\.","\\.")," ","|")
| eval knownip=if(match(src_ip,src_user_ip),"yes","no")

 

Then use this in the match against src_ip

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...