Functions to use to access Configuration Baseline compliance information

See here for additional functions.

declare @BaselineName varchar (100) = 'Check for dbutil_2_3.sys'
declare @CollectionName varchar (100) = 'All Desktop And Server Clients'

declare @AssignmentID int = (select ds.AssignmentID from fn_DeploymentSummary(1033) ds where ds.ApplicationName = @BaselineName and ds.CollectionName = @CollectionName)

select ds.* from fn_DeploymentSummary(1033) ds where ds.ApplicationName = @BaselineName

select * from fn_DCMDeploymentNonCompliantStatus(1033) nc
where nc.AssignmentID = @AssignmentID

select * from fn_DCMDeploymentCompliantStatus(1033) c
where c.AssignmentID = @AssignmentID

select * from fn_DCMDeploymentCompliantAssetDetails(1033) cad
where cad.AssignmentID = @AssignmentID

;
with ActiveDirectoryLocation as
(
select s.ResourceID, ou.System_OU_Name0 ActiveDirectoryLocation from v_R_System s inner join
(
select soun.ResourceID, soun.System_OU_Name0 from v_RA_System_SystemOUName soun inner join
(
select ResourceID, MAX (LEN (System_OU_Name0)) as LongestOULength from v_RA_System_SystemOUName
group by ResourceID
)
as ou on ou.ResourceID = soun.ResourceID and LEN (soun.System_OU_Name0) = ou.LongestOULength
) ou on ou.ResourceID = s.ResourceID
union all
select scn.ResourceID, scn.System_Container_Name0 from v_RA_System_SystemContainerName scn
)

select distinct
adl.ActiveDirectoryLocation
, s.Name0
, s.operatingSystem0
, ncad.CIName
--, ncad.RuleName
, ncad.RuleStateDisplay
, cs.Manufacturer0
, cs.Model0
from fn_DCMDeploymentNonCompliantAssetDetails(1033) ncad
inner join v_R_System s on s.ResourceID = ncad.AssetID
inner join ActiveDirectoryLocation adl on adl.ResourceID = ncad.AssetID
left join v_GS_COMPUTER_SYSTEM cs on cs.ResourceID = ncad.AssetID
where ncad.AssignmentID = @AssignmentID
order by s.operatingSystem0, s.Name0