How to create a custom report for assets
search cancel

How to create a custom report for assets

book

Article ID: 178204

calendar_today

Updated On:

Products

IT Management Suite Inventory Solution Asset Management Solution

Issue/Introduction

 This white paper article provides general information and best practices on how to create a custom report for assets, such as for computers, users, etc.

Environment

ITMS 8.x

Resolution

Best Practices for Creating a Custom Report for Assets

  • Check if an out of box report is available that includes what is mostly needed. This can be used instead of making a custom report, or, can be cloned and then modified to include other fields. Asset reports can be found under the Reports button > All Reports > Service and Asset Management. For example, two commonly used asset reports are Assets by Type, Status, Department, Cost Center and Location, and General Asset Report.
     
  • If a new custom report is needed to be created, it is recommended to create a custom SQL report as it allows greater freedom over creating one using the Report Builder UI. Best practices for creating a custom SQL report include:

    • Create the SQL script first in the Microsoft SQL Server Management Studio to be sure it works as expected. WARNING: If it takes the SQL script running in the Microsoft SQL Server Studio five minutes or longer to complete, it is not recommended to use this as the basis of a custom report. This will result in the custom report timing out after five minutes and may cause poor Notification Server performance while it tries to finish! While the timeout can be adjusted by changing several settings, such as the database's timeout found by going to the Settings button > Notification Server > Database Settings, this is not recommended because doing so may negatively impact other areas of the Notification Server.
    • If the user is unfamiliar with how to create a SQL script, please refer to the DBA or a co-worker who can assist with this. Please Note: Symantec Technical Support is unable to provide the customer with extensive assistance in writing and troubleshooting their SQL scripts, such as for use with a custom report. Please instead contact Symantec Consulting Services http://www.symantec.com/it-consulting-services for this level of assistance.
    • Always include the Guid or _ResourceGuid field of the asset and refer to it as '_ItemGuid'. This enables the drill-down tab functions of right click and create/edit to work. Without these, the user will not be able to perform these functions with a custom report.
    • A SQL script has the ability to not only provide specific fields but also limit what records are returned, such as by using the WHERE clause. For example:

      -- Show only computer names that have names that start with "Test". (All other fields from vComputer are ignored.)
      SELECT Guid '_ItemGuid', Name
      FROM vComputer
      WHERE Name LIKE 'Test%'
       
    • Don't include the USE statement. Doing so is redundant as a custom report's SQL script automatically knows to access the Notification Server's database, which by default is named Symantec_CMDB.
    • WARNING: Don't include statements that modify data, such as UPDATE, INSERT, DELETE, DROP and TRUNCATE! Doing so is unsupported and may result in severe issues, timeouts or data loss!
    • Use tables and views that contain commonly used data classes. For example:

      • vComputer. This view is used for computers with basic inventory.
      • vAsset. This view is used for computers with CMDB information.
      •  vUser. This view is used for users.
         
        For example:

        -- Show computers by name from the vAsset view including some of their CMDB information.
        SELECT vi.Guid '_ItemGuid', vi.Name 'Computer Name', im.Manufacturer, im.Model, va.[Serial Number], va.Barcode, va.[System Number]
        FROM vAsset va
        JOIN vItem vi
        ON va._ResourceGuid = vi.Guid
        LEFT JOIN Inv_Manufacturer im
        ON im._ResourceGuid = va._ResourceGuid
        WHERE va.[Asset Type] = 'Computer'
        OR va.[Asset Type] = 'Virtual Machine'
        ORDER BY Name

        -- Show all users by name from the vUser view.
        SELECT Guid '_ItemGuid', *
        FROM vUser
        ORDER BY Name
         
    • It is easier to use SELECT going FROM a resource table or view instead of a secondary table or view by using the correct JOIN type. For example:

      -- Show computers by name from the vComputer view including their serial numbers from the Inv_Serial_Number secondary table.
      SELECT Guid '_ItemGuid', *, [Serial Number]
      FROM vComputer vc
      LEFT JOIN Inv_Serial_Number sn
      ON sn._ResourceGuid = vc.Guid
      ORDER BY Name

      -- Show serial numbers from the Inv_Serial_Number including their computers from the vComputer "secondary" view.
      SELECT _ResourceGuid '_ItemGuid', *
      FROM Inv_Serial_Number sn
      JOIN vComputer vc
      ON vc.Guid = sn._ResourceGuid

      Notice that the second SQL script's output only lists computers that have serial numbers. The output of both SQL scripts are controlled by the JOIN types:

      • A JOIN statement only shows a record if it has matches from both tables/views. For example, if the first SQL script used a JOIN instead of a LEFT JOIN, then only those computers that have serial numbers would be shown, not every computer whether it has a serial number or not.
      • A LEFT JOIN or RIGHT JOIN shows a record if it has a match from the FROM table/view regardless of if the joined table/view has a match or not. For example, the first SQL script uses a LEFT JOIN to show all computers whether they have a serial number or not.
         
    • Other useful tables/views that can be included are:

      • vItem. This view includes nearly every record type in Altiris and can be used to derive names of records when their normal table/view does not include these, such as the vAsset view.
      • ResourceAssociation. This table includes assets that have resource associations such as Asset Owner, Cost Center, Department, Location and Status. The ParentResourceGuid is the asset's GUID (such as for a computer), the ChildResourceGuid is the associated asset's GUID (such as for a location) and the ResourceAssociationTypeGuid is the resource type that is being associated to (such as a GUID that indicates it is a location asset type, not a specific location). For example:

        -- Show assets by name from the vAsset view including if they have an asset owner (which can be a user or a department).
        SELECT va._ResourceGuid '_ItemGuid', vi.Name 'Asset', ISNULL(vi2.Name, '') 'Asset Owner'
        FROM vAsset va
        JOIN vItem vi
        ON vi.Guid = va._ResourceGuid
        LEFT JOIN ResourceAssociation ra -- Remove LEFT to only see assets if they have an Asset Owner.
        ON ra.ParentResourceGuid = va._ResourceGuid
        AND (ra.ResourceAssociationTypeGuid = 'ED35A8D1-BF60-4771-9DDE-092C146C485A' OR ra.ResourceAssociationTypeGuid = '1466E770-4413-4517-A89D-6599B8A7F144') -- Check for user type and department type GUIDs.
        LEFT JOIN vItem vi2
        ON vi2.Guid = ra.ChildResourceGuid
        ORDER BY Name

        Additional information about how to use resource associations can be found in the following article:

        How to write a SQL script that can reference asset associations, such as Asset Owner, Location or Department
        http://www.symantec.com/business/support/index?page=content&id=HOWTO85139
         
    • Sometimes it's not clear what table or data class to use for specific information. For example, what table and data class is the CMDB System Number found in? If the user didn't know this was in the Inv_Identity.[System Number] table and data class, there are a few ways to track this information down:

      • Clone a report that includes this information and then look at the tables and data classes being referenced in its SQL script.
      • Look through an asset's Resource Manager's Inventory section for the data class name.
      • Use a SQL script to search for the table and data class based on a specific value. For example, if a known system number has the value of "ABC123", this can be used to search through the entire Symantec_CMDB database to find which tables and data classes this is found in. The user would then need to ascertain which matches to use, however, as it may not necessarily be clear which is the correct table and data class. To help with this, a SQL script that can search the Symantec_CMDB database has been provided by Symantec Technical Support and can be found in the following article:

        How to search a Microsoft SQL database for a data value
        http://www.symantec.com/business/support/index?page=content&id=HOWTO61242
         
  • Custom resources and custom data classes can also be used in a custom report. The following article describes how to do this:

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

How to Create a Custom SQL Report From Scratch for Assets

  1. Open a Symantec Management Platform Console.
  2. Click on the Reports button and then select All Reports.
  3. Click on to expand the folder Service and Asset Management.
  4. Right click on the folder where the custom report will be saved in, such as Assets, and select New > Report > SQL Report.
  5. Click on the New SQL Report name field and change this to a new name. For example, "Show assets that have associated assets/resources custom report".
  6. In the Parameterized Query tab's field, remove the default SQL script.
  7. Type or paste the SQL script to use. For example:

    SELECT vi.Guid '_ItemGuid', vi.Name 'Asset', vi2.Name 'Association Asset/Resource Type', vi3.Name 'Association Asset/Resource Name'
    FROM vAsset va
    JOIN vItem vi
    ON vi.Guid = va._ResourceGuid
    LEFT JOIN ResourceAssociation ra
    ON va._ResourceGuid = ra.ParentResourceGuid
    JOIN vItem vi2
    ON ra.ResourceAssociationTypeGuid = vi2.Guid
    JOIN vItem vi3
    ON ra.ChildResourceGuid = vi3.Guid
    ORDER BY 1, 2
     
  8. Click on the Save Changes button. Note: If a SQL syntax error occurs, such as an invalid command or invalid table or data class reference, the errors "An error occurred on the page. Please check the log for more details." and "The DataSource is not in a runnable state." will occur. Check the Altiris Log Viewer on the Notification Server for more information about what went wrong and re-test the SQL script in the Microsoft SQL Server Management Studio to be sure it is in working order. Continue to make any necessary changes until the custom report can be saved successfully with the SQL script.
  9. If the expected fields and records do not appear, re-edit the custom report and modify the SQL script until they do.
  10. Add additional report functionality as desired such as drop-down selection lists, right click menus, etc. Information about how to add functionality to a custom report can be found in other articles such as the following:

    How to enable double click editing of assets in a custom report
    http://www.symantec.com/business/support/index?page=content&id=HOWTO83282

    How to use drop-down lists in a custom report
    http://www.symantec.com/business/support/index?page=content&id=HOWTO83270

    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


Related Article

Unable to right click or create/edit certain Configuration Items reports, such as Services
http://www.symantec.com/business/support/index?page=content&id=TECH205844

Powered by Wolken Software