How can I join values of a 'single field name' to two lookups and get the corresponding values from each lookup

[Woodpecker]

Hi,

I have search which has S_host name values of different DB instances say MSSQL and Oracle in a single field.
eg: S_Host Name has values such as

11xx
22xx
11yy
22yy

And, I have the seperate lookups for both MSSQL & Oracle ie., lookup1 & lookup 2

lookup 1 contains

hostname  supportgroup                serviceoffering
11xx      random support group1      random service offering1
22xx      random support group2      random service offering2

lookup 2 contains

hostname  serviceoffering               supportgroup
11yy      random service offering1      random support group1
22yy      random service offering2      random support group2

My base search is

index=a sourcetype="a" "field_name"="random_value" 
| dedup "IP" 
| stats values("S_Host Name") as "S_Host Name" by "IP"

Now I have to join like this

index=a sourcetype="a" "field_name"="random_value" 
| dedup "IP" 
| stats values("S_Host Name") as "S_Host Name" by "IP"
| join type=left "S_Host Name" (
[|inputlookup lookup 1
|fields hostname serviceoffering supportgroup | rename hostname as S_host Name]
[|*inputlookup lookup 2 |fields hostname serviceoffering supportgroup | rename hostname as S_host Name])

But the above search is not working...

Can someone help me with this?

[gcusello · Accepted solution]

Hi @Woodpecker,

use the lookup command (https://docs.splunk.com/Documentation/Splunk/9.0.3/SearchReference/Lookup) that's something neatr to a left join:

index=a sourcetype="a" "field_name"="random_value" 
| stats values("S_Host Name") AS S_host BY IP
| lookup lookup1 hostname AS S_host OUTPUT serviceoffering AS serviceoffering1  supportgroup AS supportgroup1
| lookup lookup2 hostname AS S_host OUTPUT serviceoffering AS serviceoffering2  supportgroup AS supportgroup2
| table IP S_host serviceoffering1 supportgroup1 serviceoffering2 supportgroup2

Ciao.

Giuseppe

[gcusello]

Hi @Woodpecker ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉