Update 04/20/2022... re-wrote the SQL query into a SWQL query, the file to download is the SWQL query but I'll keep both versions available here for reference.
SWQL:
SELECT n.NodeID ,n.Caption AS [DisplayName] ,n.SysName AS [Hostname] ,n.IP_Address AS [IP Address] ,n.ObjectSubType AS [Monitoring Method] ,n.Community AS [Snmpv1/2c-RO] ,n.RWCommunity AS [Snmpv1/2c-RW] ,c1.Name AS [SNMPv3-RO] ,c2.Name AS [SNMPv3-RW] ,c3.Name AS [WMI-Cred] FROM Orion.Nodes AS n LEFT JOIN Orion.NodeSettings AS ns1 ON ns1.NodeID = n.NodeID AND ns1.SettingName = 'ROSNMPCredentialID' LEFT JOIN Orion.NodeSettings AS ns2 ON ns2.NodeID = n.NodeID AND ns2.SettingName = 'RWSNMPCredentialID' LEFT JOIN Orion.NodeSettings AS ns3 ON ns3.NodeID = n.NodeID AND ns3.SettingName = 'WMICredential' LEFT JOIN Orion.Credential AS c1 ON c1.ID = ns1.SettingValue LEFT JOIN Orion.Credential AS c2 ON c2.ID = ns2.SettingValue LEFT JOIN Orion.Credential AS c3 ON c3.ID = ns3.SettingValue WHERE ( ( n.Community IS NOT NULL AND n.Community != '' ) OR ( n.RWCommunity IS NOT NULL AND n.RWCommunity != '' ) OR ( c1.Name IS NOT NULL AND c1.Name != '' ) OR ( c2.Name IS NOT NULL AND c2.Name != '' ) OR ( c3.Name IS NOT NULL AND c3.Name != '' ) )
SQL:
SELECT n.NodeID ,n.Caption AS [DisplayName] ,n.SysName AS [Hostname] ,n.IP_Address AS [IP Address] ,n.ObjectSubType AS [Monitoring Method] ,n.Community AS [Snmpv1/2c-RO] ,n.RWCommunity AS [Snmpv1/2c-RW] ,c1.Name AS [SNMPv3-RO] ,c2.Name AS [SNMPv3-RW] ,c3.Name AS [WMI-Cred] FROM SolarWindsOrion.dbo.NodesData AS n INNER JOIN SolarWindsOrion.dbo.NodesCustomProperties AS cp ON cp.NodeID = n.NodeID LEFT JOIN SolarWindsOrion.dbo.NodeSettings AS ns1 ON ns1.NodeID = n.NodeID AND ns1.SettingName = 'ROSNMPCredentialID' LEFT JOIN SolarWindsOrion.dbo.NodeSettings AS ns2 ON ns2.NodeID = n.NodeID AND ns2.SettingName = 'RWSNMPCredentialID' LEFT JOIN SolarWindsOrion.dbo.Credential AS c1 ON c1.ID = ns1.SettingValue LEFT JOIN SolarWindsOrion.dbo.Credential AS c2 ON c2.ID = ns2.SettingValue LEFT JOIN SolarWindsOrion.dbo.NodeSettings AS ns3 ON ns3.NodeID = n.NodeID AND ns3.SettingName = 'WMICredential' LEFT JOIN SolarWindsOrion.dbo.Credential AS c3 ON c3.ID = ns3.SettingValue WHERE ( ( n.Community IS NOT NULL AND n.Community != '' ) OR ( n.RWCommunity IS NOT NULL AND n.RWCommunity != '' ) OR ( c1.Name IS NOT NULL AND c1.Name != '' ) OR ( c2.Name IS NOT NULL AND c2.Name != '' ) OR ( c3.Name IS NOT NULL AND c3.Name != '' ) )
Top Comments
That is hands down my favorite bit of sql and swql I've read, exclaimed YES!, and immediately put into a widget in almost a year. This saved me having to work the weekend sifting through several thousand…