topic Re: Is it possible to combine 3 searches with JOIN? in Splunk Search

Is it possible to combine 3 searches with JOIN?

kamaleshwarn — Wed, 05 Oct 2016 10:40:20 GMT

Have question like how to join 3 subsearches, usually we can join the searches with similar field (ex: join samplefield [....). But here the scenario is bit different like below

index=* (search cmd) | join ipaddress [ search index=* ipaddress uniqueID....| join uniqueID [search index=*  uniqueID sample....]] | stats values(uniqueID) BY ipaddress

Is there any possibility to join like above scenario.. Thanks in advance

Re: Is it possible to combine 3 searches with JOIN?

somesoni2 — Wed, 05 Oct 2016 17:15:46 GMT

Technically, it is possible, but there could be a better way to achieve this. Could you please provide more details on what you're trying to achieve, how your data looks like from all three searches etc? Joins are expensive and should be avoided wherever possible.

Re: Is it possible to combine 3 searches with JOIN?

cmerriman — Thu, 06 Oct 2016 15:59:17 GMT

I agree with somesoni2. If you have 3 indexes/sourcetypes that all have the same joining field, you can do a index=a OR index=b OR index=c|stats values(d) by commonField
to join them all together.

However, there are cases where you have to join c to b and b to a because a and c do not have a common field to join on. I run into this in one of my reports.

this examples as a join inside of a join

index=a |join ipaddress [search index=b |table ipaddress uniqueId | join uniqueId [search index=c|table uniqueId sample]]|stats values(uniqueId) by ipaddress

or this joins them separately

index=a |join ipaddress [search index=b |table ipaddress uniqueId ]| join uniqueId [search index=c|table uniqueId sample]|stats values(uniqueId) by ipaddress

Re: Is it possible to combine 3 searches with JOIN?

kamaleshwarn — Fri, 07 Oct 2016 03:19:16 GMT

In my case data will be like first two will have same details like IP, ID, etc... and third part won't have IP but have ID and all index & sourcetypes are same. So want to join the third par with 2nd part based on ID field as common and have to combine the result with 1st part based on IP field as common.

Re: Is it possible to combine 3 searches with JOIN?

bhawkins1 — Fri, 07 Oct 2016 08:41:27 GMT

As pointed out above, there should be a better way to make your query than using multiple joins. However, it is possible. Here is an example query with four joins, with real data names redacted:

| pivot sdl s count(s) AS acr FILTER acrs is true | join [| pivot sdl s count(s) AS ncfs FILTER ucfs is true] | join [| pivot sdl s count(s) AS ucfs FILTER ucfsn is true] | join [| pivot sdl s count(s) AS dnds FILTER dndsn is true FILTER dnde is false] | join [| pivot sdl s count(s) AS dnde FILTER dndsn is true FILTER dnde is true] | eval ft="acr ncf ucf dnd" | makemv ft | mvexpand ft | eval so=case(ft=="acr", acr,ft="ncf", ncfs,ft="ucf",ucfs, ft="dnd", dnds) | eval enabled=case(ft=="acr", acre, ft="ncf", ncf_Enabled, ft="ucf", ucf_Enabled, ft="dnd", dnde) | fields ft,so,enabled