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!

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...