Displays the compliance of all configuration items for the selected baseline deployed to the selected collection with the actual values returned by the configuration item setting. Note that if you want to use CI's for reporting you need to select a compliacen rule that is never compliant. The values are returned only when the setting is found as non-compliant. Popovici Ioan @ SCCM-Zone.com 0 /ConfigMgr_A01/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} None 11111111-1111-1111-1111-111111111111 AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_ =Parameters!UserTokenSIDs.Value /* Get UserSID */ SELECT dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) AS UserSIDs true UserSIDs System.String AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_ =Parameters!ReportName.Value /* Get ReportDescription */ SELECT DISTINCT Description FROM ReportServer.dbo.Catalog WHERE Name = @ReportName true Description System.String AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_ =Parameters!BaselineID.Value =Parameters!UserSIDs.Value =Parameters!CollectionID.Value =Parameters!LocaleID.Value /* .SYNOPSIS Gets the Compliance of a Configuration Baseline. .DESCRIPTION Gets the Compliance and Actual Values of a Configuration Baseline setting result. .NOTES Created by Ioan Popovici Requires SSRS/SQL, SCCM Configuration Baseline .LINK BlogPost: https://sccm-zone.com/baseline-reporting-with-actual-values-output-in-sccm-73fec334ba8f .LINK Changes : https://SCCM.Zone/cb-configuration-baseline-compliance-changelog .LINK Github : https://SCCM.Zone/cb-configuration-baseline-compliance .LINK Issues : https://SCCM.Zone/issues */ /*##=============================================*/ /*## QUERY BODY */ /*##=============================================*/ /* Testing variables !! Need to be commented for Production !! */ --DECLARE @UserSIDs AS NVARCHAR(250) = 1; --DECLARE @CollectionID AS NVARCHAR(10) = 'A01000EC'; --DECLARE @LocaleID AS INT = 2; --DECLARE @BaselineID AS INT = 503286; /* Initialize CIID table */ DECLARE @CIID TABLE ( CIID INT ) /* Initialize SystemsInfo table */ DECLARE @SystemsInfo TABLE ( ResourceID INT , DeviceName NVARCHAR(250) , OperatingSystem NVARCHAR(250) , OSVersion NVARCHAR(250) , Managed NVARCHAR(5) , ClientState NVARCHAR(20) ) /* Initialize ComplianceInfo table */ DECLARE @ComplianceInfo TABLE ( ComplianceState NVARCHAR(20) , ResourceID INT , UserName NVARCHAR(250) , CIVersion INT , SettingVersion INT , SettingName NVARCHAR(250) , RuleName NVARCHAR(250) , Criteria NVARCHAR(250) , ActualValue NVARCHAR(450) , InstanceData NVARCHAR(250) , LastEvaluation NVARCHAR(250) , Severity NVARCHAR(20) ) /* Get CIs to process */ INSERT INTO @CIID (CIID) SELECT ToCIID FROM dbo.fn_rbac_CIRelation(@UserSIDs) WHERE FromCIID = @BaselineID AND RelationType NOT IN (7, 0) --Exlude itself and no relation /* Get systems data */ INSERT INTO @SystemsInfo (ResourceID, Managed, ClientState, DeviceName, OperatingSystem, OSVersion) SELECT ResourceID = Computers.ResourceID , Managed = CASE Computers.Client0 WHEN 1 THEN 'Yes' ELSE 'No' END , ClientState = ClientSummary.ClientStateDescription , DeviceName = Computers.Netbios_Name0 , OperatingSystem = CASE WHEN OperatingSystem.Caption0 != '' THEN CONCAT( REPLACE(OperatingSystem.Caption0, 'Microsoft ', ''), --Remove 'Microsoft ' from OperatingSystem REPLACE(OperatingSystem.CSDVersion0, 'Service Pack ', ' SP') --Replace 'Service Pack ' with ' SP' in OperatingSystem ) ELSE /* Workaround for systems not in GS_OPERATING_SYSTEM table */ ( CASE WHEN CombinedResources.DeviceOS LIKE '%Workstation 6.1%' THEN 'Windows 7' WHEN CombinedResources.DeviceOS LIKE '%Workstation 6.2%' THEN 'Windows 8' WHEN CombinedResources.DeviceOS LIKE '%Workstation 6.3%' THEN 'Windows 8.1' WHEN CombinedResources.DeviceOS LIKE '%Workstation 10.0%' THEN 'Windows 10' WHEN CombinedResources.DeviceOS LIKE '%Server 6.0' THEN 'Windows Server 2008' WHEN CombinedResources.DeviceOS LIKE '%Server 6.1' THEN 'Windows Server 2008R2' WHEN CombinedResources.DeviceOS LIKE '%Server 6.2' THEN 'Windows Server 2012' WHEN CombinedResources.DeviceOS LIKE '%Server 6.3' THEN 'Windows Server 2012 R2' WHEN CombinedResources.DeviceOS LIKE '%Server 10.0' THEN 'Windows Server 2016' ELSE 'Unknown' END ) END , OSVersion = ( SELECT OSLocalizedNames.Value FROM fn_GetWindowsServicingLocalizedNames() AS OSLocalizedNames INNER JOIN fn_GetWindowsServicingStates() AS OSServicingStates ON OSServicingStates.Build = Computers.Build01 WHERE OSLocalizedNames.Name = OSServicingStates.Name AND Computers.OSBranch01 = OSServicingStates.branch --Select only the branch of the installed OS ) FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers LEFT JOIN fn_rbac_R_System(@UserSIDs) AS Computers ON Computers.ResourceID = CollectionMembers.ResourceID LEFT JOIN fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs) OperatingSystem ON OperatingSystem.ResourceID = CollectionMembers.ResourceID LEFT JOIN fn_rbac_CombinedDeviceResources(@UserSIDs) AS CombinedResources ON CombinedResources.MachineID = CollectionMembers.ResourceID LEFT JOIN fn_rbac_CH_ClientSummary(@UserSIDs) AS ClientSummary ON ClientSummary.ResourceID = CollectionMembers.ResourceID WHERE CollectionMembers.CollectionID = @CollectionID /* Get compliance data */ INSERT INTO @ComplianceInfo (ResourceID, ComplianceState, UserName, CIVersion, SettingVersion, SettingName, RuleName, Criteria, ActualValue, InstanceData, LastEvaluation, Severity) SELECT DISTINCT ResourceID = CISettingsStatus.ResourceID , ComplianceState = CIComplianceState.ComplianceStateName , UserName = CISettingsStatus.UserName , CIVersion = CIComplianceState.CIVersion , SettingVersion = CISettingsStatus.CIVersion , SettigName = CISettings.SettingName , RuleName = CIRules.RuleName , Criteria = CISettingsStatus.Criteria , ActualValue = CISettingsStatus.CurrentValue , InstanceData = CISettingsStatus.InstanceData , LastEvaluation = CISettingsStatus.LastComplianceMessageTime , Severity = CASE CISettingsStatus.RuleSeverity WHEN 0 THEN 'None' WHEN 1 THEN 'Information' WHEN 2 THEN 'Warning' WHEN 3 THEN 'Critical' WHEN 4 THEN 'Critical with event' END FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers INNER JOIN fn_rbac_CIComplianceStatusDetail(@UserSIDs) AS CISettingsStatus ON CISettingsStatus.ResourceID = CollectionMembers.ResourceID INNER JOIN fn_ListCIRules(@UserSIDs) AS CIRules ON CIRules.Rule_UniqueID = CISettingsStatus.Rule_UniqueID AND CIRules.CIVersion = CISettingsStatus.CIVersion --Select only curent baseline version INNER JOIN fn_ListCISettings(@LocaleID) AS CISettings ON CISettings.Setting_UniqueID = CISettingsStatus.Setting_UniqueID AND CISettings.CIVersion = CISettingsStatus.CIVersion INNER JOIN fn_rbac_ListCI_ComplianceState(@LocaleID, @UserSIDs) AS CIComplianceState ON CIComplianceState.ResourceID = CollectionMembers.ResourceID AND CIComplianceState.CI_ID = @BaselineID WHERE CollectionMembers.CollectionID = @CollectionID AND CISettingsStatus.CI_ID IN (SELECT CIID FROM @CIID) AND CIComplianceState.ComplianceStateName != 'Error' --We are adding errors below, removing artefacts if any /* Get error data and add it to ComplianceInfo table */ INSERT INTO @ComplianceInfo (ResourceID, ComplianceState, UserName, CIVersion, SettingVersion, SettingName, RuleName, Criteria, ActualValue, InstanceData) SELECT ResourceID = ErrorDetails.AssetID , ComplianceState = 'Error' , UserName = ErrorDetails.ADUserName , CIVersion = ErrorDetails.BLRevision , SettingVersion = ErrorDetails.Revision , SettigName = ErrorDetails.CIName , RuleName = ErrorDetails.ObjectName , Criteria = ErrorDetails.ObjectTypeName , ActualValue = ErrorDetails.ErrorTypeDisplay , InstanceData = ErrorDetails.ErrorCode FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers INNER JOIN fn_DCMDeploymentErrorDetailsPerAsset(@LocaleID) AS ErrorDetails ON ErrorDetails.AssetID = CollectionMembers.ResourceID AND ErrorDetails.BL_ID = @BaselineID WHERE CollectionMembers.CollectionID = @CollectionID /* Join SystemsInfo and ComplianceInfo data */ SELECT ComplianceState = ( SELECT ISNULL(ComplianceInfo.ComplianceState, 'Unknown' )) , Managed = SystemsInfo.Managed , ClientState = SystemsInfo.ClientState , DeviceName = SystemsInfo.DeviceName , OperatingSystem = CONCAT(SystemsInfo.OperatingSystem, (' ' + SystemsInfo.OSVersion)) , UserName = ComplianceInfo.UserName , CIVersion = ComplianceInfo.CIVersion , SettingVersion = ComplianceInfo.SettingVersion , SettingName = ComplianceInfo.SettingName , RuleName = ComplianceInfo.RuleName , Criteria = ComplianceInfo.Criteria , ActualValue = ComplianceInfo.ActualValue , InstanceData = ComplianceInfo.InstanceData , LastEvaluation = ComplianceInfo.LastEvaluation , Severity = ComplianceInfo.Severity FROM @SystemsInfo AS SystemsInfo LEFT JOIN @ComplianceInfo AS ComplianceInfo ON ComplianceInfo.ResourceID = SystemsInfo.ResourceID WHERE DeviceName != '' --Eliminate artefacts with no device name ORDER BY ComplianceState , Managed , ClientState , DeviceName , OperatingSystem , UserName , CIVersion , SettingVersion , SettingName , RuleName , Criteria , ActualValue , InstanceData , LastEvaluation , Severity /*##=============================================*/ /*## END QUERY BODY */ /*##=============================================*/ true Managed System.String ComplianceState System.String DeviceName System.String ClientState System.String OperatingSystem System.String UserName System.String CIVersion System.Int32 SettingVersion System.Int32 RuleName System.String SettingName System.String Criteria System.String ActualValue System.String InstanceData System.String LastEvaluation System.String Severity System.String AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_ =Parameters!UserSIDs.Value /* Get Device Collections */ SELECT CollectionID, Name FROM dbo.fn_rbac_Collection(@UserSIDs) WHERE CollectionType = 2 ORDER BY Name; true CollectionID System.String Name System.String AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_ =Parameters!LocaleID.Value =Parameters!UserSIDs.Value /* Get Baselines */ SELECT CI.CI_ID AS BaselineID, CI.CI_UniqueID AS BaselineUniqueID, dbo.fn_GetLocalizedCIName(@LocaleID, CI.CI_ID) AS BaselineName, dbo.fn_GetLocalizedCIDescription(@LocaleID, CI.CI_ID) AS BaselineDescription FROM dbo.fn_rbac_ConfigurationItems(@UserSIDs) CI WHERE CI.CIType_ID = 2 AND CI.IsLatest = 1 ORDER BY BaselineName; true BaselineID System.Int32 BaselineUniqueID System.String BaselineName System.String BaselineDescription System.String AutoGen__5C6358F2_4BB6_4a1b_A16E_8D96795D8602_ =Parameters!Locale.Value /* Get Locale ID */ SELECT dbo.fn_LShortNameToLCID(@Locale) true System.Int32 9.10041cm 9.37917cm 4.14042cm 2.5cm 1.89146cm 0.6cm true =Fields!ActualValue.Value DeviceName true =SrsResources.Localization.GetString("ActualValue", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!InstanceData.Value DeviceName true =SrsResources.Localization.GetString("InstanceData", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!LastEvaluation.Value true =SrsResources.Localization.GetString("LastComplianceMessageTime", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Fields!Severity.Value true =SrsResources.Localization.GetString("Severity", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt true =Sum(Count(Fields!DeviceName.Value)) ComplianceState true =SrsResources.Localization.GetString("Total", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 0.6cm true true =Fields!ActualValue.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true =IIF(IsNumeric(Fields!InstanceData.Value), SrsResources.Localization.GetErrorMessage(Fields!InstanceData.Value, User!Language), Fields!InstanceData.Value) #e6eefc Middle 2pt 2pt 2pt 2pt true true =SrsResources.Localization.GetLocalTime(Fields!LastEvaluation.Value,User!Language) #e6eefc Middle 2pt 2pt 2pt 2pt true true =Fields!Severity.Value #e6eefc Middle 2pt 2pt 2pt 2pt true true Textbox4 #e6eefc Middle 2pt 2pt 2pt 2pt 0.6cm true true =IIF(IsNumeric(Fields!InstanceData.Value) And IsNothing(Fields!InstanceData.Value) = False, SrsResources.Localization.GetString("ErrorType", User!Language),"") #e6eefc #e6eefc #e6eefc #e6eefc #c6daf8 Middle 2pt 2pt 2pt 2pt true true =IIF(IsNumeric(Fields!InstanceData.Value) And IsNothing(Fields!InstanceData.Value) = False, SrsResources.Localization.GetString("ErrorDescription", User!Language),"") #e6eefc #e6eefc #e6eefc #e6eefc #c6daf8 Middle 2pt 2pt 2pt 2pt true true Textbox62 #e6eefc #e6eefc #e6eefc #e6eefc #c6daf8 Middle 2pt 2pt 2pt 2pt true true Textbox107 #e6eefc #e6eefc #e6eefc #e6eefc #c6daf8 Middle 2pt 2pt 2pt 2pt true true =Count(Fields!ActualValue.Value) #c6daf8 Middle 2pt 2pt 2pt 2pt 0.6cm true true Textbox123 #9eb6e4 Middle 2pt 2pt 2pt 2pt true true Textbox123 #9eb6e4 Middle 2pt 2pt 2pt 2pt true true Textbox123 #9eb6e4 Middle 2pt 2pt 2pt 2pt true true Textbox123 #9eb6e4 Middle 2pt 2pt 2pt 2pt true true =CountDistinct(Fields!DeviceName.Value) #9eb6e4 Middle 2pt 2pt 2pt 2pt 0.6cm true true #7292cc Middle 2pt 2pt 2pt 2pt true true #7292cc Middle 2pt 2pt 2pt 2pt true true #7292cc Middle 2pt 2pt 2pt 2pt true true #7292cc Middle 2pt 2pt 2pt 2pt true true =CountDistinct(Fields!DeviceName.Value) #7292cc Middle 2pt 2pt 2pt 2pt 2.14959cm true true =SrsResources.Localization.GetString("Managed", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 3.28021cm true =Fields!ComplianceState.Value ClientState true =SrsResources.Localization.GetString("Compliance State", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 3.43265cm true =Fields!DeviceName.Value DeviceName true =SrsResources.Localization.GetString("DeviceName", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 2.94978cm true =Fields!ClientState.Value DeviceName true =SrsResources.Localization.GetString("Client State", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 4.61667cm true =Fields!OperatingSystem.Value DeviceName true =SrsResources.Localization.GetString("OperatingSystem", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 2.5cm true =Fields!UserName.Value DeviceName true =SrsResources.Localization.GetString("User", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 3.4525cm true =Fields!SettingName.Value DeviceName true =SrsResources.Localization.GetString("SettingName", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 3.34667cm true =Fields!RuleName.Value DeviceName true =SrsResources.Localization.GetString("RuleName", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt 2.34125cm true =Fields!Criteria.Value DeviceName true =SrsResources.Localization.GetString("Criteria", User!Language) #4c68a2 Middle 2pt 2pt 2pt 2pt =Fields!Managed.Value =Fields!Managed.Value 2.14959cm true true =Fields!Managed.Value 1pt #9eb6e4 Middle 2pt 2pt 2pt 2pt =Fields!ComplianceState.Value =Fields!DeviceName.Value Descending 3.28021cm true true =Fields!ComplianceState.Value #9eb6e4 Middle 2pt 2pt 2pt 2pt =Fields!DeviceName.Value =Fields!ClientState.Value =Fields!DeviceName.Value 3.43265cm true true =Fields!DeviceName.Value #c6daf8 Middle 2pt 2pt 2pt 2pt =Fields!ClientState.Value =Fields!ClientState.Value 2.94978cm true true =Fields!ClientState.Value #c6daf8 Middle 2pt 2pt 2pt 2pt =Fields!OperatingSystem.Value =Fields!OperatingSystem.Value 4.61667cm true true =Fields!OperatingSystem.Value #e6eefc Middle 2pt 2pt 2pt 2pt =Fields!UserName.Value =Fields!UserName.Value 2.5cm true true =Fields!UserName.Value #e6eefc Middle 2pt 2pt 2pt 2pt =Fields!SettingName.Value =Fields!SettingName.Value 3.4525cm true true =Fields!SettingName.Value #e6eefc Middle 2pt 2pt 2pt 2pt =Fields!RuleName.Value =Fields!RuleName.Value 3.34667cm true true =Fields!RuleName.Value #e6eefc Middle 2pt 2pt 2pt 2pt Details 2.34125cm true true =Fields!Criteria.Value #e6eefc Middle 2pt 2pt 2pt 2pt 2.94978cm true true Values #c6daf8 Middle 2pt 2pt 2pt 2pt 4.61667cm true true Textbox36 #c6daf8 Middle 2pt 2pt 2pt 2pt 2.5cm true true Textbox53 #c6daf8 Middle 2pt 2pt 2pt 2pt 3.4525cm true true =IIF(IsNumeric(Fields!InstanceData.Value) And IsNothing(Fields!InstanceData.Value) = False, SrsResources.Localization.GetString("CI Name", User!Language),"") #c6daf8 Middle 2pt 2pt 2pt 2pt 3.34667cm true true =IIF(IsNumeric(Fields!InstanceData.Value) And IsNothing(Fields!InstanceData.Value) = False, SrsResources.Localization.GetString("Object Name", User!Language),"") #c6daf8 Middle 2pt 2pt 2pt 2pt 2.34125cm true true =IIF(IsNumeric(Fields!InstanceData.Value) And IsNothing(Fields!InstanceData.Value) = False, SrsResources.Localization.GetString("ObjectType", User!Language),"") #c6daf8 Middle 2pt 2pt 2pt 2pt true TV_ComplianceState 3.43265cm true true Computers #9eb6e4 Middle 2pt 2pt 2pt 2pt 2.94978cm true true Textbox14 #9eb6e4 Middle 2pt 2pt 2pt 2pt 4.61667cm true true Textbox37 #9eb6e4 Middle 2pt 2pt 2pt 2pt 2.5cm true true Textbox54 #9eb6e4 Middle 2pt 2pt 2pt 2pt 3.4525cm true true Textbox97 #9eb6e4 Middle 2pt 2pt 2pt 2pt 3.34667cm true true Textbox76 #9eb6e4 Middle 2pt 2pt 2pt 2pt 2.34125cm true true Textbox123 #9eb6e4 Middle 2pt 2pt 2pt 2pt Before 2.14959cm true true Total #c6daf8 #7292cc Middle 2pt 2pt 2pt 2pt 3.28021cm true true #7292cc Middle 2pt 2pt 2pt 2pt 3.43265cm true true #7292cc Middle 2pt 2pt 2pt 2pt 2.94978cm true true #7292cc Middle 2pt 2pt 2pt 2pt 4.61667cm true true #7292cc Middle 2pt 2pt 2pt 2pt 2.5cm true true #7292cc Middle 2pt 2pt 2pt 2pt 3.4525cm true true #7292cc Middle 2pt 2pt 2pt 2pt 3.34667cm true true #7292cc Middle 2pt 2pt 2pt 2pt 2.34125cm true true #7292cc Middle 2pt 2pt 2pt 2pt ComplianceStatusData 1.22349cm 0.09525cm 3cm 55.08078cm 1.47018in 8.34071in 0.4in true true =SrsResources.Localization.GetString("Description", User!Language) 2pt 2pt 2pt 2pt true true =SrsResources.Localization.GetString(Fields!Description.Value, User!Language) 5pt 2pt 2pt 2pt ReportDescription 0.00278in 0.12284cm 0.4in 9.81089in 1 0.42917in 0.02387in 0in 21.69898in 2 1.5pt White 1.5pt White 1.5pt White 1.5pt White 1.5pt 4.82349cm #6e7b8b 55.30832cm 2.54668cm true true 0.60597in 0.02387in 0in 21.69898in 0.75pt White 0.75pt White 0.75pt White 0.75pt White 0.75pt true true =SrsResources.Localization.GetString(Globals!ReportName, User!Language) 1.64851cm 0.12284cm 0.89817cm 24.91965cm 1 Middle 2pt 2pt 2pt 2pt #6e7b8b 0.78486cm true true true ="Page: " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString() 0.07056cm 0.06063cm 0.63492cm 2.79292cm #6e7b8b 21cm 29.7cm 2.54cm 2.54cm 2.54cm 2.54cm 1.27cm String =SrsResources.UserIdentity.GetUserSIDs(User!UserID) UserTokenSIDs true String AdminID UserSIDs UserSIDs true 2c7b7dc1-c255-43f4-b6eb-9836f5be644e /Report Parts/UserSIDs 2015-02-25T10:56:21.3870000+01:00 String =User!Language Locale true String LocaleID ID LocaleID true String Baseline Name BaselineInfo BaselineID BaselineName String Collection Name CollectionInfo CollectionID Name String =Globals!ReportName true String true ReportDescription Description true true SrsResources, culture=neutral Cm 6fbd236a-d836-4950-8788-14b828deb616