Creating a report for Retired computers

Products

• IT Management Suite
• Asset Management Suite
• Inventory Solution

Issue/Introduction

Here is a sample SQL Query that will find Retired computers, that can be used in a SQL Report

Environment

ITMS 8.x

Resolution

Here are three sample queries that could be used:

--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