A Connection Profile is a set of connection Credentials, Protocols and Port numbers, used to access the device, execute scripts, request configs and transfer configs. You can save the Connection Profile for use on multiple devices. If your Solarwinds campus has Network Configuration Manager (NCM), and you go to Manage Nodes > Node Edit Properties, and then scroll toward the bottom of the screen, you will find the Connection Profile section. You can also edit Connection Profiles via Settings > All Settings > NCM Settings > Connection Profiles.
Our Solarwinds campus is over 10 years old, and various Connection Profiles have been created and used in years past, some which include insecure connection methods, such as Telnet or TFTP. I have been tasked with identifying and remedying devices using insecure connections methods for NIST and other compliance purposes. I created a report looking at the fields regarding connection methods in the NCM_Nodes table, and started working on the list. After a while, I noticed that when I updated some nodes, the report didn't update. After doing some research, I found out that the Node Edit Properties screen pulls from different places in the Orion database. If the value in NCM_Nodes.ConnectionProfile = -1, then no Connection Profile is in use on that node, so the data in the connection methods fields NCM_Nodes table are what you see in Node Edit Properties. However, if the node is using a saved Connection Profile, then the NCM_Nodes.ConnectionProfile field has a positive number which corresponds to the saved Connection Profile in use for that node. In that case, the fields pertaining to Connection Profile in NCM_Nodes is ignored, and the same data is pulled from the NCM_ConnectionProfiles table instead.
So, when I had a device which previously used Telnet and TFTP to connect, and I updated the device to use SSH2 by using a Connection Profile named SSH-Admin, then my previous report didn't update showing that I have made an improvement. This was because my previous report was pulling data from a field that wasn't being used since the node used a Connection Profile. The solution was to create 2 SQL queries: one pulling from NCM_Nodes when NCM_Nodes.ConnectionProfile = -1; and one pulling from NCM_ConnectionProfiles NCM_Nodes.ConnectionProfile > -1, and then join the two queries together using a Union statement.
With this revised table, I can see each node and what the Connection Profile is (if any), and what the connection/execution/transfer methods are, plus a count of those. This report could easily be modified to list credentials instead of connection methods. Also, note that I have a clause limiting this to Cisco devices. You may change or eliminate that clause as you wish. Enjoy!
Table 1 showing Connection Methods as per each node.
SELECT *FROM ( SELECT [n].[CoreNodeID] , [Device Name] = [n].[NodeCaption] , [Polling IP Address] = [n].[AgentIP] , [n].[Vendor] , [Model] = [n].[MachineType] , [Execute Scripts Using] = [cp].[ExecuteScriptProtocol] , [Request Configs Using] = [cp].[RequestConfigProtocol] , [Transfer Configs Using] = [cp].[TransferConfigProtocol] , [Global Connection Profile] = [cp].[Name] , [n].[OSVersion] , [n].[OSImage] , [cp#] = [n].[ConnectionProfile] FROM [NCM_Nodes] [n] WITH (NOLOCK) JOIN [NCM_ConnectionProfiles] [cp] WITH (NOLOCK) ON ([n].[ConnectionProfile] = [cp].[ID]) WHERE ( ([n].[ConnectionProfile] > -1) AND ([n].[Vendor] LIKE '%Cisco%') ) UNION --ALL SELECT [n].[CoreNodeID] , [Device Name] = [n].[NodeCaption] , [Polling IP Address] = [n].[AgentIP] , [n].[Vendor] , [Model] = [n].[MachineType] , [Execute Scripts Using] = [n].[ExecProtocol] , [Request Configs Using] = [n].[CommandProtocol] , [Transfer Configs Using] = [n].[TransferProtocol] , [Global Connection Profile] = '<No Profile>' , [n].[OSVersion] , [n].[OSImage] , [cp#] = [n].[ConnectionProfile] FROM [NCM_Nodes] [n] WITH (NOLOCK) WHERE ( ([n].[ConnectionProfile] = -1) AND ([n].[Vendor] LIKE '%Cisco%') ) ) [X]ORDER BY [X].[Transfer Configs Using] DESC, [X].[Execute Scripts Using] DESC, [X].[Global Connection Profile] ASC
Table 2 showing counts of Connection Methods, i.e. how many nodes per Connection Method, for Transfer Protocol only -- you may change to Execute Script or Request Config Protocols.
SELECT [X].[Transfer Configs Using] , [Number of Nodes] = COUNT ([X].[Transfer Configs Using])FROM ( SELECT [Transfer Configs Using] = [cp].[TransferConfigProtocol] , [Global Connection Profile] = [cp].[Name] , [Polling IP Address] = [n].[AgentIP] , [cp#] = [n].[ConnectionProfile] FROM [NCM_Nodes] [n] WITH (NOLOCK) JOIN [NCM_ConnectionProfiles] [cp] WITH (NOLOCK) ON ([n].[ConnectionProfile] = [cp].[ID]) WHERE ( ([n].[ConnectionProfile] > -1) AND ([n].[Vendor] LIKE '%Cisco%') ) UNION --ALL SELECT [Transfer Configs Using] = [n].[TransferProtocol] , [Global Connection Profile] = '<No Profile>' , [Polling IP Address] = [n].[AgentIP] , [cp#] = [n].[ConnectionProfile] FROM [NCM_Nodes] [n] WITH (NOLOCK) WHERE ( ([n].[ConnectionProfile] = -1) AND ([n].[Vendor] LIKE '%Cisco%') ) ) [X] GROUP BY [X].[Transfer Configs Using] ORDER BY [Number of Nodes] ASC
ps. I thought I posted something about this a week or two ago, but I couldn't find it. My apologies if this is duplicate.
