Splunk Search

Vlookup between 2 queries

evelandi
New Member

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.

Tags (1)
0 Karma

cmerriman
Super Champion

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
0 Karma

evelandi
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...