Thanks. That helped a lot. SQL queries is an area I am still working on. Michael Schultz Client Systems Engineering Information Systems Providence Health & Services michael.schu...@providence.org<mailto:michael.schu...@providence.org>
From: listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On Behalf Of Sherry Kissinger Sent: Thursday, October 06, 2016 8:35 AM To: mssms@lists.myitforum.com Subject: Re: [mssms] DCM Query Help This is not an answer to your question about "by baseline". that's because for us; the Baseline is used really as a handy container of "things we want to target to a collection on a schedule". The baseline compliant or not isn't really all that interesting to us for the "ok, who's not compliant". Seeing a baseline saying non-compliant just plain isn't interesting--WHICH ci in that baseline is the cause is the more interesting thing--one can actually do something about that. with that in mind, here's a couple of reports which several lines of business here use for their "everything still compliant?" quick check--and they can go look at the per-computer report for details. report #1 This is a summary of Per-CI, compliant or not, summarized to the machine level with raw counts. It's usually run against a collection; but the below I set it for running against an individual machine. This one is used just for the quick-glance "ok, who do I actually have to look at deeper" report. It's usually a manager that runs this, to give them the warm fuzzy that everything says "compliant". Slightly OT: notice the compliancestate 6 and 0? yeah, no idea what those actually are. but for some reason we get about 10 boxes (out of 300k) that every once in a while send back a state of 6 or 0. a re-run and it changes to a normal state--so whatever it is I don't know. but I account for it in this report; so a tech can follow up. ;with cte as ( select cs.resourceid, s1.netbios_name0 [computername], SUM(CASE WHEN cs.ComplianceState = 1 THEN 1 ELSE 0 END) AS CountCompliant, SUM(CASE WHEN cs.ComplianceState = 4 THEN 1 ELSE 0 END) AS FailureCount, SUM(CASE WHEN cs.ComplianceState = 2 THEN 1 ELSE 0 END) AS CountNoncompliant, SUM(CASE WHEN cs.ComplianceState = 6 THEN 1 ELSE 0 END) AS CountStateSix, SUM(CASE WHEN cs.ComplianceState = 0 THEN 1 ELSE 0 END) AS CountStateZero, MAX(cs.MaxNoncomplianceCriticality) AS Severity, MAX(cs.LastComplianceMessageTime) as LastComplianceMessageTime, count(cs.ComplianceState) as 'TotalCount' from v_FullCollectionMembership fcm join vSMS_BaselineAssignment ba on ba.collectionid=fcm.collectionid and fcm.name<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffcm.name&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=FsUKcOqklaYbROr4Xs50lwiPKUpl81Fq7W53b7U8q18%3D&reserved=0> in (select name from v_FullCollectionMembership f where f.collectionid = 'ABC00012') join v_r_system_valid s1 on s1.resourceid=fcm.resourceid join fn_ListConfigurationBaselineInfo(1033) lcb on lcb.CI_UniqueID=ba.AssignedCI_UniqueID join vSMS_CombinedConfigurationItemRelations cir on cir.FromCI_ID=lcb.CI_ID join v_ConfigurationItems ci on ci.CI_ID=cir.ToCI_ID join fn_ListCIs(1033) ciinfo on ciinfo.ci_id=cir.ToCI_ID join dbo.v_CICurrentComplianceStatus cs WITH (NOLOCK) on cs.CI_ID=ci.CI_iD and cs.ResourceID=s1.ResourceID where -- fcm.name<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffcm.name&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=FsUKcOqklaYbROr4Xs50lwiPKUpl81Fq7W53b7U8q18%3D&reserved=0> in (select name from v_FullCollectionMembership f where f.collectionid = 'ABC00012') -- comment out the below and enable the collection one when you want to see "for computers in a collection" fcm.name<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffcm.name&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=FsUKcOqklaYbROr4Xs50lwiPKUpl81Fq7W53b7U8q18%3D&reserved=0> = 'COMPUTERNAMEHERE' and cs.isapplicable=1 and cs.isdetected=1 group by cs.compliancestate, cs.ResourceID, s1.netbios_name0 ) select f.Resourceid, f.computername, sum(f.CountCompliant) as 'CountCompliant' , sum(f.FailureCount) as 'FailureCount' , sum(f.CountNoncompliant) as 'CountNoncompliant' , sum(f.CountStateSix) as 'CountStateSix' , sum(f.CountStateZero) as 'CountStateZero' , max(f.Severity) as 'Severity', max(f.lastComplianceMessageTime) as 'LastComplianceMessageTime' , sum(f.totalcount) as 'TotalCount of CIs' from cte f group by f.resourceid, f.computername order by f.computername for an individual computer, all the CIs that box deserves and said something about. This would be where a tech would run this report. there's also a slightly different one because knowing non-compliant is also just a step.. they also want to know exactly what were the non-compliant details for that ci? was it because the regkey was a 1 and not a 0? or what exactly was the returned information which made it be non-compliant? that's interesting to the tech that has to do remediation. -------------------- select fcm.name<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffcm.name&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=FsUKcOqklaYbROr4Xs50lwiPKUpl81Fq7W53b7U8q18%3D&reserved=0>, ba.AssignmentName, lcb.IsAssigned ,cs.IsApplicable, cs.IsDetected, cs.DesiredState, cs.ComplianceState ,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, ciinfo.DisplayName, ciinfo.Description from vSMS_BaselineAssignment ba join v_FullCollectionMembership fcm on fcm.collectionid=ba.collectionid join fn_ListConfigurationBaselineInfo(1033) lcb on lcb.CI_UniqueID=ba.AssignedCI_UniqueID join vSMS_CombinedConfigurationItemRelations cir on cir.FromCI_ID=lcb.CI_ID join v_ConfigurationItems ci on ci.CI_ID=cir.ToCI_ID join dbo.v_CICurrentComplianceStatus cs WITH (NOLOCK) on cs.CI_ID=ci.CI_iD and cs.ResourceID=fcm.ResourceID join fn_ListCIs(1033) ciinfo on ciinfo.ci_id=cs.CI_ID -- join fn_DCMDeploymentNonCompliantDetailsPerAsset(1033) det on det.AssetID=cs.ResourceID where fcm.name<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffcm.name&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=FsUKcOqklaYbROr4Xs50lwiPKUpl81Fq7W53b7U8q18%3D&reserved=0> = 'COMPUTERNAMEHERE' and cs.isapplicable=1 and cs.isdetected=1 order by cs.LastComplianceMessageTime desc On Wed, Oct 5, 2016 at 5:21 PM, Schultz, Michael A <michael.schu...@providence.org<mailto:michael.schu...@providence.org>> wrote: I guess this is more SQL query help than DCM. I have a report that lets you input a computer name and it displays a bunch of information like targeted deployments, last scans, etc. I am adding DCM information to the report to list what baselines and bundled items are targeting the machine. Here is the current query: SELECT v_R_System.Name0, v_LocalizedCIProperties.DisplayName, v_LocalizedCIProperties.Description, [v_BundledConfigurationItems_All.BundledLevel] = CASE when v_BundledConfigurationItems_All.BundledLevel = '0' then 'Baseline' else 'Configuration Item' End FROM v_BundledConfigurationItems_All INNER JOIN v_LocalizedCIProperties ON v_BundledConfigurationItems_All.BundledCI_ID = v_LocalizedCIProperties.CI_ID INNER JOIN v_R_System INNER JOIN v_BaselineTargetedComputers ON v_R_System.ResourceID = v_BaselineTargetedComputers.ResourceID ON v_BundledConfigurationItems_All.CI_ID = v_BaselineTargetedComputers.CI_ID group by v_R_System.Name0, v_BundledConfigurationItems_All.CI_ID, v_BundledConfigurationItems_All.BundledLevel, v_BundledConfigurationItems_All.BundledCI_ID, v_LocalizedCIProperties.DisplayName, v_LocalizedCIProperties.Description order by v_R_System.Name0, v_BundledConfigurationItems_All.CI_ID And the results come out likes this: Name0 DisplayName Description v_BundledConfigurationItems_All.BundledLevel computer .Net Framework 4.5.2 Baseline computer .Net Framework 4.5.2 Configuration Item computer Force Full Hardware Inventory Force full HW inventory on clients with a last hardware scan date older than 14 days. Baseline computer Force Full Inventory On Clients Configuration Item computer App-V 5 Client Service Configuration Item This is fine, but I want to be able to easily group the configuration items within the baselines for prettier and easier to read reports. Something like this: Name0 DisplayName Baseline DisplayName Configuration Item Description computer .Net Framework 4.5.2 .Net Framework 4.5.2 computer Force Full Hardware Inventory Force Full Inventory On Clients computer Force Full Hardware Inventory App-V 5 Client Service Can anyone assist? I am sure it is a simple solution but just not seeing it right now. Eventually, I want to add the compliance state for the DCM, but I am happy I got this far. I am also working on a “clean” (removing of custom HW inventory items from report) version of the report that others can use. Though it is very rough in the queries side. Michael Schultz SCCM Engineer - Client Systems Engineering Information Systems Providence Health & Services michael.schu...@providence.org<mailto:michael.schu...@providence.org> ________________________________ 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. -- Thank you, Sherry Kissinger My Parameters: Standardize. Simplify. Automate Blogs: http://www.mofmaster.com<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.mofmaster.com&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=YSrmPl0u0GJklhPYiMyh5t%2BYiS6GSo65ffCKDnA765Y%3D&reserved=0>, http://mnscug.org/blogs/sherry-kissinger<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmnscug.org%2Fblogs%2Fsherry-kissinger&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=GjwA2iwCBXoKt4s0TSSuoEET4BybLq33iREwBEOXebw%3D&reserved=0>, http://www.smguru.org<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.smguru.org&data=01%7C01%7Cmichael.schultz%40providence.org%7Cc4254bd73a4b46a5345808d3edff43cd%7C2e3190869a2646a3865f615bed576786%7C1&sdata=DhQe3oR8N%2BHGgIjY3nXyln9xcK1Wr%2Ft6dDWTczysJNA%3D&reserved=0> ________________________________ 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.
