I have inherited our Solarwinds installation and would like to cleanup the NCM Connection Profiles. Is there a way to create a report to show me what nodes are using what connection? If so, any tips or suggestions would be greatly appreciated.
Using SQL:
SELECT
n.Caption 'Device'
,n.IP_Address 'IP Address'
,c.Name 'Connection Profile'
,p.LoginStatus 'Login Status'
,p.DeviceTemplate 'Device Template'
FROM NCM_NodeProperties p
JOIN NCM_ConnectionProfiles c ON c.ID = p.ConnectionProfile
JOIN NodesData n ON n.NodeID = p.CoreNodeID
ORDER BY c.Name, n.Caption
-ZackM
Loop1 Systems: SolarWinds Training and Professional Services
ZackM,
Thanks so much for the help. Not only did you resolve my issue but I am learning a lot about how to code other issues from you work.
That being said I would really like to see how you would add the column ‘SNMPV3 Credentials’ to the report with the data from [SNMPV3Credentials].[CredentialName ]to this report.
That would finish my credential clean up and send me on to other housekeeping chores.
FYI, I have already request funding for the SQL class you guys offer at Loop1.
Thanks
As far as I can tell, there's not a clear connection between those tables. However, assuming that you have distinct user names for your different SNMPv3 profiles, something like this should work:
SELECT
n.Caption 'Device'
,n.IP_Address 'IP Address'
,c.Name 'Connection Profile'
,p.LoginStatus 'Login Status'
,p.DeviceTemplate 'Device Template'
,cred.CredentialName 'SNMPv3 Credential Set'
FROM NCM_NodeProperties p
JOIN NCM_ConnectionProfiles c ON c.ID = p.ConnectionProfile
JOIN NodesData n ON n.NodeID = p.CoreNodeID
JOIN SNMPV3Credentials cred ON cred.SNMPV3Username = n.SNMPV3Username
ORDER BY c.Name, n.Caption
Generally, we only want to join tables on distinct keys, not a string like UserName that could be used multiple times across different saved sets of credentials. But, we work with what we've got sometimes. 
Also, this query qill only return nodes being actively monitored with NCM. If you want to see all SNMPv3 nodes, including those not in NCM, we need to adjust...
Good stuff for the 301 class, I think you'll love it!
Based on your earlier sql script I was able to get there, however for ease of deployment using scripts on our Cisco gear we are using the same Read User and Write User and Read User Group and Write User Group with different encryption depending on the switch type. So this isn't working as I had hoped. Worse we are using the passwords for Priv and Auth for each saved credential.
What about creating a custom property or something similar.
I'm reaching any ideas are appreciated.
Thanks
If you create a custom property, it would be very simple to pull it into the report as such:
SELECT
n.Caption 'Device'
,n.IP_Address 'IP Address'
,c.Name 'Connection Profile'
,p.LoginStatus 'Login Status'
,p.DeviceTemplate 'Device Template'
--Change this next line to match whatever Custom Property name you end up with
,n.CustomPropertyName 'SNMPv3 Profile'
FROM NCM_NodeProperties p
JOIN NCM_ConnectionProfiles c ON c.ID = p.ConnectionProfile
JOIN NodesData n ON n.NodeID = p.CoreNodeID
ORDER BY c.Name, n.Caption
The problem is really in tying in the profile name from the SNMPv3 table to the NodesData table, there is not a way (that I can see) to join those two tables to each other outside of the UserName.
This document was generated from the following discussion: Is it possible to create a report to show what Nodes in NCM use what connection profile? If so, any tips
