Splunk Search

What is the relation between the Splunk inner/left join and the ones in relational databases?

ddrillic
Ultra Champion

What's the relation between the Splunk inner/left joins and the ones in relational databases, functionality and terminology wise?

Tags (3)
0 Karma

woodcock
Esteemed Legend

DO NOT USE JOINS IN SPLUNK. See here for visual representations:
https://goo.gl/images/zyDpMD

Continuing with the explanation of @kashz:
Assume:
sourcetype=A contains {ip, more-columns-specific-to-sourcetype-A}
sourcetype=B contains {ip, more-columns-specific-to-sourcetype-B}
and furthermore that you have a search like this:

(index=A AND sourcetype=A) OR (index=B AND sourcetype=B)
| stats dc(sourcetype) AS sourcetypeCount values(*) AS * BY ip

INNER JOIN on the ip field between them will return only those contents that are matching in the both sourcetype values on the ip field. To do this, finish your search with this:

| where sourcetypeCount=2

LEFT JOIN A on the ip field will return the FULL sourcetype=A dataset (the "left" one) and only those contents from the souretype=B dataset (the "right" one) which match on field ip. To do this, finish your search with this:

| where sourcetype="A"

LEFT JOIN B on the ip field will return the EXCLUSIVE sourcetype=A dataset (the "left" one) that do not overlap the contents from the souretype=B dataset (the "right" one) which match on field ip. To do this, finish your search with this:

| where sourcetype="A" AND sourcetypeCount=1

RIGHT JOIN B on the ip field will return the FULL sourcetype=B dataset (the "right" one) and only those contents from the souretype=A dataset (the "left" one) which match on field ip. To do this, finish your search with this:

| where sourcetype="B"

RIGHT JOIN A on the ip field will return the EXCLUSIVE sourcetype=B dataset (the "right" one) that do not overlap the contents from the souretype=A dataset (the "left" one) which match on field ip. To do this, finish your search with this:

| where sourcetype="B" AND sourcetypeCount=1

OUTER JOIN on the ip field will return both datasets in FULL minus the events that that have ip in both datasets. To do this, finish your search with this:

| where sourcetypeCount=1

FULL JOIN on the ip field will return both datasets in FULL. To do this, do nothing.

woodcock
Esteemed Legend

Using join is bad because it uses subsearches which have time and results limits which are enforced without obvious indications leaving users with no easy way to know that the results are incomplete.

asveturi
Path Finder

Thank you for sharing the docs. They helped a lot.

0 Karma

kashz
Explorer

Splunk Documentation on Joins: https://docs.splunk.com/Documentation/Splunk/7.2.4/SearchReference/Join

To give you a clear picture:
Splunk only support INNER, LEFT and OUTER joins where as in SQL based queries can have a lot more variations; the concept remains the same.

Assume:
DATASET-A contains {ip, more-columns}
DATASET-B contains {ip, more-columns}

  • INNER JOIN on the ip field between them will return only those contents that are matching in the both dataset on the ip field.
  • LEFT JOIN on the ip field will return the FULL DATASET-(left-one) and only those contents from the other dataset-(right-one) which match on field ip
  • OUTER JOIN on the ip field will return both datasets in FULL with null values entered in places where a field cannot be found based on the join condition.
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...