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
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
|mvexpand Account
|eval flag=if(match(Account,user),1,0)
|search flag=0
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
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
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 |
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
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
Thanks a lot @gcusello . It worked 😁
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
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