DCM Reporting for SEP 11

Here is an example of how you can use a SQL PIVOT to see all of the Configuration Item information in a DCM Baseline on a single line for each computer.

It makes use of the Baseline and Configuration Items referenced in this article but the methodology could be applied to anything.

This is meant to be run via SQL Studio as-is. Just comment out the DECLARE/SELECT lines for @CollID and create prompts for it if you want to run it via SQL Reporting. The only downside to PIVOTs is that you have to hard-code some things into your SQL code. (I think you could do some complicated SQL to pull it off but this works for my purposes).

--DCM - SEP Compliance Overview (ver1)
 
SET NOCOUNT ON
DECLARE @CollID varchar(64)
SELECT @CollID = '0090001B' --009: Systems
 
DECLARE @DCMName varchar(128)
SELECT @DCMName = 'SEP Baseline'
 
--Get the CI_ID for the Baseline
DECLARE @DCM_CI_ID int;
  SELECT @DCM_CI_ID=CI.CI_ID FROM v_ConfigurationItems CI
    JOIN v_LocalizedCIProperties_SiteLoc LOCBASE ON LOCBASE.CI_ID = CI.CI_ID
    WHERE
      CI.CIType_ID = 2
      AND LOCBASE.DisplayName = @DCMName
 
--Select @DCM_CI_ID
 
--Get all of the Configuration Item CI_IDs that are assigned to the Baseline and put into a Table Variable
DECLARE @CI TABLE(CI_ID int PRIMARY KEY)
INSERT INTO @CI
  SELECT CIR.ToCIID
  FROM v_CIRelation CIR
  WHERE CIR.FromCIID = @DCM_CI_ID
 
SELECT
  Netbios_name0 AS Name
  , @DCMName AS DCMName
  , [SEP 11 - Components STATUS] --AS [Component Status]
  , [SEP 11 - Configuration Checks]
  , [SEP 11 - Pattern Files]
FROM
 
  (SELECT
    SYS.Netbios_name0
    --, @DCMName AS DCMName
    , LOCPROP.DisplayName
    , CICOMPLIANCE.ComplianceStateName
    --, CICOMPLIANCE.*
    --, CI.*
 
  FROM v_R_System SYS
    LEFT JOIN v_CICurrentComplianceStatus CICOMPLIANCE ON SYS.ResourceID=CICOMPLIANCE.ResourceID
    JOIN @CI CI ON CICOMPLIANCE.CI_ID = CI.CI_ID
    LEFT JOIN v_LocalizedCIProperties_SiteLoc LOCPROP ON CICOMPLIANCE.CI_ID=LOCPROP.CI_ID
  WHERE
    CICOMPLIANCE.ResourceID IN
      (SELECT DISTINCT ResourceID
      FROM v_ClientCollectionMembers
      WHERE    CollectionID = @CollID)
 
    AND CICOMPLIANCE.ComplianceStateName = 'Non-Compliant'
 
  ) NNN
PIVOT
  (
  Min(ComplianceStateName) FOR DisplayName IN ([SEP 11 - Components STATUS], [SEP 11 - Configuration Checks], [SEP 11 - Pattern Files])
  )
  AS PVT
 
ORDER BY Netbios_Name0
--ORDER BY CICOMPLIANCE.ComplianceStateName DESC, SYS.Netbios_Name0
 
 

• < Prev
• Next >