nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

How to query SNMPv3 Credential Information for Nodes

jdbrp

Hello,

I'm wondering if someone could help me figure out a way to find out which nodes are using which snmpv3 credential names under "Manage SNMPv3 Credentials". I'm trying to find some nodes that are the last devices using a legacy snmpv3 credential where the snmpv3 user name is the same but the auth and priv are still SHA1 and AES128.

I've tried a few queries I've found here but none of them work. I'm using Database Manager but also have access to SQL Studio. I see a table called SWCredentials with SWCredentials_SnmpV3, but I think I need to do a join from another query but I don't know how to do that.

Thanks for any help if possible.

Find more posts tagged with

snmpv3

Accepted answers

cnorborg

No, you have to use either SWQL Studio, or put it in a resource. I personally have something similar in a resource in one of my pages. Easier than loading SWQL Studio. Edit a page, go to page settings, add a resource to one of the columns of the type "Custom Query". Then preview the page and make the title you want and put this query in the box.

SELECT n.Caption AS [Node Name] ,n.detailsurl as [_linkfor_Node Name], '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node Name],n.ip_address as [IP Address],n.detailsurl as [_linkfor_IP Address],c.Name AS [WMI/SNMPv3 Credential Name], n.Vendor, n.Community as [SNMPv2 Community],'Edit' AS [Edit], '/Orion/Nodes/NodeProperties.aspx?Nodes=' + ToString(n.NodeID) AS [_LinkFor_Edit],'/Orion/images/nodemgmt_art/icons/icon_edit.gif' as [_IconFor_Edit]

FROM Orion.Nodes nleft JOIN Orion.NodeSettings ns ON n.NodeID = ns.NodeID and ns.SettingName like '%Credential%'LEFT JOIN Orion.Credential c ON ns.SettingValue = c.ID

where (n.ObjectSubType!= 'ICMP') AND (c.Name IS NOT NULL)

Now, here is a screenshot of it. Notice that I have a slightly modified query in the second "Search SWQL Query" box to allow me to search the list.

jdbrp

Scratch all the above. It worked!

I just started over and added each line until it failed. I then removed that line ( it was c.Name AS [WMI/SNMPv3 Credential Name] ) and finished the rest of the query and all was good. I then attempted to re-insert the problem line again but this time it worked. Must have been a typo on my part.

Thanks so much for the assistance!!!

All comments

naveensingh43

We have an out-of-the-box report in SolarWinds (Community Strings for each node), but it only displays the SNMPv2c community string.

Lofstrand

Hi @jdbrp

You could start with below SWQL query. (Not SQL, but SWQL)

You can put the query in a report or use SWQL studio. Shows you where you are using saved credentials, not individual. But with the info in the query it can be adjusted to show each nodes settings. Passwords and such are not visible.

SELECT distinct 

    C.Name AS CredentialName

    ,OSV3.Username

    ,OSV3.PrivacyMethod

    ,OSV3.PrivacyKey

    ,OSV3.PrivacyKeyIsPassword

    ,OSV3.AuthenticationMethod

    ,OSV3.AuthenticationKey

    ,OSV3.AuthenticationKeyIsPassword

    ,OSV3.RWUsername

    ,OSV3.RWContext

    ,OSV3.RWPrivacyMethod

    ,OSV3.RWPrivacyKey

    ,OSV3.RWPrivacyKeyIsPassword

    ,OSV3.RWAuthenticationMethod

    ,OSV3.RWAuthenticationKey

    ,OSV3.RWAuthenticationKeyIsPassword

FROM Orion.SNMPv3Credentials AS OSV3

INNER JOIN ORION.Nodes AS ONO ON OSV3.Nodeid = ONO.NodeID

INNER JOIN Orion.NodeSettings AS NS ON ONO.nodeid=NS.NodeID AND NS.SettingName='ROSNMPCredentialID'

INNER JOIN Orion.Credential AS C ON NS.SettingValue=C.ID

jdbrp

Will this query work in Database Manager? Do I have to replace any entries above with something unique to my environment?

I had to replace line 18 with FROM SWCredentials_SnmpV3 AS OSV3 as I was getting Invalid object name 'Orion.SNMPv3Credentials".

After that my next query got to line 19 but it gave me an Invalid column name 'NodeID'.

Msg 207, Level 16, State 1, Line 19
Invalid column name 'NodeID'.

I know NodeID is correct as if I query the actual table NodeID is the first column it shows.

Thanks so much for the help so far!

James

cnorborg

FROM Orion.Nodes nleft JOIN Orion.NodeSettings ns ON n.NodeID = ns.NodeID and ns.SettingName like '%Credential%'LEFT JOIN Orion.Credential c ON ns.SettingValue = c.ID

where (n.ObjectSubType!= 'ICMP') AND (c.Name IS NOT NULL)

Now, here is a screenshot of it. Notice that I have a slightly modified query in the second "Search SWQL Query" box to allow me to search the list.

jdbrp

I just get "There was an error processing the request". If I look at Database Manager (which I understand is not the same as Studio) and expand the tables, I see that I have Nodes (View) and also NodesData but not just nodes. Not sure if this changed after moving to On Prem late last year but this database has been going since 2017 so not sure If I need to change something in the FROM Orion.Nodes in the query.

When I do a basic SELECT TOP 1000 * FROM in database manager to look at a table, I see the query as FORM [dbo].[NodesData]. I'm guessing SQL Studio or the Custom Query handle things differently so just throwing things out there. I did try to change the FROM with the other names but no luck.

I did manage to make a small query work using FROM Orion.Nodes n so I will continue to work that angle.

jdbrp

Scratch all the above. It worked!

Thanks so much for the assistance!!!

jdbrp

And I thanks everyone who replied. Its greatly appreciated!

cgooden01

Try this and let me if it works for you

SELECT
N.NodeID,
N.Caption,
N.IPAddress,
N.SNMPVersion,
CASE
WHEN NC.CredentialID IS NOT NULL THEN 'Has SNMPv3 Credential'
ELSE 'No SNMPv3 Credential'
END AS SNMPv3Status
FROM
Orion.Nodes N
LEFT JOIN Orion.NodeCredentials NC
ON N.NodeID = NC.NodeID
AND NC.CredentialID IN (
SELECT ID FROM Orion.Credential
WHERE CredentialType = 'SNMPv3'
)
ORDER BY
SNMPv3Status, N.Caption

cgooden01

Credential Coverage Report (Nodes with/without SNMPv3)
SELECT
N.NodeID,
N.Caption,
N.IPAddress,
N.SNMPVersion,
CASE
WHEN NC.CredentialID IS NOT NULL THEN 'Has SNMPv3 Credential'
ELSE 'No SNMPv3 Credential'
END AS SNMPv3Status
FROM
Orion.Nodes N
LEFT JOIN Orion.NodeCredentials NC
ON N.NodeID = NC.NodeID
AND NC.CredentialID IN (
SELECT ID FROM Orion.Credential
WHERE CredentialType = 'SNMPv3'
)
ORDER BY
SNMPv3Status, N.Caption

jdbrp

I couldn't get it to work. I tried the same procedure as before in that I removed part of the query until it didn't give me an error.

I did this to get the Custom Query to show some data with no errors using the below.

SELECT
N.NodeID,
N.Caption,
N.IPAddress,
N.SNMPVersion

FROM
Orion.Nodes N

When I add the other sections I can't get it to work.

Thanks,

James