This is getting me pretty much what I want. It lists all the machines that aren't running because of the OS requirement not being checked on the item.
select sys.ResourceID, sys.Netbios_Name0, os.Caption0, os.CSDVersion0, dcm.* from v_r_system sys join vDCMDeploymentNonCompliantAssetDetails dcm on sys.ResourceID = dcm.AssetID join v_GS_OPERATING_SYSTEM os on sys.ResourceID = os.ResourceID where dcm.BL_ID = '140135' --and ci_id = '140134' and dcm.IsBaselineRule = '1' Down and dirty, but gets me what I want. Thanks for sending that info over, helped me figure it out. Rob From: [email protected] [mailto:[email protected]] On Behalf Of Robert Spinelli Sent: Tuesday, August 9, 2016 2:58 PM To: [email protected] Subject: RE: [mssms] RE: Compliance Status Query ? Thanks, this should point me in right direction. Weird that view though only seems to have items that are non-compliant. You would think if it's called CIComplianceStatusDetail it would have both. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Tuesday, August 9, 2016 1:39 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Compliance Status Query ? Here is a start. Its every device and its compliance state. Easy to filter on what you want. select cicsd.Netbios_Name0 [Name], cicsd.ci_id [CI ID], cicsd.configurationitemname [CI], CICSD.Criteria, cicsd.CurrentValue, cicsd.LastComplianceMessageTime [Last Message] from v_R_System SYS join v_CIComplianceStatusDetail CICSD on sys.resourceid = cicsd.ResourceID Daniel Ratliff From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Robert Spinelli Sent: Tuesday, August 09, 2016 1:13 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] Compliance Status Query ? Anyone have a query they use to report back what CI in a baseline is causing the baseline to be non-compliant? If you have a baseline with OS requirements selected that comes back as non-compliant and then you have CI in that baseline come back as complaint, the whole baseline is non-complaint (correct). I can't seem to figure out how to get a query to show me which of the items in the baseline is causing the baseline to be non-compliant. Thanks Rob The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
