Patch Compliance Report that only includes updates contained in policies
It is essential to understand how the reports in the Patch Management Solution calculate compliance. For example, the Compliance by Computer report calculates compliance based on all bulletins applicable to that computer.
If you download the applicability/detection rules for a particular bulletin and the Patch Management Solution finds that an update associated with that bulletin is applicable to a particular computer but not installed on that computer, that computer is considered to not be in compliance with respect to that bulletin. This is true even if you have not created any policies to distribute the updates associated with the bulletin to that computer.
While the Patch Management Solution reports provide you with an "absolute" measure of compliance, there may be occasions in which you want to see another view of compliance. For example, you may want to see the compliance status of a particular computer with respect to only those bulletins associated with policies that target that computer rather, than all bulletins that are applicable to that computer. In order to do so, you can build a custom report by creating a stored procedure using the SQL in the attachment to this post according to the following instructions:
- Download the attached file and change the name of the file extension from "txt" to "sql"
- Run the file in Management Studio. It will create the new stored procedure needed for the report (spPMWindows_TargetedUpdateComplianceByComputer).
- Clone the default Windows Compliance By Computer report and name it Windows Targeted Update Compliance By Computer.
- Export the report to a convenient location.
- Open the XML file in a program such as SQL Management Studio or Visual Studio. Search for 'spPMWindows' and you will find a reference to the current stored procedure being used (spPMWindows_ComplianceByComputer).
- Simply replace the call to that stored procedure with the new one (spPMWindows_TargetedUpdateComplianceByComputer). No other changes are required.
- Save the file
- Right click on the Compliance folder under Patch and choose Import - select the XML file we just changed