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

[jonglim]

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

[manjunathmeti]

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

[jonglim]

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.

[manjunathmeti]

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

[jonglim]

[efavreau]

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...