Create a custom report that uses a custom resource and custom data classes

Products

• Asset Management Solution
• Client Management Suite
• IT Management Suite

Issue/Introduction

How to create a custom report that uses a custom resource and custom data classes?

Environment

ITMS 8.x

Resolution

When a custom resource is created, an out-of-box generic report is automatically used for it. The user, however, will often wish to make a more specific custom report that leverages all of the custom resource's data classes. This article describes how to do this.

1. In a Symantec Management Platform Console, click on the Reports button > All Reports.
2. Depending on if a custom report is to be created, has already been created, or an out-of-box report is to be cloned, follow these sub-steps.
   
   If a custom report is to be created:
   
   a. Click to open the folders for open where the custom report will be created, such as Service and Asset Management > Assets.
   b. Right-click on the folder where the custom report will be created and choose New > SQL > Report > SQL Report.
   c. In the report name field, enter the name of the custom report.
   
   If an out-of-box report is to be cloned:
   
   a. Click to open the folders for open where the out-of-box report that will be cloned is located in such as Service and Asset Management > Assets.
   b. Right-click on the out-of-box report and choose Clone.
   c. In the report name field, enter the name of the custom report.
   d. Click on the OK button.
   e. Click on the Edit button.
   
   If a custom report has already been created:
   
   a. Click to open the folders for open where the custom report was created at.
   b. Click to select the custom report.
   c. Click on the Edit button.
    
3. In the Parameterized Query tab, remove the initial SQL script.
4. Enter a custom SQL script into the Parameterised Query tab. Depending on if this is referencing a custom resource and/or custom data classes, various JOINS or LEFT JOINS will need to be used to reference the corresponding associated tables. Please Note: If you are unsure how to create your own SQL script, please contact your DBA or co-workers who can help do this. While Symantec Technical Support can help customers use the out-of-box Altiris products, they are unable to create or provide extensive training or troubleshooting for customers that require SQL assistance, such as for writing SQL scripts such as for use with a custom report. Symantec Professional Services provides this level of assistance.
   
   When the custom resource is created, a custom view for it is also added. This is "vRM_<custom_resource_name>". For example, if "Docking Stations" is the custom resource name, then the view name would be "vRM_Docking_Station", with underscores "_" in place of spaces. This can be used in a SQL script, for example:
   
   SELECT cr.Guid, vi.Name
FROM vRM_<custom_resource_name> cr
JOIN vItem vi
ON vi.Guid = cr.Guid
   
   The following more elaborate SQL script lists custom resources and custom data classes. The same SQL script can be used for custom resources, out of box resources, custom data classes or out-of-box data classes. Note: The user must supply the values for the variables denoted by the lesser than and greater than symbols "<>".
   
   SELECT DISTINCT enim.ItemGuid 'Guid', i.Name 'Custom Resource Name', isn.[Serial Number], ib.Barcode, ic.TempCam, vu.[Given Name] + ' ' + vu.Surname 'Asset Owner'
/*
The SELECT statement returns the following values for each custom resource record:

- The custom resource's GUID as found in the Evt_NS_Item_Management table.
- The custom resource's Name as found in the Item table.
- The custom resource's CMDB Serial Number as found in the Inv_Serial_Number table.
- The custom resource's Barcode Number as found in the Inv_Barcode table.
- The custom resource's custom data class <custom_data_class_name>, as found in the custom data class' table <inv_custom_data_class_name>.
- The custom resource's asset owner (if assigned) association, as found in the ResourceAssociation table and vUser view.
*/
FROM Evt_NS_Item_Management enim
JOIN Item i
ON enim.ItemGuid = i.Guid
LEFT JOIN Inv_Barcode ib
ON enim.ItemGuid = ib._ResourceGuid
LEFT JOIN Inv_Serial_Number isn
ON enim.ItemGuid = isn._ResourceGuid
LEFT JOIN <custom_data_class_name> ic -- Change "<custom_data_class_name> to the name of the custom data class table, such as Inv_Camera_Details.
                                      -- The custom data class table name will always be in the format of Inv_<custom_data_class_name>.
ON enim.ItemGuid = ic._ResourceGuid
LEFT JOIN ResourceAssociation ra
ON enim.ItemGuid = ra.ParentResourceGuid
AND ra.ResourceAssociationTypeGuid = '<resource_association_type_guid>' -- Change <resource_association_type_guid> to the resource association type
                                                                        -- GUID found by referencing this in the ResourceAssociation table. For
                                                                        -- example, 'ED35A8D1-BF60-4771-9DDE-092C146C485A' is for a user association.
LEFT JOIN vUser vu
ON ra.ChildResourceGuid = vU.Guid
WHERE ResourceTypeGuid = '<custom_resource_type_guid>' -- Change <custom_resource_type_guid> to the custom resource type GUID found by referencing
                                                       -- this in the ResourceType table.
ORDER BY i.Name
    
5. New custom reports do not automatically enable editing or right-clicking of records (depending on the version of the Symantec Management Platform Console this varies slightly). This will result in the user not being able to edit, drilldown, delete, etc. records from the custom report. This is because the Drilldown tab of the custom report must be manually configured to allow these processes to occur. The following articles discuss how to configure a custom report's functionality:
   
   Double-clicking (editing) resources in a custom report does not work initially. This functionality must be manually added. The following article describes how to do this:
   
   How to enable double-click editing of assets in a custom report
   
   If more than one resource type is needed to be used by the report, a drop-down list can be added. The following article describes how to do this:
   
   How to use drop-down lists in a custom report 

Note: In some cases, the user may need to add additional resources and data classes, but may not be sure what tables these are found in. An easy way to determine this is to search the database for a specific value. This would then indicate which possible tables and data classes the values are in for the user to then determine which should be used in the custom report. The following article includes a SQL script that can search the database to help with this process.

How to search a Microsoft SQL database for a data value