Splunk Join - Subsearch Commands & Examples

Splunk Join

The join command is used to combine the results of a sub search with the results of the main search. One or more of the fields must be common to each result set. You can also combine a search result set to itself using the selfjoin command.

Syntax

join [join-options...] [field-list] subsearch

Required arguments

subsearch

Syntax: "[" subsearch "]"

Description: A secondary search where you specify the source of the events that you want to join. The subsearch must be enclosed in square brackets. The results of the subsearch should not exceed available memory.

Limitations on the subsearch for the join command are specified in the limits.conf.spec file. The limitations include the maximum subsearch to join against, the maximum search time for the subsearch, and the maximum time to wait for subsearch to fully finish.

Learn how to use Splunk, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free "Splunk Training" Demo!

Optional arguments

field-list

Syntax: , , ...

Description: Specify the fields to use for the join. If no fields are specified, all of the fields that are common to both result sets are used.

Field names must match, not just in name but also in the case. You cannot join product_id with product_ID. You must first change the case of the field in the subsearch to match the field in the main search.

join-options

Syntax: type=(inner | outer | left) | usetime= | earlier= | overwrite= | max=

Description: Options to the join command. Use either outer or left to specify a left outer join.

Checkout Splunk Interview Questions

Descriptions for the join-options argument

type

Syntax: type=inner | outer | left

Description: Indicates the type of join to perform. The difference between an inner and a left (or outer) join is how the events are treated in the main search that do not match any of the events in the subsearch. In both inner and left joins, events that match are joined. The results of an inner join do not include events from the main search that have no matches in the subsearch. The results of a left (or outer) join includes all of the events in the main search and only those values in the subsearch have matching field values.

Default: inner

usetime

Syntax: usetime=

Description: A Boolean value that Indicates whether to use time to limit the matches in the subsearch results. Used with the earlier option to limit the subsearch results to matches that are earlier or later than the main search results.

Related Article: Splunk Alert And Report

Default: true

earlier

Syntax: earlier=

Description: If usetime=true and earlier=true, the main search results are matched only against earlier results from the subsearch. If earlier=false, the main search results are matched only against later results from the subsearch. Results that occur at the same time (second) are not eliminated by either value.

Default: true

overwrite

Syntax: overwrite=

Description: Indicates whether fields from the subresults overwrite the fields from the main results, if the fields have the same field name.

Default: true

max

Syntax: max=

Description: Specifies the maximum number of subsearch results that each main search result can join with. If set to max=0, there is no limit.

Default: 1

Usage

Use the join command when the results of the subsearch are relatively small, for example, 50,000 rows or less. To minimize the impact of this command on performance and resource consumption, Splunk software imposes some default limitations on the subsearch.

Related Page: Splunk Streamstats Command

Examples 

Example 1

Combine the results from a main search with the results from a subsearch search vendors. The result sets are joined on the product_id field, which is common to both sources.

... | join product_id [search vendors]

Example 2

If the field names in the sources do not match, you can rename the field in the subsearch result set. The field in the main search is product_id. The field in the subsearch is pid.

Note: The field names must match in name and in case. You cannot join product_id with product_ID.

... | join product_id [search vendors | rename pid AS product_id]

Related Article: What Are Splunk Apps And Add-ons And Its Benefits?

Example 3

By default, only the first row of the subsearch that matches a row of the main search is returned. To return all of the matching subsearch rows, include the max= option and set the value to 0. This option joins each matching subsearch row with the corresponding main search row.

... | join product_id max=0 [search vendors]

Example 4

The dashboards and alerts in the distributed management console show you performance information about your Splunk deployment. The Resource Usage: Instance dashboard contains a table that shows the machine, number of cores, physical memory capacity, operating system, and CPU architecture.

To display the information in the table, use the following search. This search includes a join command. The search uses the information in the dmc_assets table to look up the instance name and machine name. The search then uses the serverName field to join the information with information from the /services/server/info REST endpoint. The /services/server/info is the URI path to the Splunk REST API endpoint that provides hardware and operating system information for the machine. The $splunk_server$ part of the search is a token variable.

| inputlookup dmc_assets
| search serverName = $splunk_server$
| stats first(serverName) AS serverName, first(host) AS host, first(machine) AS machine
| join type=left serverName
  [ | rest splunk_server=$splunk_server$ /services/server/info
  | fields serverName, numberOfCores, physicalMemoryMB, os_name, cpu_arch]
| fields machine numberOfCores physicalMemoryMB os_name cpu_arch
| rename machine AS Machine, numberOfCores AS "Number of Cores",
 physicalMemoryMB AS "Physical Memory Capacity (MB)", os_name AS "Operating System",
 cpu_arch AS "CPU Architecture"