That commented out line was probably a miserably failed attempt to get that
to work.  In looking at what report we are ACTUALLY using for "show me
details of the reason why a CI is error or non-compliant, regardless of
baseline that CI might be in, for a collection"... this is what's actually
being used.  What will show up in the "Reason for Non-compliant" will be
whatever the target returned as for why it said an error or non-compliant.
It might show simply a "1", when you expected the value to be 0.  It might
show "Null"--if you said "there has to be at least 1 instance of something
returned".  It might be QAFIAREDD!!!; when you said "the value must exactly
equal  "QAFIAREDD!!" (only 2 !!, clearly totally wrong).  That "reason"
will be completely dependent upon what that particular CI's logic was for
"This means compliant", per CI.

DECLARE @CollectionID varchar(8) = 'PutYourCollectionIDHere OR of course,
have a Parameter prompting for a collID'

select
distinct
s1.Netbios_Name0 'ComputerName',
reason.CurrentValue 'Reason for Non-Compliant',
s1.ResourceID,
ciName.LocaleID,
ciName.CIInformativeURL,
ciname.DisplayName,
ccs.complianceState,

case
when ccs.complianceState=1 then 'Compliant'
when ccs.ComplianceState=2 then 'Non-Compliant'
when ccs.ComplianceState=3 then 'Conflict'
when ccs.ComplianceState=4 then 'Error'
when ccs.ComplianceState=5 then 'Not Applicable'
when ccs.ComplianceSTate=6 then 'Not Detected'
end as ComplianceStateName
,ccs.LastComplianceMessageTime
from v_CICurrentComplianceStatus ccs
join v_LocalizedCIProperties ciName on ciName.ci_id=ccs.ci_id
join v_FullCollectionMembership_Valid fcm on fcm.resourceid=ccs.ResourceID
join v_r_system_valid s1 on s1.resourceid=ccs.resourceid
join v_cicurrentsettingscompliancestatusdetail Reason on Reason.CI_ID=ccs.CI_ID
and reason.ResourceID=ccs.ResourceID
where
fcm.collectionid = @CollectionID
and ccs.compliancestate in (2,4)
and datediff(day,ccs.LastComplianceMessageTime,GetDate())<=180
and ciName.LocaleID = 1033  --put this in so that I could capture unique
instances
order by
reason.CurrentValue,
ccs.ComplianceState,
ccs.LastComplianceMessageTime

On Wed, Oct 12, 2016 at 4:39 PM, Schultz, Michael A <
michael.schu...@providence.org> wrote:

> 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 <michael.schu...@providence.org>*
>
>
>
> *From:* listsad...@lists.myitforum.com [mailto:listsadmin@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> 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 <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.
>
>


-- 
Thank you,

Sherry Kissinger

My Parameters:  Standardize. Simplify. Automate
Blogs: http://www.mofmaster.com, http://mnscug.org/blogs/sherry-kissinger,
http://www.smguru.org



Reply via email to