Splunk Search

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

Woodpecker
Path Finder

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?

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

 

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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

Hi @Woodpecker ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...