How to create a report for Retired computers in NS 7

book

Article ID: 179626

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
What is the query to create a report for retired computers in NS 7

Answer

SELECT * INTO #TempStatus
FROM RM_ResourceComputer
UNION
SELECT * FROM RM_ResourceVirtual_Machine

SELECT ts.Name, 'Status' =
  CASE
    WHEN ra.ResourceAssociationTypeGuid IS NULL THEN 'Active'
    WHEN ra.ResourceAssociationTypeguid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01' AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489' THEN (
      SELECT [Name] FROM Item
      WHERE Guid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489')
    ELSE 'Unknown'
  END
FROM #TempStatus ts
LEFT JOIN ResourceAssociation ra ON ts.Guid = ra.ParentResourceGuid 
 
DROP TABLE #TempStatus

 

Here are two more examples that you could use to build a Report:

--Example 1

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