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!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...