Splunk Search

Search for non-matched values

appleman
Contributor

Hello,

Is there any way to search non-matched values from two tables like you can do on excel using VLOOKUP?

Thank you.


Here is a sample table.

table A table B
12345 12345
23456 23456
34567 34567
45678 56789
56789 67890
67890
78901
89012

non-matched value = 45678, 78901, 89012

Tags (1)
1 Solution

Suda
Communicator

Hello,

Could you use combination "NOT", "subsearch" and "return"?

(your search) NOT [search (your search to get Table_B) | return [<count>] Table_A=Table_B ]

In the subsearch, you may get a list of Table_B.

And Table_B is changed to Table_A field with using "alias" feature of "return" command.

The "NOT" located in front of the subsearch makes negative match.

So you may get the part of Table_A values which are not in Table_B.

I hope it helps you.

Thank you.

View solution in original post

Suda
Communicator

There is another approach.

(source=A (id=7 OR id=57) id!=74 name!=テスト name!=検証 contract_status_A=0 ) 
OR (earliest=-7d@d latest=now source=B (id=7 OR id=57) id!=74* (type=M OR type=W))
| stats count count(eval(source="A")) AS A count(eval(source="B")) AS B by id
| search B=0 A>0

If you cannot get your expected result, could you try to remove "|search B=0 A>0"?

You may know how Splunk handles your data. And if you share it with us, I will help you more.

Thanks.

somesoni2
SplunkTrust
SplunkTrust

Try this

source=A (id=7 OR id=57) id!=74 name!=テスト  name!=検証  contract_status_A=0 | stats count by id | eval source="A"
|append [search earliest=-7d@d latest=now source=B (id=7 OR id=57) id!=74* (type=M OR type=W) | stats count by id | eval source="B"]
|stats values(source) as source by id | where mvcount(source)=1 and source="A"

gfuente
Motivator

Hello

Have you tried the | set diff command?

With this syntax:

| set diff [search yoursearch | table tableA] [search yourothersearch | table tableB]

Regards

gfuente
Motivator

Make sure that the field name of the columns are the same. And try to reverse the searches:

| set diff [search yourothersearch | table commonfield] [search yourothersearch | table commonfield]

0 Karma

appleman
Contributor

Thank you. I tired it, and I got the wrong answer, the result count was 33 where it should be 13.

0 Karma

Suda
Communicator

Hello,

Could you use combination "NOT", "subsearch" and "return"?

(your search) NOT [search (your search to get Table_B) | return [<count>] Table_A=Table_B ]

In the subsearch, you may get a list of Table_B.

And Table_B is changed to Table_A field with using "alias" feature of "return" command.

The "NOT" located in front of the subsearch makes negative match.

So you may get the part of Table_A values which are not in Table_B.

I hope it helps you.

Thank you.

appleman
Contributor

It actually solve my question. Thank you very much!

appleman
Contributor

I actually added "" after "id=7" or "id=57", like "id=7" or "id=57*".
I got the below result.
1) total count=40
2) total count=27

So I should get the 13 ids as a result, but I currently don't.

0 Karma

Suda
Communicator

Could you check each search result?

1)
source=A (id=7 OR id=57) id!=74 name!=テスト name!=検証 contract_status_A=0 | dedup id | table id

2)
earliest=-7d@d latest=now source=B (id=7 OR id=57) id!=74* (type=M OR type=W) | dedup id | table id

I guess that you may get "id=7" or "id=57" at the maximum.
Is it your expected result?

0 Karma

appleman
Contributor

Thank you. Now I don't get an error, but I cannot get the right result.

0 Karma

Suda
Communicator

Could you tell me what error do you have?

And could you try the following query?

source=A (id=7 OR id=57) id!=74 name!=テスト name!=検証 contract_status_A=0 | dedup id | table id | search NOT [search earliest=-7d@d latest=now source=B (id=7 OR id=57) id!=74* (type=M OR type=W) | dedup id | table id | return 1000 id]

You may not need to set "alias=" in your query, I think.

And I'm sorry that I forget to tell you. The "return" command might be needed to set the "count" option.

0 Karma

appleman
Contributor

I get an error on my query....

source=A (id=7* OR id=57*) id!=74* name!=テスト name!=検証 contract_status_A=0 | dedup id | table id | search NOT [search earliest=-7d@d latest=now source=B (id=7 OR id=57*) id!=74* (type=M OR type=W) | dedup id | table id | return alias=id]

0 Karma

appleman
Contributor

There are table A and B, and both of them have numbers(table A has more numbers than B). All numbers in table B are in table A.
I want to know the numbers which are not in B but there are in A, in other words, the numbers don't match each other.
Please take a look at the sample table column above.

Thank you.

0 Karma

raghu0463
Explorer

did you get answer for this ? 

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Could you provide your sample data/expected output? The requirement does seem feasible with Splunk.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...