At the moment I'm on the road in Asia training partners and I had a few minutes to share something I learned last week that I think will be interesting to everyone. I was in Taiwan doing partner training on ZENworks Reporting and a partner asked me how he could get the same Patch Compliance information that the new ZENworks 2017 Update 3 dashlets gives in ZR.  Let's show you what that dashlet looks like to make sure you understand what I'm talking about:



This partner wanted to know how he could create a tabular report in ZENworks Reporting that would show whether the device was compliant or not and then add a filter for Operating System so that he could have a separate report for each OS. Well after a little bit of digging, it turned out that with the current ZENworks Domain there's no easy way to get that data. As it turns out there were new tables added to the ZENworks database to facilitate the creation of that dashboard and those fields haven't yet been exposed in the ZENworks Reporting domain for ZENworks.

This meant I had to go exploring and I found out something I thought was pretty cool. You see, the ZENworks Reporting Ad Hoc editor has the ability to generate reports from either Domains or from Topics. Until last week I thought that a Topic was always a subset of an existing Domain, but that's not the case. I learned last week that I can use JasperStudio to create a Topic that will run any SQL query I want and then make it available to the ZENworks Reporting Ad Hoc editor as a Topic. This means that if there's anything in the ZENworks database that you want to report on that isn't available in the Domain, or that isn't linked to other things in the way you want, you could actually write your own SQL query and then be able to use that for Ad Hoc reporting. Here's how:

1. First, you'll need to install JasperStudio. This is available from the Customer Center in the same build as where you downloaded ZENworks and ZENworks Reporting.

• (Optional) If you are using an Internal CA for your ZENworks Reporting server you will need to add your server’s CA trusted root certificate to the JasperStudio keystore so that JasperStudio can make a SSL connection to your ZENworks Reporting server. To do this:
  
  
  
  1. Run Internet Explorer as Administrator.
  
  
• Browse to your ZENworks Reporting Server.

• Click on the lock and view the certificate.

• Click the Certification Path tab.

• Click the top most certificate, then click View Certificate.

• Click the Details tab.

• Click on Copy to File...

• At the welcome screen click Next.

• Select Base-64 encoded X.509(.cer)

• Click Next and then enter a file path and name for the certificate.

• Click Next, then Finish.

• Exit Internet Explorer.

• Run CMD as Administrator.

• Change to C:\Program Files\Jaspersoft\js-studio-pro-6.2.1\features\jre.win32.win32.x86_64.feature_1.8.0.u74\jre\bin

• Run the following command to import the certificate
  keytool.exe –import –alias “ZENworks Reporting” –file “<path to the .cer you downloaded> -keystore ..\lib\security\cacerts –storepass “changeit”

• When prompted if you want to import the certificate choose to do so.

• JasperStudio should now trust your CA and be able to establish a connection to your ZENworks Reporting server.

• Launch JasperStudio. If this is the first time you've used it, it can be a little daunting. But don't worry, what we need to do here is pretty easy.

• 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..
  
  
  
  1. 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).
  
  
  
  1. 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. In my case it is using Microsoft SQL as you can see from my configuration:
  
  
  
  

• 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 you're ready to create your topic. Here's the secret I found out -- a topic can be nothing more than a JRXML report file with a Data Query, as long as you save it to the right place. So here's how you create your topic.
  
  
  
  1. Select File > New > Jasper Report.
  
  
• Pick any of the blank templates, like Blank Letter, then click Next.

• Pick a path and name for your report, then click Next.

• Pick your Data Adapter from the list.

• Now you should get the place where you can build your query. In the left pane you'll see all of the database tables. The right pane is where you can write the query. You can see mine below. 
  
  
  

• Notice in my query that all of my select fields include the keywords 'as' followed by a string in quotes. For instance zOSTarget.ProductName as "OS Name". The outputs of your query are going to be the objects that show up in the Ad Hoc editor so you want them to be friendly for anyone that's going to use them. By using 'as' I'm simply making sure that they are friendly. Also, I typically use something like the SQL Management Studio to build my query and get what I want and then just paste it here. Obviously, the magic here is figuring out the SQL query to get the data you want. If you can't figure it out, drop me a line at jason.blackett@microfocus.com or zen@microfocus.com and I'll be happy to see if I can help.

• Click Next.

• At the Fields list, just click >> to make all of the data set values into fields. The fields are what show up in the Ad Hoc Editor.

• Click Next.

• If your field had any aggregate calculations such as a SUM or a COUNT in them then you would need to specify how you wanted to group the data by selecting the field to group by. In my case I didn't need that, so I just clicked Next at the group by page.

• Click Finish.

• If you should have to make changes to the query, now that it is created, you can right-click the name of the report and select Edit Dataset and Query. If you do you get this page: 
  
  
  
  You can modify your query here. If you add or remove fields be sure to click the Read Fields button to get Fields created. You can use the Data Preview tab to check the output of your query to make sure you have what you want. Click OK to save any changes you make.

•  Now that you've got your data query returning what you want to report against, the next step is to save the report to your ZENworks Reporting server. To do this:
  
  
  
  1. Click File > Save to save the report.
  
  
• 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.

Alright, you've got your Topic! From this point on the rest of this solution shows you how you can now use your Topic to create a report. If you've created a report with the Domain before you'll find it pretty much the same. Here's what it looks like:

1. Fire up your favorite web browser and visit the ZENworks Reporting server page (typically its on TCP port 8443).

• Login as someone that can create reports.

• Select Create > Ad Hoc View. This will open the Select Data pop-up shown below. You should see your new topic in the list.
  
  
  
  

• Select your topic and then click OK.

• The standard Ad Hoc View editor gets opened. You should see the fields and measures corresponding to the fields you selected in your SQL query. You can now build the report the same way you would any other. You can also create custom fields or measures are required. In my report, I've created a Custom field called Patch Compliance Status that checks to see if the Critical Patch Percentage is 100. If so then that's what I've decided compliance means. If I had selected other data in the query I could have done things like including the number of outstanding patches, or including recommended patches in my compliance field calculation. Here's what mine looks like:
  
  
  

• As you can see I now have the compliance status, device name and last scan date in a tabular report. I can also filter by OS so that I can get a different view of the data. This is what the partner was looking for this week.

• Save the report and schedule it if desired.

So, just to recap, if you've got a report you're trying to run and the data is in the database in a format that ZENworks Reporting can read it (think not XML or JSON), then you now know everything you need to be able to make that information available, even if its not included in one of the published domains. This is also useful if you want to optimize your custom views to use your specific queries instead of those created by the domain when Ad Hoc auto-builds.

I hope you find this useful, I know for me it just simplified how I can get new information from the database to my customer when they need it.