Usage Of Splunk Commands : Join

Usage Of Splunk Commands : Join

Hi everyone !!
Today we will learn about Join command. It is a very important command of Splunk, which is basically used for combining the result of sub search with the main search and importantly one or more fields should be common in both the result-sets.

Syntax:

<main_search> | join <join_options> <fields> [sub_search]

<main search> - It will be the search query of your dataset 1
<join options> - There are many join-options like type, overwrite,
                  max etc we will discuss only about type in this blog.
<fields> - It is the common field that is present in both of the 
           data-set.
[<sub search>] - it will be the search query of dataset 2

Showcase:

Basically, with join command, there are two joins is possible

1) Inner
2) Left or outer

Now what are these two things take a look into the below figure

So let’s take a look
Let’s take an example: we have two different datasets.
1st Dataset: with four fields – movie_id, language, movie_name, country

2nd Dataset: with two fields – id,director [here id in this dataset is same as movie_id in 1st dataset]

So let’s start.
Inner join: In case of inner join it will bring only the common field values from the two data-sets (by default it takes Inner join)

index="movie_details"
| table movie_id,language,movie_name,country
| join type=inner movie_id
   [ search index="movie_set2"
   | table id,director
   | rename id as movie_id]

Explanation:
In the above figure, we have added two result-sets using join command and we took movie_id as our matching field. If you look carefully then you can notice that in the sub-search we renamed the id field as movie_id because in the main search it’s named as movie_id. It will only show those results which are common in both the result-set depending on the movie_id field. As we discussed earlier, it is fetching only common data from both the datasets. see those extra rows from the 1st dataset are not showing because it’s not present in both datasets.

Summary:
That means it added two different datasets based on one common field.
NOTE: if we don’t mention any type (i.e inner or outer) with join command then by default it will take ty as inner.

Left or outer join: In this case it will bring all the fields from the 1st search query, and only the common field values from the 2nd query.

So let’s take a look.

index="movie_details"
| table movie_id,language,movie_name,country
| join type=outer movie_id
   [ search index="movie_set2"
   | table id,director
   | rename id as movie_id]

Explanation:
As you can see that it brings all the fields from the main search and only matching rows values from the sub search.
I think now you got an idea about join command.

Happy Splunking!!