NCM Poloicy Violations Report - SQL Reporting Services Version
To get an more appropriate report that can be emailed around to team, or viewed live (easily without scrolling through hundreds of pages).
This repoprt and attached files will link to a Policy Report called 'DCC Base Config Updates'. Once you have your policy report setup, then just replace the name in this report.
I have two reports that are emabedded into a summary report.
Report1 - NCM Base Config Violations By Rule.rdl
SELECT |
TOP (100) PERCENT dbo.Nodes.NodeCaption, p.ReportName, p.RuleName,
SUM(CASE errorLevel WHEN 0 THEN 1 ELSE 0 END) AS Info,
SUM(CASE errorLevel WHEN 1 THEN 1 ELSE 0 END) AS Warnings,
SUM(CASE errorLevel WHEN 2 THEN 1 ELSE 0 END) AS Critical
FROM |
dbo.PolicyCacheResults AS p INNER JOIN
dbo.ConfigArchive ON p.ConfigID = dbo.ConfigArchive.ConfigID INNER JOIN
dbo.Nodes ON dbo.ConfigArchive.NodeID = dbo.Nodes.NodeID
WHERE p.IsViolation = 1 AND (p.ReportName = 'DCC Base Config Updates')
GROUP BY p.ReportName, p.RuleName, p.ReportID, dbo.Nodes.NodeCaption
ORDER BY p.RuleName
.rdl file attached. This is using expandable list so that you get a nice report showing the totals per level/warning.
Report 2 - NCM Base Config Violations Detail.rdl
Almost identical - this just gives you a report by Node, which then expands to show you all the rule Violations. You can view this on the NCM Node view.
SELECT |
TOP (100) PERCENT dbo.Nodes.NodeCaption, p.ReportName, p.RuleName,
SUM(CASE errorLevel WHEN 0 THEN 1 ELSE 0 END) AS Info,
SUM(CASE errorLevel WHEN 1 THEN 1 ELSE 0 END) AS Warnings, | |
SUM(CASE errorLevel WHEN 2 THEN 1 ELSE 0 END) AS Critical | |
FROM |
dbo.PolicyCacheResults AS p INNER JOIN
dbo.ConfigArchive ON p.ConfigID = dbo.ConfigArchive.ConfigID INNER JOIN | |
dbo.Nodes ON dbo.ConfigArchive.NodeID = dbo.Nodes.NodeID | |
WHERE | (p.IsViolation = 1) AND (p.ReportName = 'DCC Base Config Updates') |
GROUP BY p.ReportName, p.RuleName, p.ReportID, dbo.Nodes.NodeCaption
ORDER BY dbo.Nodes.NodeCaption