Is it possible to combine 3 searches with JOIN?

[kamaleshwarn]

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

[bhawkins1]

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

[somesoni2]

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.

[kamaleshwarn]

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.

I already tried with index=a |join ipaddress [search index=b |table ipaddress uniqueId ]| join uniqueId [search index=c|table uniqueId sample]|stats values(uniqueId) by ipaddress and index=a |join ipaddress [search index=b |table ipaddress uniqueId | join uniqueId [search index=c|table uniqueId sample]]|stats values(uniqueId) by ipaddress but the second part values are not populating

[cmerriman]

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