9 Replies Latest reply on Feb 9, 2015 2:16 AM by callidus

    Creating an AdHoc Query for Update Computer Summaries

    callidus

      Hi,

      I can view the current status of the installation state of a specific Update by selecting it from the Update Services/WSUS/Updates/xxx/Update....." tree and viewing the "Computer Summary" Tab. Example as a screenshot.

      30-01-2015 08-10-05.png

       

      Using this view I can view the most current data. Now I'm searching for a way to create an automatic report. I can get the informations from the WSUS Analytics Tree, but those reports use consolidated non live data collected by the WSUS Inventory Task. This task needs about 4 hours to run.

       

      Any idea how to get such (near live) information in an automatic report? I could even live with a powershell script if it is not possible by using the reporting functions of patchmanager.

        • Re: Creating an AdHoc Query for Update Computer Summaries
          Lawrence Garvin

          This task needs about 4 hours to run.

          Would you like to troubleshoot this four-hour runtime in this thread, or in the other one?

           

          Reporting is how you get an "automatic report". It doesn't matter the methodology you use to extract the data in the database if you're only updating it once a week.

           

          As a starting point, I notice that you have over 12,000 updates synchronized to the WSUS server, so reducing the number of updates is a good place to start.

          The other consideration, of course, is looking at the system configuration of your WSUS server.

            • Re: Creating an AdHoc Query for Update Computer Summaries
              callidus

              I would be happy to troubleshoot it within this thread.

               

              This specific report was intended for getting a current report of the deployment (rate) of the current flash update.

                • Re: Creating an AdHoc Query for Update Computer Summaries
                  Lawrence Garvin
                  I would be happy to troubleshoot it within this thread.

                  Great!

                  With respect to my observation of the >12,000 updates in your collection, I would suggest starting with these two tasks:

                  WSUS Timeout Errors - Too many updates

                  WSUS Timeout Errors - Removing unneeded update approvals

                   

                  While I know you're not yet experiencing any timeout errors, you're likely pretty close to that threshold.

                  Once you've reduced the total number of updates, and the number of approved updates, to a manageable level, then you'll want to do:

                  WSUS Timeout Errors - Defragment the filesystem

                  WSUS Timeout Errors - WSUS database maintenance

                   

                  And the combination of all of the above should significantly reduce the runtime of your WSUS Inventory task.

                  (Also, just as a side note, double-check that your Inventory task is not conflicting with any other heavy network-usage type tasks, like backups or database ETLs)

                    • Re: Creating an AdHoc Query for Update Computer Summaries
                      callidus

                      Thanks.

                      Followed all 4 guides, but my Cleanup Task (just selected the first option) runs into a timeout (after only 3 minutes).

                      [Had blindly thought that the sheduled task mail within PM would indicate a problem...]

                        • Re: Creating an AdHoc Query for Update Computer Summaries
                          Lawrence Garvin
                          Followed all 4 guides, but my Cleanup Task (just selected the first option) runs into a timeout (after only 3 minutes).


                          Yes, it's going to do this. It's a manifestation of not having done the maintenance previously.

                          Restart the task and let it run until it times out again. Rinse and repeat until it completes.


                          What's happening in this phase is that a complex set of database joins needs to occur to identify the updates eligible for deletion. A list of updates is built from that list, and then the rows are deleted from the various tables, one update at a time. This takes quite some time to do if thousands of updates are involved. Unfortunately, the ASP.NET timeout waiting for a response from the database server is not very tolerant, and the writers of this utility never envisioned this volume of work to be performed. As a result, ASP.NET gets bored, drops the connection, and reports a timeout waiting for the database server. The database, however, was still working, but with the dropped connection the task gets killed off. The good news is that the work that was already done is still done, so nothing is lost.


                          Also, if you're performing one option at a time, this is the order you should perform them in:

                          Delete computers (Option #2 in the Patch Manager dialog) -- dead computers in the collection can significant complicate the effort involved to determine whether updates are needed.

                          Decline expired updates and superseded updates (Option #5 and #6 in the Patch Manager dialog) -- this task will run fairly quickly in any condition because no deletions are being performed, merely flipping a boolean flag value from No to Yes.

                          Delete updates (Option #1 in the Patch Manager dialog) -- this is the task that actually times out. As described above, the delete updates process can be an exceptionally time consuming one depending on how many updates are eligible for deletion. It will likely take several executions of this task before it completes all available work.

                          Delete files (Option #4 in the Patch Manager dialog) -- after all updates have been declined or deleted, then the filesystem can be effectively cleaned up. If filesystem space is already critical, you can run this task after each "Delete updates" timeout, and it will remove whatever files might exist that are associated with whatever updates have been deleted.



                      • Re: Creating an AdHoc Query for Update Computer Summaries
                        Lawrence Garvin
                        This specific report was intended for getting a current report of the deployment (rate) of the current flash update.

                        The simplest way to achieve this is to use the report Approved Summary with percentages which is available in the "Windows Server Update Services" report collection.

                        Filter the report by Update Title to get the Flash update of interest.