Patch Status Compliance(software updates) report in SMS /SCCM
Posted by Eswar Koneti on October 16th, 2009
Are you looking for getting a report for a given KB numbers or MSID(like MS10-087),you can add few more lines to the existing code as like below and change the ID1 to ID2 also add a prompt to it.The same report is also applicable in SCCM but it doesn’t give some columns though it has required information.
SMS 2003 Patch Status report for given list of Specific MS ID, Q Number, title:
select summ.Product, summ.LocaleID, summ.Language,
COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end) as ‘Distribution Successful’,
COUNT(distinct case when ps.LastState=102 then ps.ResourceID else NULL end) as ‘Reboot Pending’,
COUNT(distinct case when ps.LastState=101 then ps.ResourceID else NULL end) as ‘Distribution Failed’,
COUNT(distinct case when ps.LastState not in (107,105,101,102) then ps.ResourceID else NULL end) as ‘Distribution Incomplete’,
COUNT(distinct ps.ResourceID) as ‘In Distribution Scope’,
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 %’,
ROUND(100.0*COUNT(distinct case when ps.laststate=101 then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as ‘failed %’,
ROUND(100.0*COUNT(distinct case when ps.LastState not in (107,105,101,102) then ps.ResourceID else NULL end)/count(distinct ps.ResourceID),2) as ‘Incomplete%’
from v_GS_PatchStatusEx ps
join v_ApplicableUpdatesSummaryEx summ on
ps.UpdateID=summ.UpdateID
where (summ.ID=@ID or summ.QNumbers=@ID or summ.Title=@ID) and
summ.Type=’Microsoft Update’
group by summ.Product, summ.LocaleID, summ.Language
order by summ.Product, summ.LocaleID, summ.Language
Prompt:
begin
if (@__filterwildcard = ”)
select distinct Title, ID, QNumbers,Type from v_ApplicableUpdatesSummaryEx order by Title
else
select distinct Title, ID, QNumbers, Type from v_ApplicableUpdatesSummaryEx
WHERE Title like @__filterwildcard
order by Title
end
Patch compliance Progress report in SCCM:
This report will gives you more information about the SCCM clients displays how many patches are needed more to be installed on this since it does the scan agent with your SCCM server(WUAhandler.log).By default i have provided to query from All systems ,if you need to have it from specific collection,you may add prompt for asking collection ID or Name also.
select CS.Name0, CS.UserName0,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then (‘Needs ‘+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ‘ Patches’))else ‘Good Client’
end as ‘Status’,
ws.lasthwscan as ‘Last HW scan’,
FCM.collectionID–,
from v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = UCS.ResourceID
join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’
left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
Where UCS.Status = ’2′
and FCM.collectionid = ‘SMS00001′
Group by CS.Name0,CS.UserName0,ws.lasthwscan,FCM.collectionID
Order by CS.Name0,CS.UserName0,ws.lasthwscan,FCM.collectionID
Hope it helps you.
March 6th, 2012 at 4:57 PM
Hi,
I have a problem with the “patch compliance progress report in SCCM” script.
Although it appears to me to be systems in which patches are missing, but no systems where all patches are installed.
Question:
How would the script be changed, so that systems can be displayed, in which patches are needed 0 (= Good client).
Thanks and regards,
Mario
March 6th, 2012 at 6:24 PM
Hi Mario,
What error are you getting ? if you are getting any error with Syntax ,suggest you to replace quotes by removing existing from the query as blog take typos.
If the report is working and no patches are installed successfully then you may have to see why it doesnt install as the report is based on status ID.
which report are you trying ? SMS report (the first one) ? it works well only in SMS 2003 because in sccm 2007 some of the fields are changed so you would see blanks one.look at here http://eskonr.com/2011/10/sccm-monthly-patch-statistics-reports-to-the-management-in-a-simplified-manner/
what do you mean by script to be changed ? do you to modify the report query to show what you need or you want the report to show what you need (you cant do this as it comes from database).