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.
6 Comments
Links did not show.
Patch compliance WQL Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 5
Updating Parameters (Month/Year) Creating a Custom Report for System Center 2012 R2 Configuration Manager – Part 3
Sorry for the delay, here is the updated code.
Patch compliance updated WQL
This report was updated to include the date code from Gary Simmons post which enables us to evaluate against patches released during a specific period. In addition, You can filter by Domain Name if more than one, AD Site Name, OS Type (Workstation or Server), and Collections. I also have a new field called Max run time (mins) which will evaluate against a user defined amount of time to determine if the system will complete within a maintenance window.
DECLARE @StartDate datetime, @EndDate datetime
Set @StartDate = DATEFROMPARTS(@StartYear,@StartMonth,1)
Set @EndDate = DATEFROMPARTS(@EndYear,@EndMonth,1)
SELECT DISTINCT
CS.Name0,
CS.ResourceID,
CS.UserName0,
SYS.AD_Site_Name0,
SYS.Operating_System_Name_and0,
SYS.operatingSystemServicePac0,
CASE WHEN SYS.Is_Virtual_Machine0='1' THEN 'Yes' ELSE 'No' END AS 'Is VM',
LD.FreeSpace0/1024 AS FreeSpaceGB,
LD.FreeSpace0*100/LD.Size0 as C074,
COL.CollectionName,
CASE WHEN SW.IsEnabled='1' THEN SW.StartTime ELSE SW.StartTime END AS StartTime,
CASE
WHEN (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Non-compliant')
ELSE 'Compliant'
END AS 'Status',
CASE
WHEN (sum(case when UCS.status=2 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 then 1 else 0 end)as int)))
ELSE '0'
END AS 'Patch Count',
CASE
WHEN (sum(case when UCS.status=2 then 1 else 0 end))>0 then (cast(sum(case when UCS.status=2 then UI.MaxExecutionTime/60 else 0 end) as int))
ELSE '0'
END AS 'Run Time (mins)',
CASE
WHEN (LD.FreeSpace0/1024)0 then (cast(sum(case when UCS.status=2 then UI.MaxExecutionTime/60 else 0 end) as int))
ELSE '0'
END ) > @RunTimeLimit
THEN 'FAIL - Run Time'
ELSE 'Pass'
END AS 'Automated (Pass/Fail)',
WS.LastHWScan as 'Last HW scan'
FROM
v_Update_ComplianceStatusAll UCS
left outer join v_GS_COMPUTER_SYSTEM CS on (CS.ResourceID = UCS.ResourceID)
join v_UpdateInfo UI on UI.CI_ID=UCS.CI_ID
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_FullCollectionMembership FCM on (FCM.ResourceID = CS.ResourceID)
left join v_GS_WORKSTATION_STATUS WS on (WS.ResourceID = CS.ResourceID)
left join v_GS_OPERATING_SYSTEM OS on (OS.ResourceID = CS.ResourceID)
left join v_GS_LOGICAL_DISK LD on (LD.ResourceID = CS.ResourceID)
left join v_R_System SYS on (SYS.ResourceID = CS.ResourceID)
join v_Collections COL on (COL.SiteID = FCM.CollectionID)
left join v_ServiceWindow SW on (SW.CollectionID = COL.SiteID)
WHERE
UI.IsDeployed = '1' AND
UI.DatePosted BETWEEN @StartDate AND @EndDate AND
SYS.AD_Site_Name0 in (@ADSiteName) AND
COL.CollectionName in (@CollectionName) AND
LD.DeviceID0 = 'C:' AND
SYS.Operating_System_Name_and0 like ('%' + @OSType + '%')
GROUP BY
CS.Name0,
CS.ResourceID,
CS.UserName0,
SYS.AD_Site_Name0,
SYS.Operating_System_Name_and0,
SYS.operatingSystemServicePac0,
SYS.Is_Virtual_Machine0,
LD.FreeSpace0,
LD.Size0,
COL.CollectionName,
SW.IsEnabled,
SW.StartTime,
WS.LastHWScan
ORDER BY
CS.Name0,StartTime
AD Domain WQL (DataSet1)
This code gets a list of domain names found in SCCM.
Select CollectionID, CollectionName
FROM fn_rbac_Collections(@UserSIDs)
ORDER BY CollectionName
AD Site Name WQL (DataSet2)
This code will simply get a list of AD Site Names.
SELECT DISTINCT v_R_System_Alias.AD_Site_Name0 AS ADSiteName
FROM fn_rbac_R_System(@UserSIDs) v_R_System_Alias
WHERE v_R_System_Alias.Resource_Domain_OR_Workgr0 in (@ADDomain)
ORDER BY 1
Collection Details (DataSet3)
This query filters a list of collections based on naming conventions. You can remove the WHERE clause if you want list all collections.
Select CollectionID, CollectionName
FROM fn_rbac_Collections(@UserSIDs)
WHERE CollectionName like ('% All ' + @OSType + 's') or
CollectionName like ('%SU Windows ' + @OSType + '%')
ORDER BY CollectionName
Updating Parameters (Month/Year)
Refer to Gary Simmons post for details on customizing the year and month parameters.
Updating Parameters (OSType)
Create two available values, one for Workstations and another for Servers.
Updating parameters (RunTimeLimit)
Create a default value expression for the RunTimeLimit parameters. The first number (400) will be listed if the OSType matches Workstation. The default value (600) will apply to Servers.
=IIF(Parameters!OSType.Value="Workstation",400,600)
Your patch compliance report for SCCM was a great baseline to start with. I have updated the report to also include additional details such as
- OS and Service Pack version
- free disk space on C
- isVM
- ADSite
- cumulative run time (validating against mainteance window)
In addition I have added parameters to filter by OSType (Workstation OR Server) and select multiple collections to check.
ok,great.you can post the query here so others who read the blog will be helpful.
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
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).