topic Re: Search for non-matched values in Splunk Search

Search for non-matched values

appleman — Mon, 04 Aug 2014 10:26:01 GMT

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

Re: Search for non-matched values

somesoni2 — Mon, 04 Aug 2014 12:41:08 GMT

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

Re: Search for non-matched values

appleman — Tue, 05 Aug 2014 01:15:37 GMT

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.

Re: Search for non-matched values

Suda — Tue, 05 Aug 2014 03:51:43 GMT

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.

Re: Search for non-matched values

appleman — Mon, 28 Sep 2020 17:15:43 GMT

I get an error on my query....

Re: Search for non-matched values

Suda — Mon, 28 Sep 2020 17:15:48 GMT

Could you tell me what error do you have?

And could you try the following query?

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.

Re: Search for non-matched values

appleman — Tue, 05 Aug 2014 06:56:23 GMT

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

Re: Search for non-matched values

Suda — Mon, 28 Sep 2020 17:15:51 GMT

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?

Re: Search for non-matched values

appleman — Tue, 05 Aug 2014 07:12:34 GMT

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.

Re: Search for non-matched values

gfuente — Tue, 05 Aug 2014 08:04:22 GMT

Hello

Have you tried the | set diff command?

With this syntax:

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

Regards

Re: Search for non-matched values

appleman — Tue, 05 Aug 2014 09:02:19 GMT

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

Re: Search for non-matched values

gfuente — Tue, 05 Aug 2014 09:28:54 GMT

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]

Re: Search for non-matched values

somesoni2 — Tue, 05 Aug 2014 12:41:51 GMT

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"

Re: Search for non-matched values

Suda — Wed, 06 Aug 2014 00:49:34 GMT

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.

Re: Search for non-matched values

appleman — Fri, 08 Aug 2014 08:54:01 GMT

It actually solve my question. Thank you very much!

Re: Search for non-matched values

raghu0463 — Fri, 28 May 2021 17:22:16 GMT

did you get answer for this ?