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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...