Hi All,
Thought I’d share something I built recently which I think is pretty useful.
We identified an issue where NCM was successfully pulling configs, but the account being used did not have sufficient permissions to execute CLI commands. From a SolarWinds perspective, everything appeared healthy. However, when reviewing the downloaded configs, they contained:
ACCESS-DENIED
So I got thinking, I created a Compliance report that looks particular vendor and only looks at latest 'Running' Config where 'ACCESS-DENIED' exists in the config. The compliance report works, it does what it's designed to do. However, I got thinking. why don't we explore how we can proactive rather than reactive when such an issue comes along, so with that in mind. I got thinking about how we can build this.
Attempt 1 SWQL: I initially tried using SWQL, but ran into platform limitations rather than syntax issues. The Key limitations I came across were, SWQL does not properly expose raw config blobs, it's designed for Orion Entities (Nodes, Interfaces, Volumes, etc) and it works best with structured / summarised data. Additionally, the alert engine has constraints when using SWQL, It cannot handle heavy test parsing, struggles with large dataset scans (e.g config achieves) and limited support for complex joins across NCM tables.
Final approach SQL: I moved to SQL which gave direct access to the required data. SQL allows: Full access to config blobs, joining NCM data with nodes and filtering based on timestamps. specifically the Key table used was dbo.NCM_ConfigArchive
So I came up with the following SQL Script to use within the Alerting Suite. By selecting 'Custom SQL Alert (Advanced) within the trigger condition.
SELECT
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.Vendor,
c.DownloadTime
FROM dbo.Nodes Nodes
JOIN dbo.NCM_Nodes nn
ON nn.CoreNodeID = Nodes.NodeID
JOIN dbo.NCM_ConfigArchive c
ON c.NodeID = nn.NodeID
WHERE
Nodes.Vendor LIKE 'Example%'
AND c.ConfigType = 'Running'
AND c.DownloadTime = (
SELECT MAX(c2.DownloadTime)
FROM dbo.NCM_ConfigArchive c2
WHERE c2.NodeID = c.NodeID
AND c2.ConfigType = 'Running'
)
AND c.Config LIKE '%ACCESS-DENIED%'
Performance considerations: While the query is effective, there are important considerations: dbo.NCM_ConfigArchive stores full device configs which often thousands of line per row and stored as large text (LOB Data) the Condition c.Config LIKE '%ACCESS-DENIED%' is expensive because SQL must scan the entire config, the leading wildcard (%) prevents index usage and the results in a full table scan of the config text, In simple terms, this turns the query into "Read every relevant config and search the entire text"
Optimisation in production: To reduce I/O load in our environment the alert is scheduled to run once daily and it runs after the compliance report completes. by doing this approach this reduces execution frequency, aligns the query with new config data is available and avoids repeatedly scanning unchanged configs. Which means we have the desired outcome This ensures alerts trigger when conditions are met, without placing unnecessary load on the SolarWinds platform or underlying database.
Happy to hear any feedback, suggestions, or critiques from the community
