none
WSUS Report - Updates approved per group

    Question

  • Hello all,

    I'm trying to figure out how to get a report in WSUS that would show which individual update is approved for which of my WSUS groups?  It would also need to include inherited approvals.

    Something like this:

    Title              GroupA        GroupB        GroupC        GroupD
    KB12345        Y                     Y                   Y                 Y
    KB54321        Y                     Y                   N                 Y
    KB00000        Y                     N                   N                N

    Is this possible with Power Shell or a database query?  I'm not quite sure how to make a script that would work to display with that output.

    I can make an update view with a single group picked and the "Updates are approved for a specific group (not including inherited approvals)" option, but that does not help me much since many updates are approved for All Computers so I don't see the inherited updates in the list then when I do it per group.

    Any help or pointers would be greatly appreciated!  Thanks!

    Friday, November 15, 2013 9:27 PM

Answers

  • I'm trying to figure out how to get a report in WSUS that would show which individual update is approved for which of my WSUS groups?  It would also need to include inherited approvals.

    Something like this:

    Title              GroupA        GroupB        GroupC        GroupD
    KB12345        Y                     Y                   Y                 Y
    KB54321        Y                     Y                   N                 Y
    KB00000        Y                     N                   N                N

    You'd have to do some copy-and-paste work to put that information into a worksheet to get it in the tabular format you show, but fundamentally that information is available from a native Update Status Report in the console. Right-click an update, select "Status Report". The very first page lists every group and the approval state for that group.

    Is this possible with Power Shell or a database query?

    It is also possible to extract this data with PowerShell or SQL, and with some creative use of PS scripting or SQL procedures, you can probably construct the tabular report above in a single pass. You'll need to refer to the WSUS PUBLIC_VIEWS for using SQL.


    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2013)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence R Garvin
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Friday, November 15, 2013 11:28 PM
    Moderator

All replies

  • I'm trying to figure out how to get a report in WSUS that would show which individual update is approved for which of my WSUS groups?  It would also need to include inherited approvals.

    Something like this:

    Title              GroupA        GroupB        GroupC        GroupD
    KB12345        Y                     Y                   Y                 Y
    KB54321        Y                     Y                   N                 Y
    KB00000        Y                     N                   N                N

    You'd have to do some copy-and-paste work to put that information into a worksheet to get it in the tabular format you show, but fundamentally that information is available from a native Update Status Report in the console. Right-click an update, select "Status Report". The very first page lists every group and the approval state for that group.

    Is this possible with Power Shell or a database query?

    It is also possible to extract this data with PowerShell or SQL, and with some creative use of PS scripting or SQL procedures, you can probably construct the tabular report above in a single pass. You'll need to refer to the WSUS PUBLIC_VIEWS for using SQL.


    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2013)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence R Garvin
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Friday, November 15, 2013 11:28 PM
    Moderator
  • I've seen this question asked in so many places without a satisfactory answer, at least for me. As an administrator, I need to manage what updates are rolled out to which groups and know what update were approved for which groups. I do not enjoy having department yell at us when they say a massive amount of updates were released and it delayed or caused some systems to have issues.

    It really is a legitimate and fair request to have a "built-in" reporting mechanism that could tell you at the end of the day, how many updates were approved to the various containers one has created regardless of whether they were approved for  the "All Computers" group  and inherited all the way down, or for a specific group.

    At this point, the only way to accomplish this is to approve updates for individual groups, even it the updates may apply to the entire organization. Unless you do this in the first place, there doesn't appear to be anyway to backtrack if you have approved updates for "All Computers" and allowed them to be inherited.

    What bugs me is that Microsoft specifically hard coded the "not including inherited approvals" in the built in reporting view. If there was just some way to get rid of that, I think many admins with this task would be eternally grateful.

    I understand that there are PowerShell this and PowerShell that, and SQL queries, etc., but frankly whose has the time for that? I do use these tools but am still learning and when someone like me is also responsible for things like a Cisco switch going down on a Sunday afternoon, I would like a better answer.

    Isn't there anybody out there that has figured out how to remove the "not including inherited approvals" filter without too much trouble?




    Monday, March 31, 2014 4:22 PM
  • As an administrator, I need to manage what updates are rolled out to which groups and know what update were approved for which groups.

    To be frank, most patch administrators with this level of deployment complexity would be documenting these requirements in a work order / change request prior to implementing them in the product.

    I do not enjoy having department yell at us when they say a massive amount of updates were released and it delayed or caused some systems to have issues.

    Big difference between computers not supposed to get updates, and the arbitrary fact that some month has "a massive amount of updates to install". Presumably these sensitive departments are getting some sort of notification as to what to expect for a patch cycle before it gets dumped on them.

    Otherwise, there are MANY reasons why a system might be installing "a massive number of updates", and most of them might have absolutely nothing to do with the approval procedures. The first step here is to investigate WHAT the systems installed and WHY they were installed at that time. If the WHAT and WHY matches up with the approvals issued -- well, those approvals are, ostensibly, known before clicking on the dialog in the console.

    It really is a legitimate and fair request to have a "built-in" reporting mechanism that could tell you at the end of the day, how many updates were approved to the various containers one has created regardless of whether they were approved for the "All Computers" group and inherited all the way down, or for a specific group.

    If a patch administrator has a need for this level of reporting, then I'd say, absolutely, they should be using groups with granular definitions as specific as possible. But, also, as I already noted, these "deployment plans" should be documented outside of WSUS prior to approving the updates. Otherwise, how would you ever audit what DID happen with what was SUPPOSED TO happen?

    At this point, the only way to accomplish this is to approve updates for individual groups, even it the updates may apply to the entire organization.

    Yes. Correct. That is exactly the purpose of having WSUS Target Groups!

    Unless you do this in the first place, there doesn't appear to be anyway to backtrack if you have approved updates for "All Computers" and allowed them to be inherited.

    I would argue that if you have subgroups, approving updates for "All Computers" and inheriting them is a fundamentally flawed practice to begin with.

    Fundamentally approving update for "All Computers" is a flawed practice unto itself, unless you actually have an update that should go to *ALL* computers unconditionally (e.g. Defender Definition Updates would be a good example).

    In fact, about the only reason I could imagine a patch administrator routinely approving updates for "All Computers" is because they don't have any other Target Groups -- and then I'd still be asking why there aren't any other Target Groups.

    What bugs me is that Microsoft specifically hard coded the "not including inherited approvals" in the built in reporting view. If there was just some way to get rid of that, I think many admins with this task would be eternally grateful.

    Everybody will have their own challeges; personally this has never been one of mine. First, in my case updates only get approved for the group(s) where those updates should be approved, so I don't even need a report to tell me what's approved where because I *know*, inherently, what group(s) any given update is/has been approved for. This is all a function of properly defining and using Target Groups and Approvals and having a deployment plan before configuring the approvals in the console.

    Isn't there anybody out there that has figured out how to remove the "not including inherited approvals" filter without too much trouble?

    Well, actually, yes. Presuming my understanding that you want a report on updates that have been Approved for one or more groups.

    1. Launch the Update Summary Status Report.
    2. Select the Target Group(s) (which don't care about inheritance or heirarchy).
    3. Select the Products/Classifications.
    4. Select the Status conditions. (If it's machines already slammed by a bunch of updates, then probably you only need to look at "Installed" updates.)
    5. Run the Report.

    The report produces one page per update based on the selections made and lists ALL of the Target Groups selected, and the approval of that update for each of those groups.

    It also helps if you've narrowed the target of your investigation to one or more specific updates prior to running the report. Select the page corresponding to the update(s) of interest and observe the approval state(s) for those update(s).

    Another way to approach this.. if a department is complaining about one or more computers, then run a Computer Detailed Status Report for that computer. The first page of the report section for each computer provides an overview, and the second and subsequent pages provides a list of all updates selected (products; classifications) and the installation and approval state of the updates. Sort by Approval if it helps.

    For detailed manipulation of reporting data... all reports can be exported to EXCEL.

    For sharing with others, they can be exported to PDF.


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Friday, April 04, 2014 11:05 PM
    Moderator