You should be able to write a report to display that information and then use the "Pick Report" icon to adjust your view of that location or any asset for that matter, to include the information in your asset. 

Thank you for your answer,

The problem is that I am a bit lazy and was wondering if somebody hadn't writen this report already...

Best regards,

David

I might be able to do it if all your asset locations have one parent location, but I'm still not all that great at conditional join type things in SQL.

Is there a specific asset report you're looking at or is it all of them in general?

Hello,

Thank you for your answer.
I think it is a fairly common report that a lot of people may use.

In my customer's case, we have a lot of locations that have the same name (like 'first floor', 'north aisle", etc.), and could only be distinguished in the reports by the name of their direct parent or even second level parent.

So I was thinking of something like:
assetname | location | parent 1 | parent 2 | parent 3
with a "blank if null" option.

Best regards,

David

Here is some SQL that may put you on the path to what you need.

 

-- This query returns the parent-child associations 
-- for Location

USE Symantec_CMDB

SELECT

i.Name AS 'Association Type', --i.Guid,

vri1.Name AS 'Parent',

vri2.Name AS 'Child', vri2.Guid

FROM

ResourceAssociation ra

JOIN Item i ON ra.ResourceAssociationTypeGuid=i.Guid

JOIN vResourceItem vri1 ON ra.ParentResourceGuid=vri1.Guid

JOIN vResourceItem vri2 ON ra.ChildResourceGuid=vri2.Guid

WHERE

i.Guid = 'DC4689D9-1D2D-47CC-BF65-FD9437D08ED5' 
-- Association type Guid for Location Hierarchy

Sorry, I just realized you are still on version 6.5 so I don't know if the code will work.  For those that are on version 7.0  here is a refinement.

-- In the ResourceAssociation the Guid for the asset's location 
-- is in the ParentResourceGuid field and the parent location's 
-- Guid is in the ChildResoureGuid field (in other words it seems
-- backwards with the parent being the child and vice versa).

select  va.[Asset Type],
 i.Name,
 va.Status,
 va.Manufacturer,
 va.Model,
 --loc.Location,
 rtrim(loc.[Location]) + '/' + vloc.[Name] as 'Location',
 va.[Serial Number],
 va.[System Number],
 va.[Barcode],
 va.[Last Barcode Scan]
    
from vAsset va
join vItem i
  on va._ResourceGuid = i.Guid
left outer join vAssetLocation loc
   on va._ResourceGuid=loc._AssetGuid
left outer join ResourceAssociation ra
  on loc._Location = ra.ParentResourceGuid
left outer join vLocation vloc
  on vLoc._ResourceGuid = ra.ChildResourceGuid

-- This ResourceAssociationTypeGuid is for the 
-- ChildLocation to ParentLocation association
WHERE 
    ra.ResourceAssociationTypeGuid = 'DC4689D9-1D2D-47CC-BF65-FD9437D08ED5'

Order by
 [Asset Type],
 [Status],
 i.[Name]
 

Thanks Sandy, you are the best!!