SELECT n.NodeID, n.Caption as Node, a.ID as ApplicationID, a.Name as [Application], c.ID as ComponentId, c.Name as Component, cts.Value as TemplateCredId, cs.[Value] as OverridenCredId, cred.Name as CredName FROM APM_Component c Right JOIN APM_Application a ON c.ApplicationID = a.ID LEFT JOIN Nodes n ON a.NodeID = n.NodeID LEFT JOIN APM_ComponentSetting cs ON cs.ComponentID = c.ID AND cs.[Key] = '__CredentialSetId' LEFT JOIN APM_ComponentTemplate ct ON ct.ID = c.TemplateID LEFT JOIN APM_ComponentTemplateSetting cts ON cts.ComponentTemplateID = ct.ID AND cts.[Key] = '__CredentialSetId' LEFT JOIN [Credential] cred ON cred.ID = ISNULL(cs.Value, cts.Value) This is what I have so far, still plugging away. Still muted progress.
Here is what I use for similar report:
FROM APM_Application RIGHT OUTER JOIN
Nodes ON APM_Application.NodeID = Nodes.NodeID
WHERE (Nodes.NetworkCategory = 'Servers') AND (APM_Application.Name IS NULL)
ORDER BY Nodes.Caption
This works for me. The Nodes.NetworkCategory is a custom property we assign to servers. You may or may not have the same criteria where you can filter out non-server nodes.
Hope this helps some...
Awesome. A tweak here and there and it snapped right in. I was looking in all the wrong places and joining backwards.
You are most welcome - thwack is a great resource.
You may find the following helpful as well.
In your example this will only return nodes with no apps assigned at all, but what if you need to know which nodes do not have specific application assigned? This will not work if some other applications are assigned to node already?
The below solution is what I use for compliance reporting on unassigned applications:
Hope this helps,