Skip to main content

MySQL - Views for Cumulative Update reports*

Summary​

This script is designed to initialize the database environment by creating the necessary SQL views required for the "Patch Compliance - CU" report set. It constructs four distinct views that process raw inventory and patch data into calculated compliance scores and statistical summaries.

The script performs the following actions:

  1. Creates the Master Detail View (plugin_proval_computerpatchcompliance): This view serves as the foundation for the reports. It calculates the "freshness" of the installed Cumulative Update (CU) for every individual computer, determines if the OS is End-of-Life (EOL), and assigns a weighted compliance score.
  2. Creates the Client Aggregator View (plugin_proval_clientpatchstats): This view summarizes the detailed computer data up to the client level. It calculates the overall "CU Compliance" percentage and provides the counts for the "Environment Summary" section of the "All Machines" report.
  3. Creates the Server-Specific Aggregator (plugin_proval_clientpatchstatsserver): This view filters the data to process statistics strictly for server operating systems, feeding the "CU Compliance and Audit - Servers" report.
  4. Creates the Workstation-Specific Aggregator (plugin_proval_clientpatchstatsworkstation): This view filters the data to process statistics strictly for workstations, feeding the "CU Compliance and Audit - Workstations" report.
  5. Applies Permissions: The script automatically grants the necessary SELECT permissions on these new views to active LabTech users, ensuring they can be accessed by the Report Designer.

Note: This is a one-time use script, used solely to install and create the required views. Once the script has been executed successfully and the views are confirmed to exist in the database, the script itself can be removed from the environment.

Sample Run​

Image1

Dependencies​

Output​

  • Views