Creating a custom report for assets

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. 
  • 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
       
  • 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:
       
• Custom resources and custom data classes can also be used in a custom report. 
   

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.