Splunk Search

How to match field values with multivalue field?

Skysurfer
Explorer

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

Labels (1)
Tags (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @Skysurfer,

please try this:

index=your_index
| fields User Account
| mvexpand Account
| eval status=if(User!=Account,"OK","Present")
| stats dc(status) AS dc_status values(status) AS status BY User
| search dc_status=1 abs status="OK"
| table User

Ciao.

Giuseppe

View solution in original post

marysan
Communicator

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

PickleRick
SplunkTrust
SplunkTrust

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

 

gcusello
SplunkTrust
SplunkTrust

Hi @Skysurfer,

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

index=your_index
| fields User Account
| mvexpand Account
| where User!=Account
| dedup User
| table User

Ciao.

Giuseppe

0 Karma

Skysurfer
Explorer

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
0 Karma

Skysurfer
Explorer

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.

UserAccount
p12345redfox
p12345h12345
p12345home\redfox
p12345new@redfox.com

 

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

Ciao

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Skysurfer,

please try this:

index=your_index
| fields User Account
| mvexpand Account
| eval status=if(User!=Account,"OK","Present")
| stats dc(status) AS dc_status values(status) AS status BY User
| search dc_status=1 abs status="OK"
| table User

Ciao.

Giuseppe

Skysurfer
Explorer

Thanks a lot @gcusello . It worked 😁

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

marysan
Communicator

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

Tags (2)
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...