Splunk Search

What is the equivalent of excel's vlookup in Splunk?

jonglim
New Member

i'm trying to join these 2 tables.
table 1 : index ="A" sourcetype = A WITH fields deviceName, physicalElementId, physicalType, productFamily, productId, productType, serialNumber
table 2 : index ="A" sourcetype = B WITH fields currentEoxMilestone, devDeviceIp, devProductFamily, devProductId, deviceName, physicalElementId, physicalType, productId

this is my search :
index ="A" sourcetype = A| fields deviceName, physicalElementId, physicalType, productFamily, productId, productType, serialNumber
| join type=left physicalElementId [ search sourcetype = B| fields currentEoxMilestone, devDeviceIp, devProductFamily, devProductId, deviceName, physicalElementId, physicalType, productId ]
| table currentEoxMilestone, devDeviceIp, devProductFamily, devProductId, deviceName, physicalElementId, physicalType, productId, deviceName, physicalElementId, physicalType, productFamily, productId, productType, serialNumber
| dedup physicalElementId
| sort -deviceName

the problem is that the resulting table has holes on them because of the join type=left. devProductId is absent in sourcetype = A. devProductId is present in sourcetype = B.

I'm thinking, i will need to create another Table - Table C. Table C basically be Table A + additional field devProductId. field devProductId would come from Table B.

How do it do this ? I tried append, appendcols, join, lookup, etc. inputlookups requires a .csv file which i don't have. I have Table B.
Thanks

Tags (1)
0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

hi @jonglim,

Try this.

index ="A" sourcetype = A  OR sourcetype = B | fields deviceName, physicalElementId, physicalType, productFamily, productId, productType, serialNumber, currentEoxMilestone, devDeviceIp, devProductFamily, devProductId, deviceName
| stats max(*) as * by physicalElementId
| table currentEoxMilestone, devDeviceIp, devProductFamily, devProductId, deviceName, physicalElementId, physicalType, productId, deviceName, physicalElementId, physicalType, productFamily, productId, productType, serialNumber
| sort - deviceName
0 Karma

jonglim
New Member

didn't help.

the "| stats max(*) as * by physicalElementId" basically replaced the join command.

what I need is to create 1 column called devProductId. The content of devProductId depends on column deviceName.

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

you need Table C = Table A + additional field, then do this:

index ="A" sourcetype = A OR sourcetype = B | fields deviceName, physicalElementId, physicalType, productFamily, productId, productType, serialNumber, devProductId] | stats max(*) as * by deviceName
0 Karma

jonglim
New Member

this is my current search results. Check this out to see what my problem is VISUALLY

0 Karma

efavreau
Motivator

Anytime I see the word "join" and "problem", I immediately think they're related and start there. This may not help your exact issue now, but it will help in the long term either way. As such, maybe read through this answer: https://answers.splunk.com/answers/387510/what-are-alternatives-to-using-the-join-command-fo.html
I'd also recommend the coalesce command: https://docs.splunk.com/Documentation/Splunk/8.0.2/SearchReference/ConditionalFunctions#coalesce.28X...

###

If this reply helps you, an upvote would be appreciated.
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 ...