SMP
Reports
Asset's Location
Settings | Notification Server | Resource and Data Class Settings | Resource Associations | CMDB Assocation Types | 
Asset’s Location
| GUID | 05de450f-39ea-4aae-8c5f-77817889c27c |
|---|
Details
| From Type | Asset |
|---|---|
| To Type | Location |
Settings | Notification Server | Resource and Data Class Settings | Resource Types | Asset Types | Generic Asset Types | 
Asset
Settings | Notification Server | Resource and Data Class Settings | Resource Types | Organisational Types | 
Location
DECLARE @AssetGuid AS UniqueIdentifier = NULL --'00000000-0000-0000-0000-000000000000'
DECLARE @LocationGuid AS UniqueIdentifier = NULL --'00000000-0000-0000-0000-000000000000'
SELECT
ri.[Guid] AS [AssetGuid]
,ri.[Name] AS [AssetName]
--,rt.[Guid] AS [AssetTypeGuid]
--,rt.[Name] AS [AssetType]
,l.[Guid] AS [LocationGuid]
,l.[Name] AS [LocationName]
FROM
vRM_Asset_Item ri
INNER JOIN ResourceAssociation ra
ON ri.Guid = ra.ParentResourceGuid
AND (ra.ResourceAssociationTypeGuid = '05de450f-39ea-4aae-8c5f-77817889c27c') --Location
INNER JOIN vRM_Location_Item l
ON ra.ChildResourceGuid = l.Guid
--INNER JOIN ResourceType rt
-- ON rt.Guid = ri.ResourceTypeGuid
WHERE
(
(@AssetGuid IS NOT NULL AND ri.Guid = @AssetGuid)
OR
(@LocationGuid IS NOT NULL AND l.Guid = @LocationGuid)
)
Location Hierarchy
If you have a Hierarchy of Locations it's handy to add this in your Reports.
Just add the Path field.
SELECT Guid, Path
FROM dbo.fnAssetHierarchyTreeExcludeChildren('834BC951-D70F-48F4-9E8E-D7E32C68788D', NULL, 0x0, 0x0, 1)
AS h
Example
| UK\England\Newcastle |
|---|
query to build location hierarchy
https://www.symantec.com/connect/forums/query-build-location-hierarchy
Locations By Parent At Location
You may just wish to find the parent of a Location.
Settings | Notification Server | Resource and Data Class Settings | Resource Associations | CMDB Assocation Types | Location Hierarchy
| GUID | dc4689d9-1d2d-47cc-bf65-fd9437d08ed5 |
|---|
Details
| From Type | Location |
|---|---|
| To Type | Location |
Settings | Notification Server | Resource and Data Class Settings | Resource Types | Organisational Types | Location
Settings | Notification Server | Resource and Data Class Settings | Resource Types | Organisational Types | Location
DECLARE @ParentLocationGuid AS UniqueIdentifier = NULL
SELECT
li.[Guid]
,li.[Name]
,l.[Guid] AS ParentLocationGuid
,l.[Name] AS ParentLocationName
FROM
vRM_Location_Item li
INNER JOIN ResourceAssociation ra
ON li.[Guid] = ra.ParentResourceGuid AND ra.ResourceAssociationTypeGuid = 'dc4689d9-1d2d-47cc-bf65-fd9437d08ed5'
INNER JOIN vRM_Location_Item l
ON ra.ChildResourceGuid = l.[Guid]
WHERE
l.[Guid] = @ParentLocationGuid