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

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

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

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.

zackm-- I extended you some kudos for your helpful replies above. 

I am wondering what version of Orion you are coding SQL on. I'm on 2019.4 -- our database does not have a dbo.SNMPV3Credentials table or view. I have found some tables with some information on our SNMPv3 credentials -- stored in other tables, but the information is incomplete. And, it certainly isn't as elegant at the dbo.SNMPV3Credentials seems to be from your example above.

Here's what I was able to find:

dbo.Credential

dbo.CredentialProperty

dbo.CredentialRelation

dbo.Ctx_C_Orion_SnmpCredentialV3Data_man_1

If I run the following query, it does correctly identify the SNMPv3 credential sets configured in our Orions system.

select * 
from dbo.Credential with(nolock) 
where credentialtype='SolarWinds.Orion.Core.Models.Credentials.SnmpCredentialsV3'

I join with NodesData and CredentialRelation, and get some good info, but for some reason, only the PAN Firewalls are in the results. This is strange since I know some other devices are on SNMPv3, and nothing in teh query limits to PAN.

SELECT   [n].[Caption]
	,[n].[IP_Address]
	,[c].[Name]
FROM [dbo].[NodesData][n]
JOIN [dbo].[CredentialRelation][r] ON [n].[NodeID] = [r].[EntityID]
JOIN [dbo].[Credential][c] ON [r].[CredentialID] = [c].[ID]

Your thoughts on why my DB doesn't have a dbo.SNMPV3Credentials table, or where I could find it?

Regards, Eric