Retired Computer Asset Report Creation

Products

IT Management Suite Asset Management Suite Inventory Solution

Issue/Introduction

Administrators need to generate a list of hardware targets that have been retired to verify license reclamation, clean up Active Directory syncs, or perform lifecycle audits, but the default computer views do not filter natively by asset status.

This article provides SQL query examples to locate, filter, and report on assets marked with a "Retired" status within the Symantec Management Platform (SMP) database. Use these queries to build custom reports or notification policies in IT Management Suite (ITMS) when monitoring decommissioned hardware assets.

Environment

ITMS 8.7.x, 8.8.x

Resolution

How It Works

In the ITMS database schema, an asset's status is not stored as a simple text column within the primary resource tables. Instead, it is managed via a Resource Association.

A computer or virtual machine resource is linked to a specific state item using the ResourceAssociation table. To find retired computers, the query must match the resource's GUID against the fixed GUID for the "Retired" status item, filtered by the specific Asset Status association type.

Asset Status Association Type GUID: 3028166F-C0D6-41D8-9CB7-F64852E0FD01
"Retired" Status Item GUID: 492C463B-AFA2-4DD6-AE73-6FD2C7B0E489

To deploy these queries as a functional report inside the Symantec Management Console, follow these steps:

Step 1: Create a New Custom SQL Report

Open the Symantec Management Console.
Navigate to Reports > All Reports.
Right-click the folder where you want to store the report (e.g., a custom folder or Custom Reports) and select New > Report > SQL Report.
In the upper-right corner of the new report panel, click Edit.

Step 2: Add the SQL Query

Choose one of the following queries based on your environment's reporting requirements.

--Example 1

-- setup a temporary table to hold Computer and VM data
SELECT * INTO #TempStatus
FROM RM_ResourceComputer
UNION
SELECT * FROM RM_ResourceVirtual_Machine

-- find systems with Retired resourceassociation set
select ts.[Guid] as _ItemGuid, ts.name, Status = 'Retired', ts.CreatedDate, ts.ModifiedDate
FROM #TempStatus ts
LEFT JOIN ResourceAssociation ra 
  ON ts.Guid = ra.ParentResourceGuid 
where ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'
and ts.Deleted = 0

-- drop the temp table
DROP TABLE #TempStatus


--Example 2

select vi.[guid] as _itemguid, vi.name as [Resource Name], ISNULL(i.Name, 'Active') as [Status] from vAssetResource vc

left join vItem vi on vc.Guid =vi.Guid

left join ResourceAssociation ra on vc.Guid = ra.ParentResourceGuid

and ra.ResourceAssociationTypeguid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'

left join vitem i on i.guid = ra.childResourceGuid

order by [Status]

--Example 3

select vc.[Guid] as _ItemGuid, vc.name as [Resource Name], ISNULL(i.Name, 'Active') as [Status] from Rm_resourcecomputer vc

left join ResourceAssociation ra on vc.Guid = ra.ParentResourceGuid

and ra.ResourceAssociationTypeguid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'

left join vitem i on i.guid = ra.childResourceGuid

union

select vc.[Guid] as _ItemGuid,vc.name, ISNULL(i.Name, 'Active') from RM_ResourceVirtual_Machine vc

left join ResourceAssociation ra on vc.Guid = ra.ParentResourceGuid

and ra.ResourceAssociationTypeguid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'

left join vitem i on i.guid = ra.ChildResourceGuid

 

--Example 4

--Trigger below query to get list of retired/disposed asset list:

select vresourceitem.[Name], Manufacturer, Model, [Serial Number], vresourceitem.guid    
from vasset         
join vresourceitem         
on vresourceitem.guid = vasset._resourceguid     
where status = 'retired' or status = 'disposed'