How to create a search that combines the two searches?

[hyewonkim]

index	title	id
A	AA	111
A	CC	111
B	BB	111

if the index is A and the title is AA, i'm trying to find id in index BB and look up how many.
In the above example, the second is that the title is CC, so even if the id value is the same, it is not counted.
there is 1 id 111 in index B, So the answer I want is 1.

How do I look up the query?

[yuanliu]

You can use subsearch for title AA in index A to restrict to the desired id, like

index = B 
    [search index = A title = AA
    | dedup id
    | fields id]
| stats count

Hope this helps.

[hyewonkim]

It's a failure.

I want to make sure that only the ID searched in number 1 is searched in number 2.

1.   index=A title=AA

2.   |append [|search index=B]

[ITWhisperer]

Given your limited construct example, the solution proposed by @yuanliu works. If this doesn't work for your real usecase, please provide more realistic examples showing how the proposed solution does not fit your needs.

[hyewonkim]

no	index	title	or_id	ev_id
1	A	check	111
2	A	check	222
3	A	check	333
4	A	confirm	444
5	A	confirm	555
6	A	confirm	666
7	B	OK		111
8	B	OK		777
9	B	OK		888
10	B	OK		999
11	B	NO		123
12	B	NO		666
13	B	NO		234
14	B	NO		222

Let me give you a specific example again.

In the case above,

I want to check if the or_id of the event with title "check" in index=a has the same value in the ev_id of index=b.

I want to look up or_id or ev_id if they have the same value.

In the example, numbers 1 and 7, 2 and 14 are the same, and the value I want to look up is 111,777.
Numbers 6 and 12 are the same, but since title 6 is not a check, it is not counted in the results.

[ITWhisperer]

Assuming you mean 111 and 222 (not 777 - that's one of the problems with constructed examples!), you could try something like this

| eval id=case(index="A" AND title="check", or_id, index="B", ev_id)
| stats dc(index) as indexes by id
| where indexes=2

[hyewonkim]

omg!! sorry for the confusion. 111,222 is correct. 

I searched as shown below, and all idexes values ​​are 1. 

index IN (A, B) 

| eval id=case(index="A" AND title="check", or_id, index="B", ev_id)

| stats dc(index) as indexes by id 

| where indexes=2

 Ultimately, I want to find the value that has or_id but does not have ev_id. 

The final answer I really want is 333 in the example above.

 | was trying to find the case "where count<2", 

but I think about it again,

 there may be a case where there is an ev_id and an or_id.

 Could you please help me further?

[ITWhisperer · Accepted solution]

index IN (A, B)

| eval id=case(index="A" AND title="check", or_id, index="B", ev_id)
| dedup id index ``` this line is only required if or_id or ev_id can occur multiple times in either of the indexes ```
| eval index_no=if(index="A",1,2)
| stats sum(index_no) as indexes by id
| where indexes=1

[hyewonkim]

Oh, I guess there was a typo.

The search works well now!

thank you 🙂

[hyewonkim]

I looked it up and all the indexes values ​​are 2, so where < 1 is 0.

There is clearly a value without ev_id.