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!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

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