topic Re: What is the equivalent of excel's vlookup in Splunk? in Splunk Search

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

jonglim — Mon, 16 Mar 2020 08:27:49 GMT

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

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

jonglim — Mon, 16 Mar 2020 08:36:01 GMT

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

efavreau — Mon, 16 Mar 2020 15:28:44 GMT

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.2C....29

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

manjunathmeti — Mon, 16 Mar 2020 16:23:45 GMT

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

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

jonglim — Tue, 17 Mar 2020 05:56:00 GMT

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.

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

manjunathmeti — Tue, 17 Mar 2020 08:14:13 GMT

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