selfjoin

Description

Join search result rows with other search result rows in the same result set, based on one or more fields that you specify.

Syntax

selfjoin [<selfjoin-options>...] <field-list>

Required arguments

<field-list>

Syntax: <field>...

Description: The field or list of fields to join on.

Optional arguments

<selfjoin-options>

Syntax: overwrite=<bool> | max=<int> | keepsingle=<bool>

Description: Options that control the search result set that is returned. You can specify one or more of these options.

Selfjoin options

keepsingle

Syntax: keepsingle=<bool>

Description: Controls whether or not to retain results that have with a unique value in the join fields. When keepsingle=true search results that have no other results to join with are kept in the output.

Default: false

max

Syntax: max=<int>

Description: Indicates the maximum number of 'other' results to join with each main result. If max=0, there is no limit. This argument sets the maximum for the 'other' results. The maximum number of main results is 100,000.

Default: 1

overwrite

Sytnax: overwrite=<bool>

Description: When overwrite=true, causes fields from the 'other' results to overwrite fields of the main results. The main results are used as the basis for the join.

Default: true

Usage

Self joins are more commonly used with relational database tables. They are used less commonly with event data.

An example of an events usecase is with events that contain information about processes, where each process has a parent process ID. You can use the selfjoin command to correlate information about a process with information about the parent process.

Basic example

1: Use a single field to join results

Extended example

The following example shows how the selfjoin command works against a simple set of results.
You can follow along with this example on your own Splunk instance.

This example builds a search incrementally. With each addition to the search, the search is rerun and the impact of the additions are shown in a results table. The values in the _time field change each time you rerun the search. However, in this example the values in the results table are not changed so that we can focus on how the changes to the search impact the results.

1. Start by creating a simple set of 5 results by using the makeresults command.

| makeresults count=5

There are 5 results created, each with the same timestamp.

_time

2018-01-18 14:38:59

2018-01-18 14:38:59

2018-01-18 14:38:59

2018-01-18 14:38:59

2018-01-18 14:38:59

2. To keep better track of each result use the streamstats command to add a field that numbers each result.

| makeresults count=5 | streamstats count as a

The a field is added to the results.

_time

a

2018-01-18 14:38:59

1

2018-01-18 14:38:59

2

2018-01-18 14:38:59

3

2018-01-18 14:38:59

4

2018-01-18 14:38:59

5

3. Additionally, use the eval command to change the timestamps to be 60 seconds apart. Different timestamps makes this example more realistic.

7. To understand how the selfjoin command joins the results together, remove the | selfjoin joiner portion of the search. Then modify the search to append the values from the a field to the values in the b and c fields.

Enter your email address, and someone from the documentation team will respond to you:

Send me a copy of this feedback

Please provide your comments here. Ask a question or make a suggestion.

Feedback submitted, thanks!

You must be logged into splunk.com in order to post comments.
Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic.
If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk,
consider posting a question to Splunkbase Answers.

0
out of 1000 Characters

Your Comment Has Been Posted Above

We use our own and third-party cookies to provide you with a great online experience. We also use these cookies to improve our products and services, support our marketing campaigns, and advertise to you on our website and other websites. Some cookies may continue to collect information after you have left our website.
Learn more (including how to update your settings) here »