This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

NCM Poloicy Violations Report - SQL Reporting Services Version

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.

pastedImage_0.png

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

pastedImage_15.png

attachments.zip