Splunk Search

How to map results of 2 searches using Join?

dasaed
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)
0 Karma
1 Solution

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

 

View solution in original post

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

 

dasaed
Explorer

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

0 Karma
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 ...