List all assets and the locations that are located in. Quickly find which location has which device on it by running this report. No longer will you have to spend valuable time looking for switches, servers or other devices when they are in need of repairs, maintenance or just some hardware changes.
Assets and Their Location Map Query
Select Top 1000000 tblAssets.AssetName As Asset,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysAssetRelationTypes.Name As Relation,
tblAssets1.AssetID,
tblAssets1.AssetName
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Inner Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetRelationTypes.Name = 'is located in' And tblState.Statename =
'Active'
Order By tblAssets.IPNumeric,
tblAssets.Domain,
Asset
