Report Query Question

[Migration User]

I have recently been assigned management of the Client Management Suite for a new installation. We are attempting to build a report or query to display computers with 'Name' 'Discovery Date' and 'Location' to ensure devices are being assigned correctly. I have searched through the forums and am unable to find any suggestions and hope someone could point us in the right direction.

[A.K.M]

Here's the query I would use:

 

SELECT        dbo.vAllComputerInfo.Guid, dbo.vAllComputerInfo.Name AS [Computer Name], dbo.RMV_HW_AltirisAgent.[First Discovered], dbo.vAllComputerInfo.Location
FROM            dbo.vAllComputerInfo LEFT OUTER JOIN
                         dbo.RMV_HW_AltirisAgent ON dbo.vAllComputerInfo.Guid = dbo.RMV_HW_AltirisAgent.Guid

[sdmayhew]

x2 on the inner joins - never do those in asset management context - search on my name for a paper i published on using Altiris for Asset managment a couple years back. Still relevant 

 

What is your location key ? naming standard? ip range, Primary user location? I have posted a couple detailed user reports and detailed location reports 

 

 

 

[Alex Hedley]

*Updated JOINS

SELECT
  ri.[Guid] AS [AssetGuid]  
  ,ri.[Name] AS [AssetName]
  ,ri.[CreatedDate] AS [First Discovered]
  ,l.[Guid] AS [LocationGuid]
  ,l.[Name] AS [LocationName] 
FROM  
  vRM_Asset_Item ri  
  LEFT OUTER JOIN ResourceAssociation ra 
    ON ri.Guid = ra.ParentResourceGuid
    AND (ra.ResourceAssociationTypeGuid = '05de450f-39ea-4aae-8c5f-77817889c27c') --Location  
  LEFT OUTER JOIN vRM_Location_Item l 
    ON ra.ChildResourceGuid = l.Guid

 

First Discovered

DECLARE @ComputerGuid UniqueIdentifier = 'x'

SELECT vi.CreatedDate as 'First Discovered'
FROM vResource vr
LEFT OUTER JOIN vItem vi ON vi.Guid = vr.Guid 
WHERE vr.Guid = @ComputerGuid

 

Asset's Location

SELECT  
  ri.[Guid] AS [AssetGuid],   
  ri.[Name] AS [AssetName],
  l.[Guid] AS [LocationGuid],
  l.[Name] AS [LocationName] 
FROM  
  vRM_Asset_Item ri  
  LEFT OUTER JOIN ResourceAssociation ra 
    ON ri.Guid = ra.ParentResourceGuid
    AND (ra.ResourceAssociationTypeGuid = '05de450f-39ea-4aae-8c5f-77817889c27c') --Location  
  LEFT OUTER JOIN vRM_Location_Item l 
    ON ra.ChildResourceGuid = l.Guid