Vlookup between 2 queries

[evelandi]

Hello folks,

i have a list of hardware for an account X and i want to know if all the hawrdware list is present in other query which is the hardware list for my Y and Z account.

Query for X account

index=x sourcetype=hw
| table productId,index
| dedup productId,index

Query for Y and Z accounts

index=x or index=z sourcetype=hw
| table productId,index
| dedup productId,index

i would like to obtain a table similar like this:

.COLUMNA | COLUMNB
productIdX | is in Y | index
........routerx | true..| z
...... switchx | false.| y
..... firewallx | true..| y

thansk in advance for your help.

[cmerriman]

I just want to prefix with the queries and table example, while very much appreciated, are confusing, as the second query states that it is for Y andZ but has index=x and that the table has index=y as false for is in y. So if i misunderstood what you needed, I'm sorry, and if you could clarify what you need, that'd be great. Otherwise, if this works for you, super awesome.
what about something like this:

(index=x OR index=y OR index=z) sourcetype=hw
|table index productId 
|dedup productId index
|eval is_in_y=if(index="x","false","true")
|table productId is_in_y index

[evelandi]

thanks cmerriaman for pointing that. my bad, the correction for the second query is:

 index=y or index=z sourcetype=hw
 | table productId,index
 | dedup productId,index

so your suggestion doesn't work for this case because there is no index=x in Y or Z. the final goal for this is to count how many productIds are in Y and Z. for instance if X has 20 products i would like to know how many products are in Y or Z. if Y has 18 of 20 and Z has 15 of 20, i should go for Y.

if i am not very clear just let me know.