NCM Poloicy Violations Report - SQL Reporting Services Version

Version 3

    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