topic Re: Comparing Multivalue Fields in Splunk Search

Comparing Multivalue Fields

wilcomply13 — Mon, 10 May 2021 18:53:39 GMT

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.

Re: Comparing Multivalue Fields

ITWhisperer — Tue, 11 May 2021 12:52:32 GMT

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.

Then use this in the match against src_ip

Re: Comparing Multivalue Fields

richgalloway — Mon, 10 May 2021 19:27:01 GMT

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

Re: Comparing Multivalue Fields

wilcomply13 — Tue, 11 May 2021 12:25:41 GMT

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 expression in 'REGEX'"

Re: Comparing Multivalue Fields

richgalloway — Tue, 11 May 2021 12:38:59 GMT

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.

Re: Comparing Multivalue Fields

wilcomply13 — Tue, 11 May 2021 13:53:07 GMT

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

Re: Comparing Multivalue Fields

theChain — Mon, 23 Aug 2021 02:23:00 GMT

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

Re: Comparing Multivalue Fields

Jason — Tue, 31 May 2022 15:59:37 GMT

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

Re: Comparing Multivalue Fields

theChain — Tue, 31 May 2022 16:33:15 GMT

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

Re: Comparing Multivalue Fields

colbym — Mon, 16 Sep 2024 19:35:53 GMT

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