cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Is it possible to create a report to show what Nodes in NCM use what connection profile? If so, any tips

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 ...

Comments

Hi,

I was looking to do a report to show the connection profile is SSH or Telnet but the SQL above doesn't work for me as SQL in the WEB GUI Report designer. I can get the Connection Profile number but need to display the name - SSH, Telnet, etc.

It looks like others have managed to get the SQL working is there anything customized that wouldn't be in the SQL database.

Z.

Version history
Revision #:
1 of 1
Last update:
‎02-13-2020 09:32 AM
Updated by: