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
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
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.
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
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