Enterprise Patch Management Report
Explanation Of Request
I was asked to produce a Patch Status Summary report that would display the status of each Department that is presently tracked though SMS. This report was to contain the following:
Agency = Department
Patched = A sum of all Critical Patches installed
Total Requests = A sum of all patches requested/required
Total Clients = Number of clients represented in the scan
Percent Compliant = (Patched / Total Requests ) X 100
Date Ran = Date the report was last run
This report is only supposed to show critical patches that are older than 30 days. This part is addressed in the actual Stored Procedure query for each agency.
This sounds easy but in my environment I am responsible for reporting on 45 agencies.
Here are the problems I saw:
Problems
- Some of the agencies are under our main Domain and some of the agencies are under a resource domain that they control. So the report needed to list each of 45 agencies.
- The second problem is that each row in the report requires that the query for that data be altered to reflect each agencies collection. This isn’t that big of a problem but each row takes 5-6 min to run. So, it would take almost 4 hours to run this report.
- The third problem is caused by agencies that have not implemented ITMU yet. So I added a query to show those agencies that do not have ITMU data in the DB as of yet. Once, all of the agencies are reporting, that part of the report can be removed.
Read the attached Document to see how I resolved this issue.