Status report for Software Update deployments

Spending some time in other communities often gives me a lot of good ideas to reports and queries. A little while ago in the System Center Technet community I stumbled over a very need report created by a user called Qu4rk. His report shows a status of each update deployment, with a count of the each possible software update states. The only thing I have changed (thanks to Claus) is a new column showing the compliant computers in %. I am not going to take any credit for the report as my work was very limited. But this report is way to cool to be forgotten, and I am sure that it will help most of you.

image

 

The SQL code:

Select Deploymentname, Available, Deadline,

cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) as varchar(256)) + ‘%’ AS ‘% Compliant’,

  [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)]

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

      sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)])

) AS pvt

By | 2009-09-21T09:43:54+00:00 September 21st, 2009|Configuration Manager (SCCM)|20 Comments

About the Author:

Kent Agerlund
Microsoft Regional Director, Enterprise Mobility MVP. Microsoft Certified Trainer and Principal consultant. I have been working with Enterprise client management since 1992. Co-founder of System Center User Group Denmark in 2009. Certified MCITP: Enterprise Administrator, MCSA+Messaing, and much more. Member of: Microsoft Denmark System Center Partner Expert Team The Danish Technet Influencers program System Center Influencers Program.

20 Comments

  1. Vitalie Ciobanu September 21, 2009 at 11:02 - Reply

    Hi,

    Can you add add one more thing to this report? I haven’t learned how to “work” software updates reports…

    Would be nice to have a link from this report to “Compliance 4 – Deployment (per update)” report. This way, you can from here to a detailed report of a specific deployment.

    Thanks!

  2. Shane June 9, 2011 at 14:05 - Reply

    Great report! I second that from Vitalie… how do you link this to that other report?

  3. Ted August 25, 2011 at 21:41 - Reply

    Just an FYI, make sure to change the ticks to single quotes for your SQL query.

  4. Umesh September 28, 2011 at 18:36 - Reply

    Very useful report !! It is saving lot time for me. Thank you very much.

    I am looking for a report which tells me how many updates are failing on how many systems in a deployment.

    Any help would be highly appreciated.

    Regards,
    Umesh

  5. Chris September 30, 2011 at 19:00 - Reply

    Umesh,

    How about this?

    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

    By the way the script above did you get an Incorrect syntax near the keyword ‘as’?

  6. Shane Alexander December 10, 2011 at 1:07 - Reply

    I’ve improved the report, by including ALL Enforcement States, from http://technet.microsoft.com/en-us/library/bb932172.aspx . And changed the order of the Enforcement States, reading from “good” to “bad”, left to right.

    And… added drill down option to “States 1 – Enforcement states for a deployment” (which can then be drilled down to “States 4 – Computers in a specific state for a deployment”)… so you can see more details.

    SQL code below (Note : not sure how ‘ will go with copy and paste)

    Select DeploymentID, Deploymentname, Available, Deadline,

    cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Pending system restart], 0) + ISNULL([Waiting for restart], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Downloaded update(s)], 0) + ISNULL([Downloading update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Waiting for maintenance window before installing], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Failed to download update(s)], 0) + ISNULL([Failed to install update(s)], 0) ))*100) as Numeric(10,2)) as varchar(256)) + ‘%’ AS ‘% Compliant’,

    [Compliant],
    [Successfully installed update(s)],
    [Pending system restart],
    [Waiting for restart],
    [Installing update(s)],
    [Downloaded update(s)],
    [Downloading update(s)],
    [Waiting for another installation to complete],
    [Waiting for maintenance window before installing],
    [Enforcement state unknown],
    [Failed to download update(s)],
    [Failed to install update(s)]

    From

    (select
    a.Assignment_UniqueID as DeploymentID,
    a.AssignmentName as DeploymentName,
    a.StartTime as Available,
    a.EnforcementDeadline as Deadline,
    sn.StateName as LastEnforcementState,
    count(*) as NumberOfComputers
    from v_CIAssignment a
    join v_AssignmentState_Combined assc
    on a.AssignmentID=assc.AssignmentID
    join v_StateNames sn
    on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

    group by a.Assignment_UniqueID, a.AssignmentName, a.StartTime, a.EnforcementDeadline,
    sn.StateName) as PivotData

    PIVOT

    (

    SUM (NumberOfComputers)
    FOR LastEnforcementState IN

    ( [Compliant],
    [Successfully installed update(s)],
    [Pending system restart],
    [Waiting for restart],
    [Installing update(s)],
    [Downloaded update(s)],
    [Downloading update(s)],
    [Waiting for another installation to complete],
    [Waiting for maintenance window before installing],
    [Enforcement state unknown],
    [Failed to download update(s)],
    [Failed to install update(s)])
    ) AS pvt

    ORDER BY Deploymentname

    Then when at “Links”, choose “Link to another report”, then choose “States 1 – Enforcement states for a deployment”.

    You can also link above report to “States 2 – Evaluation states for a deployment”.

    Enjoy !!!

    Shane

  7. Bhasker June 4, 2012 at 15:10 - Reply

    Shane, I was looking for such a report but when i try to run a report in SQL Studio, i am getting the belwo error. I am not that well with SQL queries, cna you please help me.

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘‘’.
    Msg 156, Level 15, State 1, Line 34
    Incorrect syntax near the keyword ‘as’.

  8. Rich July 18, 2012 at 3:55 - Reply

    The query is perfectly fine. Thanks for sharing.

  9. Travis February 6, 2013 at 21:27 - Reply

    For everyone discussing the syntax error message you have to replace the apostrophes with actual ‘ marks. The apostrophes at the very end of the cast line:

    + ‘%’ AS ‘% Compliant’, <- make sure those are apostrophes and not weird quotes

  10. Nisar February 13, 2013 at 10:53 - Reply

    Hi,
    I am looking for SCCM2012 query for patch status from specific deployment and specific collection. I need the following details in my patch report with against each machine name
    Total patch count in deployment
    Total patch count installed
    Total patch count missing
    Total patch count not required

    • Dshay February 18, 2014 at 21:04 - Reply

      I’m looking for this as well. It would be nice to query which computer failed to install a patch from a specific deployment.

  11. Raja February 13, 2013 at 20:24 - Reply

    Code works perfectly…Thankzzz keep rockinggggg… 🙂

  12. Drift Rc Cars July 25, 2014 at 3:53 - Reply

    There are many fine stores to choose from, but consider the following:1.
    This year, experts are predicting that dolls based on the boy band,
    Onne Direction will sell incredibly well. In standard, the smaller a great RC car is normally, the more
    expensive it will be, as well.

  13. Gabriel Alicea July 29, 2014 at 15:27 - Reply

    This is a great report. I am currently using this at my organization. Thank you for this Kent!

  14. Nick L October 9, 2014 at 6:32 - Reply

    This is exactly what I am needing but not sure where/how to run it properly?

    How would i run this query? I really would love it as a query in CM, but also tried it in SQL on the CM database and get errors:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘‘’.
    Msg 156, Level 15, State 1, Line 47
    Incorrect syntax near the keyword ‘as’.

    Thanks

  15. CI November 21, 2014 at 8:14 - Reply

    Great report ..Can this be modified in such a way that it can be extracted collection wise ? I used prompt but it opens default to the above report

  16. G December 24, 2014 at 1:18 - Reply

    i have been using this report for a while. My question is can someone let me know how to add the field “Non-Compliant” under the Last state?
    Thanks for all your help

  17. Paul Murray January 15, 2015 at 14:49 - Reply

    Has anyone modified the report to break out by OU’s?
    Or any idea where to join v_RA_System_SystemOUName and v_R_system?

    Thanks
    -Paul

  18. Everett February 26, 2015 at 20:54 - Reply

    man this would be even more awesome if you could take this info and create a collection based on it for a more aggressive maintenance window or different reporting against this group of computers.I haven’t seen all of the views there to create something like this in a wql query

  19. Henry January 8, 2018 at 19:58 - Reply

    How do you include other assignmentnames in this query besides software updates (such as application deployments)

Leave A Comment