join vSMS_BaselineAssignment ba on ba.collectionid=fcm.collectionid and fcm.name in (select name from v_FullCollectionMembership f where f.collectionid = 'ABC00012')
is that computername also in a collectionID of ABC00012 ? (just like my lab?) On Thu, Oct 6, 2016 at 2:27 PM, SCCM FUN <[email protected]> wrote: > Can anyone get the 1st report to work? It just returns blank for me. I am > putting in my computer name. > > > The 2nd report worked for me when I enter in my computer name. > > > Thanks > > > ------------------------------ > *From:* [email protected] <[email protected]> > on behalf of Sherry Kissinger <[email protected]> > *Sent:* Thursday, October 6, 2016 11: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 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 > > > -- Thank you, Sherry Kissinger My Parameters: Standardize. Simplify. Automate Blogs: http://www.mofmaster.com, http://mnscug.org/blogs/sherry-kissinger, http://www.smguru.org
