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

Article:HOWTO83493  |  Created: 2013-02-05  |  Updated: 2014-06-17  |  Article URL http://www.symantec.com/docs/HOWTO83493
Article Type
How To



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 in, such as Service and Asset Management > Assets.
    b. Right click on the folder for where the custom report will be created in 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. Symantc Consulting Services at http://www.symantec.com/it-consulting-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
    http://www.symantec.com/business/support/index?page=content&id=HOWTO83282

    If more then 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
    http://www.symantec.com/business/support/index?page=content&id=HOWTO83270

    Right click menus many also not work initially. The following article describes how to add these:

    Right click menu fails to work when using a custom report or custom resource
    http://www.symantec.com/business/support/index?page=content&id=TECH176318
     
  6. When finished making changes to the custom report, click on the Save Changes button. If errors occur while trying to save the custom report, refer to the following article:

    Errors occur while editing a cloned or custom report
    http://www.symantec.com/business/support/index?page=content&id=TECH200851


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
http://www.symantec.com/business/support/index?page=content&id=HOWTO61242

Related Articles

How to add columns or tables to a custom SQL report
http://www.symantec.com/business/support/index?page=content&id=HOWTO84069

How to create a custom resource
http://www.symantec.com/business/support/index?page=content&id=HOWTO83472

How to create custom data classes
http://www.symantec.com/business/support/index?page=content&id=HOWTO83473

Creating and modifying custom Notification Server reports
http://www.symantec.com/business/support/index?page=content&id=HOWTO62993

Related Legacy Article

How to create a CMDB Solution 6.5 custom report that uses a custom resource and custom data classes
http://www.symantec.com/business/support/index?page=content&id=HOWTO10517




Article URL http://www.symantec.com/docs/HOWTO83493


Terms of use for this information are found in Legal Notices