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
Champion

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
Champion

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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...