How do I create a CMDB Solution 6.5 custom report that uses a custom data class?
A custom report can be created to report on the values in a custom data class. A clone can be made of an existing report and then edited as well. This article describes how to perform these procedures.
Note: The examples in this article assume that you have completed the previous examples from the following related articles:
How to use CMDB Solution 6.5 to create a custom data class and associate it with a custom resource type
How to create a CMDB Solution 6.5 custom report that uses a custom data class
Custom Data Class SQL Information
Before continuing with instructions on how to create or modify a report, it is useful to understand how to directly access a custom data class in SQL. This information can be then used when creating SQL queries for custom reports, such as in the following examples.
In SQL, custom data classes are saved to new tables. These are named "Inv_<custom_data_class_name". For example, for the Camera Specifications custom data class, this will appear in the Altiris database as the table "Inv_Camera_Specifications" with the following fields: _id, ResourceGuid, and Megapixel Size. Note that the first two are added automatically, whereas the last is the name of the single field created in the earlier examples.
To query custom data classes in conjunction with its resource data classes, join the custom data class table to a resource table, such as the vasset table. For example:
select *, i.[name] from vasset va
join item i on i.guid = va._resourceguid -- joining to item table as item contains the name
left join inv_camera_specifications on va._resourceguid = inv_camera_specifications._resourceguid
where [asset type] = 'Cell Phone'
For more information on using SQL queries, please contact your Database Administrator for assistance.
Create a New Custom Report Using a SQL Statement
- To create a new custom report, in the Altiris Console, go to Reports > Reports.
- Right click on the Reports folder and choose New > Report.
- Type a value for the Report name field. For example, "My Custom Report".
- Select the Report type that you want. For example, choose Enter SQL Directly. Note: The remainder of these instructions assume that Enter SQL Directly was picked. For other report types, please refer to the references at the end of this article.
- In the SQL Statement box, enter the following:
select * from vasset where [asset type] = 'smart phone'
- Click on the Test button. The Test Report window should show the three smart phone assets previously created in article 48665.
- Click on the Close button.
- Click on the Finish button.
- Click on Run this report. Assuming that all examples were followed, the report displays the smart phone records that were displayed during step 6.
Create a Custom Report from a Cloned Report
- To create a custom report from a cloned report, in the Altiris Console, go to Reports > Reports > Assets and Inventory > Asset Management > Asset Type.
- Right click on the Assets by Type, Status, and Location report and choose Clone.
- Type a name for the custom report in the edit box.
- Click on the OK button.
- Click on Edit this report.
- Under Queries, click on the pencil icon to edit the query.
- The query can be edited to include or exclude data classes as needed. Note: This step assumes that you are familiar with using SQL. If not, please contact your database administrator for assistance in modifying SQL queries.
- Once the query has been modified, click on the Run button to test it.
- Click on the Finish button.
- Click on the Apply button.
- Click on Run this report. The report runs, displaying the same records that were displayed during step 8.
The information contained in this article was brief and only discussed basic custom report creation. For more information on creating custom reports, please refer to the Notification Server 6.0 SP3 Help guide, starting on page 97, the Advanced Report Builder guide, and the How-to: Guide on the Report Builder guide:
Using the Advanced Report Builder to Create Custom Reports
How-to Guide on the Report Builder