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 Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...