→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
joled
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Tom_P
Lansweeper Employee
Lansweeper Employee
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

View solution in original post

2 REPLIES 2
joled
Engaged Sweeper
This is great. Thank you!!
Joe
Tom_P
Lansweeper Employee
Lansweeper Employee
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