WMI Creds being Used ?

are you looking for WMI credentials being used for Nodes or Component Monitors?

For Nodes:

SELECT
n.Caption 'DEVICE'
,n.IP_Address 'IP ADDRESS'
,c.Name 'CREDENTIAL NAME'
FROM Credential c
JOIN NodeSettings s ON c.ID = s.SettingValue
JOIN Nodes n ON s.NodeID = n.NodeID
WHERE s.SettingName = 'WMICredential'
ORDER BY c.Name, n.Caption

For SAM Components:

SELECT
n.Caption 'DEVICE'
,a.Name 'APPLICATION'
,c.Name 'COMPONENT'
,ISNULL(r.Name,x.Name) 'CREDENTIAL NAME'
FROM APM_Component c
JOIN APM_Application a ON a.ID = c.ApplicationID
JOIN Nodes n ON n.NodeID = a.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] = '_CrednetialSetID'
LEFT JOIN Credential r ON r.ID = ISNULL(cs.Value,cts.Value)
JOIN
(
SELECT
n.NodeID
,c.Name
FROM Nodes n
JOIN NodeSettings s ON n.NodeID = s.NodeID
JOIN Credential c ON s.SettingValue = c.ID
WHERE n.ObjectSubType = 'WMI'
AND s.SettingName = 'WMICredential'
) x ON x.NodeID = n.NodeID
WHERE a.ID NOT IN
(
SELECT
a.ID
FROM APM_Application a
JOIN APM_ApplicationTemplate t ON t.ID = a.TemplateID
WHERE t.CustomApplicationType IS NOT NULL
)
ORDER BY a.Name, n.Caption, c.Name

Thanks Zack!!  That did the trick, and yes I was concerned with node data!

I am using this report to identify servers that have moved domains without getting their SLW WMI domain creds updated!  Thanks again!

Credentials is where all the creds live, but there are a ton of use cases that determine which joins need to be made to get them accurately reflected.  I have this custom swql query that does most the leg work if you have SAM, you should be able to modify it to what you need.  If you don't have SAM or don't need it for this case then I have a much simpler one that works nicely in NPM

--Solarwinds Credential Use
SELECT
n.caption as [Node]
,n.detailsurl as [_linkfor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
,n.objectsubtype as [Polling Method]
,case when n.objectsubtype in ('ICMP','WMI') then 'None'
else n.community
end as [SNMP Credential]
,'/Orion/Nodes/NodeProperties.aspx?Nodes=' + ToString(n.NodeID) as [_linkfor_SNMP Credential]
,'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_SNMP Credential]
,case when n.objectsubtype='WMI' then cn.Name
else 'None'
end as [WMI Credential]
,'/Orion/Nodes/NodeProperties.aspx?Nodes=' + ToString(n.NodeID) as [_linkfor_WMI Credential]
,'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_WMI Credential]
,case when a.name is null then 'None Assigned'
else a.name
end as [Application]
,a.detailsurl as [_linkfor_Application]
,'/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_Application]
,case when aset.value='-3' then 'Inherit From Node'
when aset.value is null and c.name is not null then 'Set by Component'
when a.name is null then 'None Assigned'
else ca.name end as [Application Credential]
,'/Orion/APM/Admin/Edit/EditApplication.aspx?id=' + tostring(a.applicationid) as [_linkfor_Application Credential]
,'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_Application Credential]
,case when c.name is null then 'None Assigned'
else c.name end as [Component]
,c.detailsurl as [_linkfor_Component]
,case when c.statusdescription = 'Not Running' then '/Orion/APM/Images/StatusIcons/Components/Small-not-running.gif'
when c.statusdescription = 'Critical' then '/Orion/APM/Images/StatusIcons/Components/Small-up-critical.gif'
when c.statusdescription ='Unplugged / Offline' then '/Orion/APM/Images/WebsiteUpgrade/button.close.gif'
else concat('/Orion/APM/Images/StatusIcons/Components/Small-',c.StatusDescription,'.gif')
end AS [_IconFor_Component]
,case when a.ApplicationTemplateID in (8,9,10) then 'Set by Application'
when cs.value='-3' then 'Inherit From Node'
when c.name is null then 'None Assigned'
else cc.name end as [Component Credential]
,'/Orion/APM/Admin/Edit/EditApplication.aspx?id=' + tostring(a.applicationid) as [_linkfor_Component Credential]
,'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_Component Credential]


from orion.nodes n
left join Orion.NodeSettings ns ON n.NodeID = ns.NodeID and settingname='WMICredential'
left join Orion.Credential cn ON ns.SettingValue = cn.ID   
left join orion.apm.application a on n.nodeid=a.nodeid
left join Orion.APM.ApplicationSettings as aset on aset.applicationid=a.applicationid and (aset.key='CredentialSetId' or aset.key='SqlCredentialSetId')
left join Orion.Credential ca ON aset.Value = ca.ID
left join orion.apm.component c on c.applicationid=a.applicationid
left join orion.apm.componentsetting cs on cs.componentid=c.componentid and cs.key='__CredentialSetId'
left join orion.apm.componenttemplatesetting cts on cts.componenttemplateid=c.templateid and cts.key='__CredentialSetId'
left join Orion.Credential cc ON cs.Value = cc.ID or cc.id=cts.value


--where (n.caption like '%${SEARCH_STRING}%' or
--n.community  like '%${SEARCH_STRING}%' or
--cn.Name like '%${SEARCH_STRING}%' or
--a.name like '%${SEARCH_STRING}%' or
--ca.name like '%${SEARCH_STRING}%' or
--c.name like '%${SEARCH_STRING}%' or
--cc.name like '%${SEARCH_STRING}%')

Or this simpler one just looking at nodes without the SAM info

SELECT n.Caption AS [Node Name] ,n.detailsurl as [_linkfor_Node Name], '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node Name],n.ip_address as [IP Address],n.detailsurl as [_linkfor_IP Address],c.Name AS [WMI Credential Name], n.Community as [SNMP Community],'Edit' AS [Edit], '/Orion/Nodes/NodeProperties.aspx?Nodes=' + ToString(n.NodeID) AS [_LinkFor_Edit],'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_Edit]   
 
 
FROM Orion.Nodes n   
JOIN Orion.NodeSettings ns ON n.NodeID = ns.NodeID   
LEFT JOIN Orion.Credential c ON ns.SettingValue = c.ID   
WHERE ns.SettingName = 'WMICredential' or ns.settingname like'Core.TopologyPollInterval'   
--and (n.caption like '%${SEARCH_STRING}%' or n.community like '%${SEARCH_STRING}%' or c.name like '%${SEARCH_STRING}%') 

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems