topic Re: match field values with multivalue field in Splunk Search

How to match field values with multivalue field?

Skysurfer — Fri, 15 Jul 2022 16:54:34 GMT

I have a data with two fields: User and Account

Account is a field with multiple values. I am looking for a search that shows all the results where User is NOT matching any of the values in Account. From the below mentioned sample data, the search should only give "Sample 1" as output

Sample 1

User	Account
p12345	redfox
	h12345
	home\redfox
	new@redfox.com

Sample 2

User	Account
L12345	redsox
	L12345
	sky\newid
	sam@redsox.com

I have tried makemv, but not getting desired output

Re: match field values with multivalue field

ITWhisperer — Fri, 15 Jul 2022 10:35:53 GMT

You could try something like this

| where isnull(mvfind(account, user))

user will be treated as regex so it may depend on what characters you have in this field

Re: match field values with multivalue field

gcusello — Fri, 15 Jul 2022 10:36:37 GMT

Hi @Skysurfer,

you have to use the mvexpand command (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Mvexpand), something like this:

Ciao.

Giuseppe

Re: match field values with multivalue field

Skysurfer — Fri, 15 Jul 2022 11:01:44 GMT

Hi @gcusello

Thank you for your response.

I tried using mvexpand, but using User!=Account gives 4 matches for "Sample 1" and 3 matches for "Sample 2". What I am looking for is only one output where there will be no corresponding value in account field. In the above two samples "| table User" should only give output as

User

P12345

Re: match field values with multivalue field

gcusello — Fri, 15 Jul 2022 11:34:31 GMT

Hi @Skysurfer,

are the 4 matches for Sample1 the same or different?

if the same, you have to use dedup as in my answer.

Ciao.

Giuseppe

Re: match field values with multivalue field

Skysurfer — Fri, 15 Jul 2022 16:15:39 GMT

Hi @gcusello

Here "User!=Account | table User Account" would give something like this. As you can see all values are different. so dedup will not help.

User	Account
p12345	redfox
p12345	h12345
p12345	home\redfox
p12345	new@redfox.com

I am looking for something where there is no value in Account field for the User.

Ciao

Re: match field values with multivalue field

gcusello — Sat, 16 Jul 2022 14:01:21 GMT

Hi @Skysurfer,

please try this:

Ciao.

Giuseppe

Re: How to match field values with multivalue field?

PickleRick — Sat, 16 Jul 2022 16:41:16 GMT

It's easier than you think.

Comparisons for multivalued fields are performed on a per-value basis. So you can do something like this (run-anywhere example):

| makeresults 
| eval f1="e",f2=split("a,b,c,d",",") 
| append 
   [| makeresults 
    | eval f1="e",f2=split("e,f,g,h",",") ]
| where NOT f1=f2

Re: match field values with multivalue field

marysan — Sun, 17 Jul 2022 10:59:14 GMT

|mvexpand Account
|eval flag=if(match(Account,user),1,0)
|search flag=0

Re: How to match field values with multivalue field?

marysan — Sun, 17 Jul 2022 11:00:02 GMT

|mvexpand Account
|eval flag=if(match(Account,user),1,0)
|search flag=0

Re: match field values with multivalue field

Skysurfer — Tue, 19 Jul 2022 16:13:45 GMT

Thanks a lot @gcusello . It worked 😁