Create a custom report or a SQL script that can reference asset associations, such as Status, Asset Owner, Location or Department

Products

• Asset Management Solution
• IT Management Suite

Issue/Introduction

How to create a custom report or a SQL script that can reference asset associations, such as Status, Asset Owner, Location or Department.  The information in this article describes how to use asset associations in a custom report. Several example SQL scripts are included in the body of this article as well as as attachments.

Environment

ITMS 8.x

Resolution

How to add associations into a custom SQL report

1. In the Symantec Management Console, click on the Reports button > All Reports.
2. Click on to expand the folders to where the existing custom SQL report is at, or clone an out of box report, or create a new SQL report.
3. Click on the custom report.
4. Click on the Edit button.
5. Click on the Parameterized Query tab.
6. Reviewing the SQL script that may be there, it will likely reference a primary table that includes assets, along with then joining secondary tables for relational data. For example, this may look similar to the following:
   
   SELECT
     vi.Name 'Computer',
     COALESCE(isn.[Serial Number], '') 'Serial Number'
FROM vAsset va -- Where the primary asset data will come from.
JOIN vItem vi
ON vi.Guid = va._ResourceGuid
LEFT JOIN Inv_Serial_Number isn -- Where secondary data comes from, in this case, the Serial Number.
ON isn._ResourceGuid = va._ResourceGuid
ORDER BY 1
    
7. Once the user has decided what tables and data classes to join for the associations (as described throughout this article, they'll need to next add in a JOIN to these tables and add the data class to the SELECT statement. For example, here is the same SQL script as above, but with the addition of accessing the ResourceAssociation table to find if the computers have a Status or not. The Status' name is then added to the SELECT statement.
   
    
8. Once finished, click on the Save Changes button.

Asset tables and views

To begin, an asset table or view needs to be selected on which to display assets from:

• vComputer - Includes computer names and some basic inventory data, such as operating system information. Other similar views exist, such as vMonitor, for specific asset types.
• vAsset - Includes all CMDB assets. This can be filtered by asset type or can be used to display all types. This view includes CMDB data as opposed to vComputer, which mostly has basic inventory data. vAsset, however, does not include the asset's name, and so must be JOINed to another table or view that has this.
• RM_ResourceComputer - Includes computer names and basic information. As this is an actual table, this can be used to make changes or deletions (but is not recommended to do so) if necessary, whereas the first two views cannot do this. Other similar table names exist, such as RM_ResourceVirtualMachine, for specific asset types.
• vUser - Includes user names and their information.

The following are some basic SQL script examples of how to use each of the above:

-- Script 1: Display computers and their basic inventory data.
USE Symantec_CMDB
SELECT *
FROM vComputer
ORDER BY 2

-- Script 2: Display computers, join vItem to derive their name and display the CMDB Serial Number value.
USE Symantec_CMDB
SELECT vi.Name, va.[Serial Number]
FROM vAsset va
JOIN vItem vi
ON vi.Guid = va._ResourceGuid
ORDER BY 1

-- Script 3: Display raw computer data from a computer table.
USE Symantec_CMDB
SELECT *
FROM RM_ResourceComputer
ORDER BY 2

-- Script 4: Display users and their information.
USE Symantec_CMDB
SELECT *
FROM vUser
ORDER BY 2

Resource associations

Most assets have several resource associations going to a foreign key or association or reverse association. For example, a computer asset type includes some of the following out of box associations:

• Asset's Status
• Depreciation
• Associated Assets
• Cost Centers
• Dependent CIs (Reverse), which are Services
• Location
• Asset Owners

Generally, associations are recorded in the ResourceAssociation table. These are recorded by association type. Unfortunately, the ResourceAssociation table only includes GUIDs, and so it is not simple to look at to see what's associated to what. A JOIN must be performed to another table or view that has a name of the asset or association.
 
Reference ResourceAssociation to compare the parent GUID (the asset) to check for an association type and then display the association. Performing a SELECT on the table will result in the following columns:

• ResourceAssociationTypeGuid - What the resource type is, for example, Asset's Status, Location or Asset Owners. This GUID can be matched up for reference in another table or view to verify what it belongs to. For example:
  
  ED35A8D1-BF60-4771-9DDE-092C146C485A
  
  Refers to Asset User Owners, as verified in vItem by:
  
  USE Symantec_CMDB
SELECT *
FROM vItem
WHERE Guid = 'ED35A8D1-BF60-4771-9DDE-092C146C485A'
  
  Or,
  
  3028166F-C0D6-41D8-9CB7-F64852E0FD01
  
  Refers to the Asset's CMDB set Status.
   
• ParentResourceGuid - The asset's GUID. If a reverse association record is being evaluated, however, the asset's GUID will actually be in the ChildResourceGuid (below), and the association's GUID here.
• ChildResourceGuid - The association's GUID. For example, for an Asset Owner, this can be a user or department's GUID.
• CreatedDate - When the association was made.

The following example SQL script can be used to see which asset owners are associated to Computer assets:

USE Symantec_CMDB
SELECT va._ResourceGuid 'Guid', vi.Name 'Computer', 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')
LEFT JOIN vItem vi2
ON vi2.Guid = ra.ChildResourceGuid
WHERE va.[Asset Type] = 'Computer'
OR va.[Asset Type] = 'Virtual Machine'
ORDER BY 2

The Status association

The Status can be checked in SQL in several places. As noted, the ResourceAssociation table is where CMDB Status is saved to. Also, vAsset can be checked. The following are example scripts of how to evaluate these tables:

-- Check ResourceAssociation for CMDB Status. This does not also show implied Active, however, as the next SQL script does.
USE Symantec_CMDB
SELECT rc.Name 'Computer', vi.Name 'Status'
FROM RM_ResourceComputer rc
LEFT JOIN ResourceAssociation ra
ON ra.ParentResourceGuid = rc.Guid
JOIN vItem vi
ON vi.Guid = ra.ChildResourceGuid
WHERE ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
ORDER BY rc.Name

-- Check vAsset to see a computer's Status. This shows CMDB and implied Active Status.
USE Symantec_CMDB
SELECT Name, Status, COALESCE([Serial Number], '') 'Serial Number', COALESCE([System Number], '') 'System Number', COALESCE(Barcode, '') 'Barcode'
FROM vAsset va
JOIN vItem vi
ON vi.Guid = va._ResourceGuid
WHERE [Asset Type] = 'Computer'
OR [Asset Type] = 'Virtual Machine'
ORDER BY Name

The following is a more elaborate SQL script to show CMDB and implied Active:

USE Symantec_CMDB
SELECT va._ResourceGuid, vi.Name 'Computer', COALESCE(vi2.Name, '') 'CMDB Status', 'Implied Status' =
CASE
  WHEN vi2.Name IS NULL THEN 'Active' ELSE vi2.Name
END
FROM vAsset va
JOIN vItem vi
ON vi.Guid = va._ResourceGuid
LEFT JOIN ResourceAssociation ra
ON ra.ParentResourceGuid = va._ResourceGuid
AND ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
LEFT JOIN vItem vi2
ON ra.ChildResourceGuid = vi2.Guid
WHERE va.[Asset Type] = 'Computer'
OR va.[Asset Type] = 'Virtual Machine'
ORDER BY 2

Custom associations

If custom associations are used, these can also be found in the ResourceAssociation table. The ResourceAssociationTypeGuid must be known, just as any other type, to reference in a SQL script. A way to find this it to look for the name of the custom association in the vItem view, for example:

USE Symantec_CMDB
SELECT *
FROM vItem
WHERE Name = '<name of the custom association>'

If this doesn't return the expected record and its GUID, another way is to look through the ResourceAssociation table after a custom association is added to an asset. Then, check the ParentResourceGuid for the asset's GUID, and look at the last records modified. One of these should be the record that includes the custom association, which can be double checked by the above SQL script with a small modification, in the vItem table:

USE Symantec_CMDB
SELECT *
FROM vItem
WHERE Guid = '<guid from the Resource Association table for the ResourceAssociationTypeGuid field>'

One-to-many associations

Some associations are one-to-one, such as the Asset's Status or Location. Others are one-to-many, such as Associated Assets. For one-to-many, these may need to be viewed separately from a primary asset SQL script, as they can result in many rows for the same asset. These can result in confusion in reports or issues in an import rule if injudiciously added to a primary asset SQL script. SQL scripts to these may need to also reference other tables or views as well as the asset tables or views, or may not even need to access ResourceAssociation. One-to-many associations may also result in confusion when looking at the finished report, as the computer they refer to will appear more than once, which may be thought of as duplicate computers, which they are not.

The following is an example of how to view just an asset's cost items:

USE Symantec_CMDB
SELECT vi.Name 'Computer Name', vi2.Name 'Type', iad.[Cost Id], ici.Date, ici.Amount, ici.Status, ici.Description, ipoi.[Order Number]
FROM Inv_Cost_Items ici
LEFT JOIN vAsset va
ON ici._ResourceGuid = va._ResourceGuid
LEFT JOIN vItem vi
ON va._ResourceGuid = vi.Guid
LEFT JOIN vItem vi2
ON ici.Type = vi2.Guid
LEFT JOIN Inv_Accounting_Code_Details iad
ON ici.[Accounting Code] = iad._ResourceGuid
LEFT JOIN Inv_Purchase_Order_Information ipoi
ON ici._ResourceGuid = ipoi._ResourceGuid
WHERE va.[Asset Type] = 'Computer'
ORDER BY 1

Sample custom report

To use any new associations in an existing custom report. the user needs to add the appropriate LEFT JOIN or JOIN to the right area in the existing SQL script for that report. It is recommended to find an out of box report that is nearly what the user wants, clone that and then edit its SQL script to make these inclusions.

A sample custom report is also attached to this article. This is a customized "Assets by Type, Status, Department, Cost Center and Location" report that includes an asset's Barcode and Asset Owner and documents how these associations were added. To use this, save the report file to the Symantec Management Platform server. Then in a Symantec Management Platform Console, click on Reports > All Reports. Right click on the folder to import the custom report to and then click on > Import.

Additional Information

178204 Creating a custom report for assets

180714 Understanding how a computer's Status works

155139 Import Rule fails to set or removes Asset Owner or Cost Center