ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Master Patch Management Report

book

Article ID: 150194

calendar_today

Updated On:

Products

Patch Management Solution

Issue/Introduction

Master Patch Management Report 

Environment

Patch Management Solution 7.5, 7.6, 8.0, 8.5 and 8.6

Resolution

This Master Patch Management Report (SQL Query - Ultimate Patch Report_5 - All Clients & Bulletin-Update Details.txtapplies to versions earlier than ITMS 8.0. 

Caution: Putting this report into a Custom SQL Report to be ran on the Console may time-out due to the length of the returned data. The parameters on the report could be modified to a specific Filter or Target to reduce the rendering time, but running this in SQL Server Management Studio directly would be the ideal method of execution. 

  • Note: Review the SQL Script; GUID at the start of the query that pertains to the 'All Computers with Software Update Plug-in Installed' can be swapped out for any Filter/Target GUID. Review the Console > Manage > Filters; right-click > Properties, and copy that GUID and replace in the SQL Query. That should help to reduce the overall client count rendered.

Advisory: Found the report fails to render in ITMS 8.0 due to changes in database schema. Attached updated report to run in ITMS 8.0: SQL Query - Ultimate Patch Report - ITMS 8.0.sql.

  • This version of the report may also be modified in the script on the line to show all Software Updates, for it is written to keep returned rows limited to only the Software Updates that have an associated Software Update Policy.
    • Line to modify: declare @policyEnabled int = 1 --1 = Only in policies, 2 = all software updates

Additionally, added changes presented in ITMS 8.0 HF3 required an updated script. Please review the attached SQL Query - 80-HF3-Ultimate Patch Report_5 - All Clients & Bulletin-Update Details.txt to review in SQL Server Managment Studio, or import the attached .xml to view the report in the Console.

For 8.5 and 8.6 just import the xml file for report generation.

Attachments

Ultimate Patch Report - Filter Selection.xml get_app
SQL Query - Ultimate Patch Report_5 - All Clients & Bulletin-Update Details.txt get_app
SQL Query - Ultimate Patch Report - ITMS 8.0.sql get_app
SQL Query - 80-HF3-Ultimate Patch Report_5 - All Clients & Bulletin-Update Details.txt get_app