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 < [email protected]> 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 > > *[email protected] <[email protected]>* > > > > *From:* [email protected] [mailto:listsadmin@lists. > myitforum.com] *On Behalf Of *Sherry Kissinger > *Sent:* Thursday, October 6, 2016 8:35 AM > > *To:* [email protected] > *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 < > [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 > <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
