Comparing Multivalue Fields

[wilcomply13]

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_ip	src_ip	KnownIP
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_ip	src_ip	KnownIP
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.

[Jason]

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

[colbym]

Exactly what I needed and all I had to do was substitute my field names.  Worked like a charm.  Karma for you.  Thanks !

[theChain]

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

[theChain]

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

[richgalloway · Accepted solution]

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

[wilcomply13]

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...

[richgalloway]

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.

[wilcomply13]

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

[ITWhisperer]

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