Node application component assigned credentials SWQL query

Update 04/20/2022... re-wrote the SQL query into a SWQL query, the file to download is the SWQL query but I'll keep both versions available here for reference.

SWQL:

SELECT
	 n.NodeID
	,n.Caption AS [DisplayName]
	,n.SysName AS [Hostname]
	,n.IP_Address AS [IP Address]
	,n.ObjectSubType AS [Monitoring Method]
	,a.ID as ApplicationID
	,a.Name as [Application]
	,c.ComponentID as ComponentId
	,c.Name as Component
	,cts.Value as TemplateCredId
	,cs.[Value] as OverridenCredId
	,cred.Name as CredName
FROM Orion.APM.Component AS c
INNER JOIN Orion.APM.Application AS a ON a.ID = c.ApplicationID
INNER JOIN Orion.Nodes AS n ON a.NodeID = n.NodeID
LEFT JOIN Orion.APM.ComponentSetting AS cs ON cs.ComponentID = c.ComponentID AND cs.[Key] = '__CredentialSetId'
LEFT JOIN Orion.APM.ComponentTemplate AS ct ON ct.ID = c.TemplateID
LEFT JOIN Orion.APM.ComponentTemplateSetting AS cts ON cts.ComponentTemplateID = ct.ID AND cts.[Key] = '__CredentialSetId'
LEFT JOIN Orion.Credential AS cred ON cred.ID = ISNULL(cs.Value, cts.Value)
WHERE cred.Name IS NOT NULL

SQL:

SELECT
	 n.NodeID
	,n.Caption AS [DisplayName]
	,n.SysName AS [Hostname]
	,n.IP_Address AS [IP Address]
	,n.ObjectSubType AS [Monitoring Method]
	,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 SolarWindsOrion.dbo.APM_Component AS c
INNER JOIN SolarWindsOrion.dbo.APM_Application AS a ON a.ID = c.ApplicationID
INNER JOIN SolarWindsOrion.dbo.Nodes AS n ON a.NodeID = n.NodeID
INNER JOIN SolarWindsOrion.dbo.NodesCustomProperties AS cp ON cp.NodeID = n.NodeID
LEFT JOIN SolarWindsOrion.dbo.APM_ComponentSetting AS cs ON cs.ComponentID = c.ID AND cs.[Key] = '__CredentialSetId'
LEFT JOIN SolarWindsOrion.dbo.APM_ComponentTemplate AS ct ON ct.ID = c.TemplateID
LEFT JOIN SolarWindsOrion.dbo.APM_ComponentTemplateSetting AS cts ON cts.ComponentTemplateID = ct.ID AND cts.[Key] = '__CredentialSetId'
LEFT JOIN SolarWindsOrion.dbo.Credential AS cred ON cred.ID = ISNULL(cs.Value, cts.Value)
WHERE cred.Name IS NOT NULL

Anonymous