I recently put this together for a report we use to look at a lot of information on a computer. This is the DCM part. (FYI, we are using build 1602)
SELECT ba.AssignmentName, ciinfo.DisplayName, ciinfo.Description, v_CISettings.SettingName, v_CISettings.SettingDescription, CASE WHEN cs.ComplianceState = 1 THEN 'Compliant' WHEN cs.ComplianceState = 2 THEN 'Non-Compliant' WHEN cs.ComplianceState = 4 THEN 'Failure' END AS 'TextComplianceState', cs.IsEnforced, cs.ComplianceValidationRuleFailures, cs.ErrorCount, cs.ConflictCount, cs.LastComplianceMessageTime, cs.LastEnforcementMessageTime FROM vSMS_BaselineAssignment AS ba INNER JOIN v_FullCollectionMembership AS fcm ON fcm.CollectionID = ba.CollectionID INNER JOIN dbo.fn_ListConfigurationBaselineInfo(1033) AS lcb ON lcb.CI_UniqueID = ba.AssignedCI_UniqueID INNER JOIN vSMS_CombinedConfigurationItemRelations AS cir ON cir.FromCI_ID = lcb.CI_ID INNER JOIN v_ConfigurationItems AS ci ON ci.CI_ID = cir.ToCI_ID INNER JOIN v_CICurrentComplianceStatus AS cs WITH (NOLOCK) ON cs.CI_ID = ci.CI_ID AND cs.ResourceID = fcm.ResourceID INNER JOIN dbo.fn_ListCIs(1033) AS ciinfo ON ciinfo.CI_ID = cs.CI_ID INNER JOIN v_CISettings ON ciinfo.CI_ID = v_CISettings.CI_ID WHERE (fcm.Name = 'computer') AND (cs.IsApplicable = 1) AND (cs.IsDetected = 1) Michael Schultz Client Systems Engineering Information Systems Providence Health & Services [email protected]<mailto:[email protected]> From: [email protected] [mailto:[email protected]] On Behalf Of Jeff Poling Sent: Thursday, November 17, 2016 9:42 AM To: [email protected] Subject: [mssms] Compliance Baseline report I need to create a report that provides the compliant and non compliant counts for specific compliance baselines. Does anyone have sample SQL for something like that? There is a technet article with some samples but they are not getting exactly what I need. Just looking for the wisdom of the group to get me started….. Thanks! Jeff Sent from my Windows 10 phone ________________________________ This message is intended for the sole use of the addressee, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message.
