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
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.
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.
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"
Hello
Have you tried the | set diff command?
With this syntax:
| set diff [search yoursearch | table tableA] [search yourothersearch | table tableB]
Regards
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]
Thank you. I tired it, and I got the wrong answer, the result count was 33 where it should be 13.
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.
It actually solve my question. Thank you very much!
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.
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?
Thank you. Now I don't get an error, but I cannot get the right result.
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.
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]
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.
did you get answer for this ?
Could you provide your sample data/expected output? The requirement does seem feasible with Splunk.