Reporting On Credentials in use within SolarWinds NPM and SAM

For SolarWinds® Orion® to discover and monitor your network, you will need to supply credentials (SNMP/WMI) to authenticate and gain access to the required data such as, inventory, availability and performance metrics, even run scripts on servers if you have SAM.

It is best practice to create unique “service accounts” to be used by SolarWinds Orion for all this data collection activity. In reality, numerous account configurations are defined and assigned to various monitored nodes and applications, especially during testing/troubleshooting a troublesome configuration. Tracking those account configurations and assignments, is kinda tricky in the console.

Using Orion Reports, we can create a report to list the assigned credentials, for those nodes and applications, along with any beneficial corresponding information such as custom properties, machine type etc.

SNMP and WMI Credentials Assignment report on OrionServer

Start by creating a new report and selecting a ‘custom table’ content.

First step is to define the data source, so click on ‘Advanced Selector’ and then we need to select what object our data resides on, in this case SNMP data is associated with the Node object, so set drop down the ‘Node’. We will then add a filter, so we only get devices populate that have an SNMP community value, so set the filter to ‘Community’ is not ‘empty’.

Provide a ‘Selection Name:’ and click ‘ADD TO LAYOUT’

Now, we need to configure the display output, so here you can choose the data columns you wish to display. We have chosen to display, the device name ‘caption’, IP Address, Community name (this is after all the purpose of the report!) and the geographic location of the device ‘site’. You may well have other data columns you wish to add, so do so here.

Once added, you can use the ‘Advanced’ link to change the formatting options for that data column, then give the table a title, adjust column presentation and, sort and group results as needed. Click Submit.

This first table provides list of nodes and assigned community. We can easily add an additional data structure for reporting on SNMPv3 assignments in the same report.

Click ‘Add Content’ and select a ‘custom table’ content.

Select ‘Create new object selection’ and for ‘Selection method’, choose ‘Advanced Database Query (SQL, SWQL)’. For ‘Query Type’ select ‘SWQL’. Insert the following SWQL query that will list SNMPv3 community assignment.

[code]SELECT n.caption AS [Node],
n.detailsurl AS [_LinkFor_Node Name],
n.ip_address AS [IP Address],
n.customproperties.site AS [Site],
n.objectsubtype AS [Polling Method],
c.NAME AS [SNMPv3 Credential]
FROM orion.nodes n
LEFT JOIN orion.nodesettings ns
ON n.nodeid = ns.nodeid
AND settingname LIKE ‘%Credential%’
LEFT JOIN orion.credential c
ON ns.settingvalue = c.id
WHERE objectsubtype = ‘SNMP’
AND c.NAME IS NOT NULL
[/code]

Remember, only the data fields included in the SQL query are available for display in the data columns of the report, so be prepared to adjust the SQL query if you wish to add further columns. As before, provide a title and adjust column presentation and, sort and group results as needed. Click Submit.

Click ‘Add Content’ and repeat the same for the following SWQL queries:
We are now going to repeat this to display the list of WMI credentials assignment for node polling.

[code]SELECT n.caption AS [Node],
n.detailsurl AS [_LinkFor_Node Name],
n.ip_address AS [IP Address],
n.customproperties.site AS [Site],
cre.NAME AS [WMI Credential]
FROM orion.nodes n
INNER JOIN orion.nodesettings nse
ON nse.nodeid = n.nodeid
INNER JOIN orion.credential cre
ON cre.id = nse.settingvalue
WHERE NSE.settingname = ‘WMICredential’
AND n.objectsubtype = ‘wmi’
[/code]

One last table to add and that is the credential assignments for application polling in SAM.

[code]SELECT n.nodeid,
n.caption AS Node,
n.detailsurl AS [_LinkFor_Node Name],
a.id AS ApplicationID,
a.NAME AS [Application],
a.detailsurl AS [_LinkFor_Application Name],
c.id AS ComponentId,
c.NAME AS Component,
c.detailsurl AS [_LinkFor_Component Name],
cts.value AS TemplateCredId,
cs.value AS [OverridenCredId],
cred.NAME AS Credential
FROM orion.apm.component c
INNER JOIN orion.apm.application a
ON c.applicationid = a.id
INNER JOIN orion.nodes n
ON a.nodeid = n.nodeid
LEFT JOIN orion.apm.componentsetting cs
ON cs.componentid = c.componentid
AND cs.[key] = ‘__CredentialSetId’
LEFT JOIN orion.apm.componenttemplate ct
ON ct.id = c.templateid
LEFT JOIN orion.apm.componenttemplatesetting cts
ON cts.componenttemplateid = ct.id
AND cts.[key] = ‘__CredentialSetId’
LEFT JOIN orion.credential cred
ON cred.id = Isnull(cs.value, cts.value)
WHERE Cred.NAME IS NOT NULL
[/code]

Once done, provide a report title and adjust remaining report configuration as needed then Submit.

Note: you can use ‘Report Limitation’ to restrict who can access this report in Orion.

This report provides centralised visibility into configured credentials and, node and application component assignment. This is useful to remove credential configuration that is not a designated “service accounts” and, by adding extra information in the form of custom properties, you can have additional context to the assignments.

These SWQL queries can be tuned to be used in alerts, dashboards and drive automation.