Here is a quick trick on how to create a report based on a Configuration Baseline's Non-compliance utilizing only a collection ID. This assumes the Config Item and Baseline are already created, as well as the Collection based on Non-Compliance of the baseline. The report can then be generated based on the below query, then subscribed to, to check results over time. The only thing needing editing in the query is the CollectionID in bold.
select all dbo.v_Collection.Name AS Location,
collection.Name AS [Computer Name],
dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial Number],
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],
dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Computer Model],
"Client" = CASE
WHEN collection.ClientType = 1 THEN 'Yes'
WHEN collection.ClientType = 0 THEN 'No'
ELSE ''
END,
"Operating_System" = CASE
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 6.1%' THEN 'Windows 7'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 10.0%' THEN 'Windows 10'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 6.3%' THEN 'Windows 8.1'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Advanced Server 6.0%' THEN 'Windows Server 2008'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Advanced Server 5.2%' THEN 'Windows Server 2003'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT%Server 6.1%' THEN 'Windows Server 2008 R2'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT%Server 6.3%' THEN 'Windows Server 2012 R2'
WHEN collection.DeviceOS LIKE 'Microsoft Windows NT Workstation 5.1%' THEN 'Windows XP'
WHEN collection.DeviceOS LIKE 'Mac OS X%' THEN 'Mac OS X'
end,
collection.Domain AS [Domain],
dbo.v_CH_ClientSummary.LastPolicyRequest AS [Last Checked-in],
dbo.v_CH_ClientSummary.LastHW AS [Last HW Inventory],
collection.UserName AS [Last Logged User],
dbo.v_FullCollectionMembership.CollectionID AS [CollID]
from _RES_COLL_COL00001 AS collection
left JOIN dbo.v_GS_COMPUTER_SYSTEM ON collection.MachineID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
left JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON collection.MachineID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
left JOIN dbo.v_FullCollectionMembership ON collection.MachineID = dbo.v_FullCollectionMembership.ResourceID
left JOIN dbo.v_Collection ON dbo.v_FullCollectionMembership.CollectionID = dbo.v_Collection.CollectionID
left JOIN dbo.v_CH_ClientSummary ON collection.MachineID = dbo.v_CH_ClientSummary.ResourceID
where
(dbo.v_FullCollectionMembership.CollectionID IS null
or dbo.v_FullCollectionMembership.CollectionID = 'CAS00002'
or dbo.v_FullCollectionMembership.CollectionID = 'CAS00003')
ORDER BY [Computer Name]
The 'where' clause includes extra Collections to which the results can be further filtered down - this was required in my situation since we didn't want to have all endpoints included.
