As you saw from my prior posts I was onsite for a customer the week before Thanksgiving. I’m trying to push out all of the things we learned or created there. This cool solution provides the report that we worked with them to build. Basically the requirement they had was that they need a report that would show the overall Patched or Not Patched status for each device in the system, but only for patches that were assigned to the device via a Patch Policy or Remediation.

This particular report gives a simply Patched / Not Patched status that is a roll-up of all of the patches assigned to all patch policies and remediation bundles for that device and a simple pie chart summary that shows the overall status for all devices in the system. It was created with JasperStudio so that optimal SQL queries are being used. This report will work on environments using MS SQL as the ZENworks database platform. It is recommended that you run the indexes.sql in the zip file to create new indexes on the database to ensure optimal performance.

In my next post I expect to provide a small supplemental domain for ZENworks that can be used to build custom reports against the data that is exposed by the queries used to generate this report.

How to import this report

To import this report do the following:

1. Download the Report Archive and extract it so that you have the OverallPatchStatus.jrxml file.

• Login as a ZENworks Reporting admin to your ZENworks Reporting server.

• From the menu select View > Repository.

• Expand Organization > Reports.

• Right click the folder where you want to import the report.

• Select Add Resource > Report. This displays the following screen:
  

• In the Name field, enter Overall Patch Status.

• Under the Upload a Local File text box, click Browse.

• Browse for the OverallPatchStatus.jrxml that you downloaded; then click Open.

• On the left side, click Controls & Resources. This displays the following:
  

• Under Input Controls, click Add Input Control…

• Select Define an Input Control in the next step; then click Next.

• In the Type drop-down select Multi-select Query.

• In the Prompt Text field, enter Patch Status.

• In the Parameter Name field, enter Patch_Status.

• Select Mandatory and Visible.

• Click Next.

• Select Define a Query in the next step; then click Next.

• In the Name field of the Name the Query set, enter Patch_Status; then click Next.

• Select Select data source from repository.

• Browse to Organization > Data Sources > ZENworks Datasource; then click Select.

• Click Next.

• In the query language field select SQL.

• In the Query string field enter:
  select distinct case when Status=1 or Status=3 THEN 'Patched'
  ELSE 'Not Patched' end as Patch_Status from PATCHDEVICESTATUS

• Click Save.

• In the Value Column field, enter Patch_Status.

• In the Visible Columns field, enter Patch_Status; then click Add.

• Click Submit.

• From the Display Mode drop-down, select Pop-up window.

• Select Always prompt.

• From the left, select Data Source.

• Select Select data source from repository.

• Browse to Organization > Data Sources > ZENworks Datasource; then click Select.

• Click Submit. This will import the report.

• You should now see the report and click on it to run it.

Report Behavior

When the report runs you will be asked whether you want to see Patched or Not Patched devices or both in the report as shown below:



One you click OK then the report will run. The first pages will show the overall status for each device as shown below:



From here you can click on the device name to be taken to ZCC Patch details for the device.

If you go to the last page of the report you will see the summary page as shown below:



I hope that you will find this report useful. I hope to wrap up the supplemental domain to surface some of the underlying data for custom reporting in the next couple of days.