Splunk Search

## How to map results of 2 searches using Join?

Explorer

as a result of an inputlookup, I have the following table 1(a dish that a chef can prepare, and the chef's name):

 dish chef a gordon ramsay b gordon ramsay c Guy Fieri d Guy Fieri f Jamie Oliver g gordon ramsay h gordon ramsay Rachael Ray

and I have the following table from another outputlookup (the restaurant where a chef works, and the chef name):

 restaurant chef 1 gordon ramsay 2 Guy Fieri 3 Guy Fieri 4 Jaime Oliver 5 Michael Caines

I want to combine the 2 tables into this:

 restaurant dish chef 1 a gordon ramsay 1 b gordon ramsay 2 c Guy Fieri 2 d Guy Fieri 3 c Guy Fieri 3 d Guy Fieri 4 f Jamie Oliver 1 g gordon ramsay 1 h gordon ramsay 5 null Michael Caines null h Rachael Ray

Basically, based on tables 1 & 2, how do I get a table telling me the restaurant where a chef works, the dishes that he/she would prepare, and the chef's name?

In stuff I've tried,  I'm able to combine table 1 & 2 with the join command, but a lot of results end up getting filtered out. (eg. I might end up with one result per chef but not getting all the dishes, or one result per dish but not getting all the restaurants).

Labels (2)

• ### table

Tags (5)
1 Solution
SplunkTrust

Your spelling of Jamie Oliver in your restaurants is wrong - "Jaime" and there is no Rachel Ray in dishes for dish h.

However, this will given you the table - not exactly the same order

| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| stats values(*) as * by restaurant chef
| mvexpand dish
| table restaurant dish chef

this also, with ordering more as you have in your table

| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| mvexpand restaurant
| eventstats count(dish) as dishes by chef
| where dishes=0 OR isnotnull(dish)
| table restaurant dish chef dishes

SplunkTrust

Your spelling of Jamie Oliver in your restaurants is wrong - "Jaime" and there is no Rachel Ray in dishes for dish h.

However, this will given you the table - not exactly the same order

| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| stats values(*) as * by restaurant chef
| mvexpand dish
| table restaurant dish chef

this also, with ordering more as you have in your table

| inputlookup dishes.csv
| lookup restaurants.csv chef
| inputlookup append=t restaurants.csv
| mvexpand restaurant
| eventstats count(dish) as dishes by chef
| where dishes=0 OR isnotnull(dish)
| table restaurant dish chef dishes

Explorer

just wanna say thanks. Your solution worked for my case 🙂

Get Updates on the Splunk Community!

#### Adoption of RUM and APM at Splunk

Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

#### March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

#### Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...