Search Config And Create Scheduled NCM Reports

billcjr

I am trying to make a scheduled report for a customer where they want to know any device whose config contains permit-all. There didn't seem to be any pre-built search for this so I wrote a query:

SELECT
     nd.Caption
     ,nd.IP_Address
FROM
     NCM_ConfigArchive AS ca LEFT JOIN
     NCM_NodeProperties AS np ON ca.NodeID = np.NodeID LEFT JOIN
     NodesData AS nd ON np.CoreNodeID = nd.NodeID
WHERE
     ca.DownloadTime >= DATEADD(DD,-1,GETDATE())
     AND ca.Config LIKE '%permit-all%'

• This query works fine in SQL but it doesn't work when I try to make a report with it.
• If I narrow it down to just do the select for the NodesData table it works fine.
• If I narrow it down to just select 1 field from the NCM_NodeProperties table, SolarWinds tells me that the query is not valid.

I have created web based reports using SQL in the past and they work fine, so it makes me wonder if NCM web based reports in the SolarWinds Reports are allowed. But I have to guess this is just me doing something wrong.

I do not necessarily require a fix for the SQL reporting if there is a pre-existing report that can produce this report that I am just not seeing.

I am running the 2023.4 version. Thanks for any help provided.

silverbacksays

Have you tried using the policy reports in NCM? If you setup a rule which searches for the permit all command, and then link this rule to a policy, you can create a report which will show which devices are currently breaching the policy.