Search for non-matched values

[appleman]

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

[Suda]

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]

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]

Hello

Have you tried the | set diff command?

With this syntax:

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

Regards

[gfuente]

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]

[appleman]

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

[Suda · Accepted solution]

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]

It actually solve my question. Thank you very much!

[appleman]

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.

[Suda]

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?

[appleman]

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

[Suda]

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.

[appleman]

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]

[appleman]

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.

[raghu0463]

did you get answer for this ? 

[somesoni2]

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