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!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...