This is continuation to the post avilable here on http://eskonr.com/2010/03/monthly-patch-statistics-reports-to-show-up-to-the-management-in-a-simplified-manner/
Report posted on the above link doesnt give you the required information what it gives in SMS 2003 since some of the columns in have been changed i.e product has blank value from v_GS_patchstausEX view etc in sccm 2007.
Below is the modified report that works in SCCM environment.
1) Patch Management summary:
select summ.ID,summ.QNumbers as 'Q Number',
COUNT(distinct ps.ResourceID) as 'Requested',
COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End) as 'Installed',
ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)
/count(distinct ps.ResourceID),2) as 'Success %'
from v_GS_PatchStatusEx ps
join v_ApplicableUpdatesSummaryEx summ on
ps.UpdateID=summ.UpdateID
where (summ.ID='MS11-075') and
(summ.Type='Microsoft Update')
group by summ.ID,summ.QNumbersorder by summ.ID
PS: Please correct the quotes used in this query ,if you do copy paste,might give you error like "Incorrect Syntax error " so type the quotes again by removing the exisinting ones.
I will post other quiries in the mean time what is avilable in SMS 2003 environment.
Attached is MOF file which you can directly import into your configmgr 2007 environment rather copy /paste.Patch_complilance_report.MOF (remove the txt extension,you will get MOF file).
9 Comments
Pingback: Patch Management | Process Street
This report is exactly what I'm looking for, we have the same report that runs in our SCCM environment but doesn't port over to our CM12 environment. Is there a way to make this report work in CM12 by collection, maybe an RDL for ease of import?
I have posted lot many reports on patch compliance status also ,in CM12 ,you can see the status of the patches directly in the console.
Have a look at the posts on compliance reports http://eskonr.com/?s=patch+compliance+report&submit=
I've looked at your Config Mgr 2012 reports but don't see anything like this one that reports the current status of a deployment, Where you can select the deployment, specifically the different Last State of Software Updates, or any other deployment. I currently have a SCCM 07 report that tells me the following deployment Last State(s):
Compliant,
Downloaded Update(s)
Downloading Update(s)
Enforcement State Unknown
Failed to download Update(s)
Failed to install update(s)
Installing Update(s)
Pending system restart
Successfully installed update(s)
Waiting for another installation to complete
The problem I have is that the report won't port into CM12, your 07 report is close to this information which is much more information on what the Compliance 1- Overall Compliance report generates.
have you looked at the default reports in CM12 for deployment status based on last state(s) ? While creating reports,i did not look at the last state(s) ,the more interested was, whether the client is compliant or not ,no matter the cause for Non-Compliant and it needs troubleshooting.
Understood; however, knowing the Last State is very helpful in how the non-compliant clients get troubleshot and resolved. I guess I'll have to find another way.
it is possible using status messages to amend this into the query, i will look at it later when get time..
Hi Kevin,
it is possible using state messages , need to have sometime to look at the message ID and its description .
Hello Kevin,
I am currently stuck to fetch the details of all updates in an SUG deployed to a collection by their deployment state i.e. -
Last State(s):
Compliant,
Downloaded Update(s)
Downloading Update(s)
Enforcement State Unknown
Failed to download Update(s)
Failed to install update(s)
Installing Update(s)
Pending system restart
Successfully installed update(s)
Waiting for another installation to complete
Could you kindly help me with the query please. CB 1906