SolarWinds THWACK
THWACK
  • Sign In
  • Search
  • Product Forums
    • Observability
      • SolarWinds Observability
      • Hybrid Cloud Observability
      • DevOps
    • The Orion Platform
      • Forum
      • Scalability Engines (HA, APE, AWS)
      • Enterprise Operations Console (EOC)
      • Orion SDK
      • Alert Lab
      • Report Lab
    • Network Management
      • Network Performance Monitor (NPM)
      • NetFlow Traffic Analyzer (NTA)
      • Network Configuration Manager (NCM)
      • IP Address Manager (IPAM)
      • User Device Tracker (UDT)
      • VoIP & Network Quality Manager (VNQM)
      • Log Analyzer
      • Engineer’s Toolset
      • Network Topology Mapper (NTM)
      • Kiwi CatTools
      • Kiwi Syslog Server
      • ipMonitor
    • Systems Management
      • Server & Application Monitor (SAM)
      • Virtualization Manager (VMAN)
      • Storage Resource Monitor (SRM)
      • Server Configuration Monitor (SCM)
      • SolarWinds Backup
      • Web Performance Monitor (WPM)
    • Database Management
      • Database Performance Analyzer (DPA)
      • SQL Sentry
      • Database Performance Monitor (DPM)
      • Database Mapper
      • Task Factory
    • Application Management
      • AppOptics
      • Loggly
      • Papertrail
      • Pingdom
      • DevOps
    • IT Security
      • Access Rights Manager (ARM)
      • Identity Monitor
      • Security Event Manager (SEM)
      • Patch Manager
      • Serv-U FTP & MFT
    • IT Service Management
      • SolarWinds Service Desk (SWSD)
      • Web Help Desk (WHD)
      • DameWare Remote Support (DRS)
      • DameWare Remote Everywhere (DRE)
      • DameWare Mini Remote Control (MRC)
  • Resources
    • THWACK Command Center
    • DevOps
    • What We're Working On
    • Blogs
      • Community Announcements
      • Product Blog
      • Monitoring Central
      • Geek Speak
      • The DevOps Blog
    • THWACK Tech Tips
    • TechPod
    • Support
      • Success Center
      • Documentation
      • Submit a Support Ticket
      • Customer Portal
      • Renew Maintenance
    • Community Groups
      • New To THWACK
      • Federal & Government
      • User Experience
      • EMEA Group
      • Japan Group
  • Events, Missions, & Musings
    • Events
      • THWACK Livecast
      • THWACK Livecast Archive
      • SolarWinds Lab
      • SolarWinds Lab Archive
      • THWACKcamp 2022 On Demand
      • SolarWinds User Groups
      • SolarWinds Events Calendar
    • Missions & Contests
      • Monthly Mission: What Killed Your Productivity?
      • THWACK 101
    • Musings
      • Water Cooler
      • Geek Tank
      • IT Tech Jobs/Careers
      • Monitoring for Managers
    •  
      •  
  • Content Exchange
    • The Orion Platform
      • Alerts
      • Custom HTML
      • Custom Queries
      • Modern Dashboards
      • Reports
      • Scripts
    • Network Performance Monitor
      • Device Pollers
      • Universal Device Pollers (UnDP)
    • Network Configuration Manager
      • Config Change Scripts
      • Device Templates
      • Firmware Upgrade Templates
      • Policy Documents
    • Server & Application Monitor
      • API Pollers
      • Application Monitor Templates
    • Server Configuration Monitor
      • Policies
      • Profiles
    • Database Performance Analyzer
      • Custom Alerts
      • Custom Metrics
      • Custom Queries
    • Web Help Desk
      • Style Sheets
  • Academy
    • Newsroom
    • Forums
      • Classroom Discussions
      • SolarWinds Certified Professional (SCP)
    • Training & Certification
  • Free Tools & Trials
  • Store
The Orion Platform
  • Content Exchange
  • More
The Orion Platform
Custom Queries Node assigned credentials SWQL query
  • Tags
  • More
  • Cancel
Options
  • View all
  • Previous
  • Next
  • View slideshow
  • More
  • Cancel
fileName

Node assigned credentials SWQL query

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 != '' ) )

  • credential
  • node
  • credentials
sum_giais
sum_giais
  • 17 Feb 2022
  • 18 Downloads
  • Share
  • More
  • Cancel
Anonymous

Top Comments

  • chrisorourke
    chrisorourke over 1 year ago +1

    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…

  • sum_giais
    sum_giais over 1 year ago in reply to chrisorourke

    Glad to hear it was helpful for you! Slight smile

    • Cancel
    • Up 0 Down
    • Reply
    • More
    • Cancel
  • chrisorourke
    chrisorourke over 1 year ago

    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 nodes to find the last few I hadn't flipped to SNMP v3 and swapped to new CLI credentials left to get.

    • Cancel
    • Up +1 Down
    • Reply
    • More
    • Cancel

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 190,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification SolarWinds Lab Link Accounts
About THWACK Blogs Federal & Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2021 SolarWinds Worldwide, LLC. All Rights Reserved.