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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...