How to add pinned location to a report

[joled]

Greetings,
I am a newbie and I don't know the system well enough to do what I am looking to do. I have defined locations (IT, Marketing ...). I have pinned locations to some assets. I created a report to give me all Windows systems with the query below. I now want to add the location that was pinned to each asset so when I look at the report I can see which have not been given a location.

How can I add the pinned location to the code below?

Thanks in advance.
Joe



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.OScode
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename = 'Windows'
Order By tblAssets.AssetName

[joled]

This is great. Thank you!!
Joe

[Tom_P · Accepted solution]

Hi,

As the location type (Location, Map, IP, ADLocation) was not defined, we've altered your SQL so that all 4 the locations are returned. The locations that are not needed can of course be removed from the query.

Select Top 1000000 tblAssets.AssetID,
	tblAssets.AssetName,
	tsysAssetTypes.AssetTypename,
	tsysAssetTypes.AssetTypeIcon10 As icon,
	tblAssets.Username,
	tblAssets.IPAddress,
	tblAssets.Lastseen,
	tblAssets.Lasttried,
	tblAssets.OScode,
	tblAssetCustom.Location as assetLocation,
	tsysIPLocations.IPLocation,
	tblADComputers.Location As ADLocation,
	mapLocation.AssetName
From tblAssets
	Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
	Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
	Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
	Left Join tblADComputers on tblADComputers.AssetID = tblAssets.AssetID
	Left Join (Select Top 1000000 tblAssets.AssetID As AssetID, tblAssets1.AssetName From tblAssets
				  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
				Where tsysAssetRelationTypes.Name = 'is located in') as mapLocation on mapLocation.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename = 'Windows'
Order By tblAssets.AssetName