3 Replies Latest reply on Jul 26, 2017 2:12 PM by mesverrum

    WMI Creds being Used ?

    nick_scott

      Can anyone tell me if there is a way to find the credentials being used (WMI) via report writer ?  I was unable to find this value via report GUI.  

       

      Does anyone know what table holds this info ?  I can do some simple joins if I knew where it was!

        • Re: WMI Creds being Used ?
          zackm

          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
          1 of 1 people found this helpful
          • Re: WMI Creds being Used ?
            mesverrum

            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