SCCM Configmgr Patch Status Compliance software update report

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 Responses to "SCCM Configmgr Patch Status Compliance software update report"

  1. 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)

    Reply
  2. 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.

    Reply
  3. 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

    Reply
    1. 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).

      Reply

Leave a Reply