Sorry to bring this back up.  This was helpful.  I saw 
fn_DCMDeploymentNonCompliantDetailsPerAsset was commented out.  I wanted to 
include the compliance of the individual settings within a configuration item 
as well.  Since they won’t let me auto remediate, quickly seeing what setting 
failed would be great.  Problem is that it takes so long to run this query.  
Longer than it should and I normally just kill it after ten minutes.  Did I do 
it wrong or is there a way to get the info without using the function as adding 
fn_DCMDeploymentNonCompliantDetailsPerAsset seems it cause the long execution 
time?

SELECT        fcm.Name, ba.AssignmentName, ciinfo.DisplayName, 
ciinfo.Description, cis.SettingName, cis.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, 
det.RuleStateDisplay, det.RuleSeverity
FROM            vSMS_BaselineAssignment AS ba JOIN
                         v_FullCollectionMembership AS fcm ON fcm.CollectionID 
= ba.CollectionID JOIN
                         dbo.fn_ListConfigurationBaselineInfo(1033) AS lcb ON 
lcb.CI_UniqueID = ba.AssignedCI_UniqueID JOIN
                         vSMS_CombinedConfigurationItemRelations AS cir ON 
cir.FromCI_ID = lcb.CI_ID JOIN
                         v_ConfigurationItems AS ci ON ci.CI_ID = cir.ToCI_ID 
JOIN
                         v_CICurrentComplianceStatus AS cs WITH (NOLOCK) ON 
cs.CI_ID = ci.CI_ID AND cs.ResourceID = fcm.ResourceID JOIN
                         dbo.fn_ListCIs(1033) AS ciinfo ON ciinfo.CI_ID = 
cs.CI_ID JOIN
                         v_CISettings AS cis ON ciinfo.CI_ID = cis.CI_ID JOIN
                         dbo.fn_DCMDeploymentNonCompliantDetailsPerAsset(1033) 
AS det ON det.Setting_ID = cis.Setting_ID
WHERE        (fcm.Name = 'COMPUTER') AND (cs.IsApplicable = 1) AND 
(cs.IsDetected = 1)


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 6, 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.

Reply via email to