SQL Reporting Services SCCM DCM

This article is dedicated to providing working examples for DCM (Desired Configuration Managment) reports, using reporting services 2008.


1. This report displays three statenames for the baseline called BL - WK DCM.  A count of Conpliance state populates the third column.

SELECT     CIProp.DisplayName AS 'DCM Baseline Name', SNames.StateName, COUNT(CCS.ComplianceState) AS 'Compliance State Count'
FROM         dbo.v_BaselineTargetedComputers AS BTC INNER JOIN
                      dbo.v_ConfigurationItems AS CI ON CI.CI_ID = BTC.CI_ID INNER JOIN
                      dbo.v_CICurrentComplianceStatus AS CCS ON CCS.CI_ID = CI.CI_ID AND CCS.ResourceID = BTC.ResourceID INNER JOIN
                      dbo.v_LocalizedCIProperties_SiteLoc AS CIProp ON CIProp.CI_ID = CI.CI_ID INNER JOIN
                      dbo.v_StateNames AS SNames ON CCS.ComplianceState = SNames.StateID
WHERE     (CIProp.DisplayName = 'BL - WK DCM') AND (SNames.TopicType = 401)
GROUP BY CIProp.DisplayName, CCS.ComplianceState, SNames.StateName

 

 

 

 

2. This report displays Computer name with corresponding last logged on username, for machines with non compliant status for specific baseline.
SELECT     TOP (100) PERCENT SYS.Name0 AS 'Computer Name', SNames.StateName AS 'Compliance State',
                      CCS.LastComplianceMessageTime AS 'Last Compliance Evaluation', SYS.User_Name0 AS 'User Name', OS.Caption0 AS 'Operating System',
                      OS.InstallDate0 AS 'Install Date', STATUS.LastHWScan AS 'Last HW Scan', COMP.Model0 AS 'Model', CIProp.DisplayName,
                      CI.CIVersion AS 'Baseline Content Version'
FROM         dbo.v_BaselineTargetedComputers AS BTC INNER JOIN
                      dbo.v_R_System AS SYS ON SYS.ResourceID = BTC.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM AS COMP ON COMP.ResourceID = BTC.ResourceID INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = SYS.ResourceID INNER JOIN
                      dbo.v_ConfigurationItems AS CI ON CI.CI_ID = BTC.CI_ID INNER JOIN
                      dbo.v_CICurrentComplianceStatus AS CCS ON CCS.CI_ID = CI.CI_ID AND CCS.ResourceID = BTC.ResourceID INNER JOIN
                      dbo.v_LocalizedCIProperties_SiteLoc AS CIProp ON CIProp.CI_ID = CI.CI_ID INNER JOIN
                      dbo.v_StateNames AS SNames ON CCS.ComplianceState = SNames.StateID LEFT OUTER JOIN
                      dbo.v_GS_WORKSTATION_STATUS AS STATUS ON STATUS.ResourceID = SYS.ResourceID LEFT OUTER JOIN
                      dbo.v_R_User AS USR ON USR.User_Name0 = SYS.User_Name0
WHERE     (CIProp.DisplayName = 'BL - WK DCM') AND (SNames.TopicType = 401) AND (SNames.StateName <> 'Compliant')
ORDER BY 'Compliance State'

NOTE: Change WHERE for compliant machines
WHERE     (CIProp.DisplayName = 'BL -WK DCM') AND (SNames.TopicType = 401) AND (SNames.StateName <> 'Non-Compliant') AND
                      (SNames.StateName <> 'error')

This

SELECT DISTINCT
                      TOP (100) PERCENT SYS.Name0, ccs.LastComplianceMessageTime, ccs.ComplianceStateName, dbo.v_LocalizedCIProperties_SiteLoc.DisplayName,
                      SYS.User_Name0,
                      CASE WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0 LIKE '%Europe%' THEN 'EMEA' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0
                       LIKE '%Asia Pacific%' THEN 'APAC' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0 LIKE '%Brazil%' THEN 'LAC' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0
                       LIKE '%Hispanic LAC%' THEN 'LAC' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0 LIKE '%Canada%' THEN 'NA' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0
                       LIKE '%Northern%' THEN 'NA' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0 LIKE '%Southern%' THEN 'NA' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0
                       LIKE '%Western%' THEN 'NA' WHEN dbo.v_RA_System_SystemOUName.System_OU_Name0 LIKE '%Americas%' THEN 'Americas' ELSE 'MISC' END AS
                       RegionOUCodeGrouped, dbo.v_GS_OPERATING_SYSTEM.Caption0, dbo.v_GS_OPERATING_SYSTEM.InstallDate0,
                      dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM         dbo.v_CICurrentComplianceStatus AS ccs INNER JOIN
                      dbo.v_R_System AS SYS ON ccs.ResourceID = SYS.ResourceID INNER JOIN
                      dbo.v_LocalizedCIProperties_SiteLoc ON ccs.CI_ID = dbo.v_LocalizedCIProperties_SiteLoc.CI_ID INNER JOIN
                      dbo.v_RA_System_SystemOUName ON SYS.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM ON SYS.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON SYS.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (dbo.v_LocalizedCIProperties_SiteLoc.DisplayName LIKE 'CI%') AND (ccs.ComplianceStateName = 'Compliant') AND
                      (dbo.v_RA_System_SystemOUName.System_OU_Name0 <> 'OPS.Domain.COM/AMERICAS')
ORDER BY dbo.v_LocalizedCIProperties_SiteLoc.DisplayName