Today’s topic of discussion is join command in Splunk. As we all work in Splunk we came across with various Splunk commands with their own functionality which gives us a better understanding of data, using those commands we can create reports, alerts and dashboards the way we want. Today we will be discussing Splunk’s very widely used command that is join command.
Join command allow us to get data from two different datasets which can be useful to get proper knowledge of data. From the 2 datasets there must be a common field with the help of that field we can join 2 different dataset and combine the result sets.
In the SQL language we use join command to join 2 different schema where we get expected result set. Same as in Splunk there are two types of joins.
- Inner Join
- Outer Join (Left)
Above example show the structure of the join command works
In Inner Join we join 2 dataset tables which is table A and B and the matching values from those tables is our results.
In Left join or outer join we join 2 dataset or tables where left table which is A gives all the results that it has having and take only matching values from Table B whereas other non matching values are displayed as blank.
Syntax for Join Command –
Index = “your_index”
| join type = (inner | left | outer) “common_field”
[ | search index = “your_index”
……………………….
]Example of Join Command.
Here we are using 2 datasets with separate index for data.
Above example is of data where we are having index=school_data which includes roll_no, div, std, rank
Above example is of index=name_data where we have fields id and Name.
We will be using these 2 tables and join the data among them. For using Join command we need a common field from those 2 table dataset in 1st example we are having roll_no in them while in 2nd example we are having id field where values are same in both the tables just the field name is different.
We will be joining these 2 fields and combining them in one from 1st table we are having all the required school_data but missing Name field in them where the Name field is available in name_data index we are going to combine the results having all the fields in one table. For this we are using Inner Join command to extract the common value fields from them.
Using above command we joined 2 dataset of index school_data and name_data with the help of roll_no field.
Further filtering out the query by adding only the specified roll_no values data by using where clause
It will only give the matching field values which are specified in command that is only for roll_no 1 2 and 3.
But when using same for outer join command in above example.
Here we are getting all the results from table A and B including matching and non matching events but the non matching fields are output as blank values. Left and Outer join are same we can use them interchangeably
Join commands comes with 2 attributes max and overwrite.
When executing subsearches in Splunk there is limitation that events of rows it processed by default it is 10000 which can be seen in limits.conf configuration by using max=0 attribute in join command we can get all the evens rows from the subsearch that we execute.
Example –
For attribute overwrite it takes Boolean values true/false by default it is set as true.
If the 1st dataset has same field name as in 2nd dataset keeping overwrite=false it will take
both field values having same field name without overwriting its values.
Example –
Here overwrite set as true for the field value Name because it is been available in both datasets.
so it is overwriting only the matching values but rest of the values remains same as of 1st Dataset.
In above example overwrite is set as false so the fields having matching values does not overwrite their values instead it creates new field row but it must be rename different as Name1.
If you are still facing issue regarding join command in splunk Feel free to Ask Doubts in the Comment Box Below and Don’t Forget to Follow us on 👍 Social Networks, happy Splunking >😉
