How to report on ZENworks Patch Management Patch Policy Compliance with ZENworks Reporting
One of the most requested ZENworks Patch Management features we have is the ability to report on the patch policy compliance status. Basically you want to know which devices in the zone have all of the patches they are supposed to and which don't. While ZENworks Patch Management gives you an out of the box Compliance Dashlet that shows you the overall compliance status, it doesn't currently provide an easy way to do that based on a device's assigned policies.
This article is my solution to this request. It uses ZENworks Reporting to provide the requested capabilities using Ad Hoc Reporting topics. For more information on what a ZENworks Reporting Topic is I recommend you check out this article. In the past I had created another solution that described how to use JasperStudio to create similar reports, but with the flexibility of Topics I've decided this will provide a much better solution. So without further adieu, here's how you can use ZENworks Reporting to figure out your Patch Policy Compliance Status as shown in the screen shot below:
IMPORTANT: The SQL queries behind these topics are specifically crafted for Microsoft SQL Server, although they probably work with Sybase as well. If you take a look at the queries you will see they aren't pretty and they have lots of joins because it's the only way to get the data. As such, I do not recommend building or running these reports during peak business hours as there is a potential that you will cause utilization on the database servers to spike. Instead either build these and then schedule them to run in off-peak hours OR create a copy of the DB that gets sync'd regularly and have ZRS report against that instance.
Import the JRXML topics to your ZENworks Reporting Server
When you unzip the attachment to this article you will find two .jrxml files. These files are the Ad Hoc Topic definitions that need to be imported into your ZENworks Reporting Server in order for you to generate patch policy compliance reports. To import them you'll need to do the following:
- Download and install the latest version of JasperStudio from the ZENworks download page. This makes it much easier to trust the SSL certificate of your JasperServer when establishing the connection.
- Launch JasperStudio.
- Add your ZENworks Reporting Server to JasperStudio so that when you are done creating the topic you can easily push it to your ZENworks Reporting Server.
- In the Repository Explorer pane, right-click Servers and click Create JasperReports Server Connection.
- In the Name field, enter ZENworks Reporting
- In the URL field, enter the URL to your ZENworks Reporting server, including “/jasperserver-pro”
- In the User field, enter the name of a user with rights to create reports.
- In the Password field, enter the password for the user.
- Click Test Connection and verify the connection works.
- Click Finish.
- Add a Data Adapter so that JasperStudio can connect to your ZENworks database or whatever other Endpoint Management product backend you want to run reports against. (Remember legally you can only use ZENworks to report against the Endpoint Management family of products).
- In the Repository Explorer pane, right-click Data Adapters and click Create Data Adapter.
- Select Database JDBC Connection from the list, then click Next.
- Give the adapter a name, something like ZENworks JDBC.
- Pick the database driver type your ZENworks server is using.
- Enter the JDBC URL. This might take a little Googling to figure out. You can see the one I used for SQL.
Here's a Sybase example: jdbc:sybase:Tds:10.1.10.1:2638?ServiceName=zenworks_ZENGURU.
- Click Test to make sure you can connect.
- Once you've verified you can connect, click Finish to save the change.
- Now open up each of the JRXML files that are included with this solution. They are:
- PatchCompliance.jrxml – This one gives you the big picture fields.
- Patchpolicycompliancedetails.jrxml – This one dives into the details about each device and patch assigned to the patch policy.
- Repeat the following steps for each of the JRXML files to publish the reports and their input controls to your ZRS server.
- Select Project > Publish the file on JasperReports Server.
- Expand Ad Hoc Components and click Topics.
- Check the Create Report Unit checkbox.
- Enter a name that you want the Ad Hoc user to see when he attempts to create the report.
- Click Next.
- On the Datasource tab, select Datasource from Repository.
- Click ... and browse to the Data Sources and pick the ZENworks Reporting data source that corresponds to your ZENworks database, usually ZENworks Datasource.
- Click Finish to publish the report.
Build Patch Policy Compliance Reports and Dashboards
Now that you’ve got the topics imported you can build some reports and possibly even a dashboard. For your summary reports you’re probably going to want to create 1 custom field and 1 custom measure in the ad hoc editor. Here’s what they are:
Custom Measure: Compliance Percent
Field Definition: ("Patches Applied Count" / "Total Applicable Patches") * 100
Description: This gives you the % of compliance to the policy.
Custom Field: Compliance State
Field Definition: IF("Compliance Percent" == 100, 'Compliant', 'Not Compliant')
Description: This displays compliant if the device is at 100% else it displays non-compliant.
Below are a handful of videos that show you how you can build out reports and a dashboard that use these topics. Hope you find these useful!
Creating the custom fields and a compliance overview chart:
Creating a tabular report that shows compliance details:
Creating a tabular report with the details of patch policy per device:
Create a patch policy compliance dashboard:
Scheduling the execution of your reports and dashboard: