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
Revered Legend

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
Revered Legend

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

0 Karma
Get Updates on the Splunk Community!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...