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 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 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 = '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, 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 = '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 <
[email protected]> 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
>
> *[email protected] <[email protected]>*
>
>
>
> ------------------------------
>
> 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, http://mnscug.org/blogs/sherry-kissinger,
http://www.smguru.org
