Splunk Search

How to join with one key but multiple results?

fereze
Engager

Hi I have a table similar to this:

Brand ID_EMP

Nike 123

Adidas 456

Lotto 123

 

other table like this:

code name

123 Smith

456 Myers

 

The result should be

Nike 123 Smith 

Adidas 456 Myers 

Lotto 123 Smith

 

 

but insted of this I'm getting:

Nike 123 Smith john

adidas 456 Myers. Mike

 

This is the query that I'm using.

| dbxquery query="SELECT * FROM Clients ;" connection="Clients"
| where NOT like(SAFE_NAME,"S0%")
|rename ID_EMP as code
| join type=left [|  inputlookup append=t Emp.csv
| table code, name, STATUS
]| fillnull value="Blank" STATUS
| dedup code
| where STATUS!="Blank"

Labels (1)
0 Karma

nadlurinadluri
Communicator

Please find the sample query with the data points, hope it helps you to change your query accordingly - 

Also, in most of the cases you can skip using join command if one of your datasources is a lookupfile, you can use |lookup filename.csv command in those cases.

Considering the below is in lookupfile emp.,csv

code name
123 Smith
456 Myers

Final Query - 

| makeresults 
| eval BRAND_EMPID="Nike,123;Adidas,456;Lotto,123" 
| makemv delim=";" BRAND_EMPID 
| mvexpand BRAND_EMPID 
| eval Brand=mvindex(split(BRAND_EMPID,","),0) 
| eval ID_EMP=mvindex(split(BRAND_EMPID,","),1) 
| table Brand ID_EMP 
| lookup emp.csv Code AS ID_EMP
| table Brand ID_EMP NAME


 Let me know if the above query helped or if you feel anything needs to be changed in the above example.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...