‎12-14-2015 07:05 PM
Solved! Go to Solution.
‎12-17-2015 02:10 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
Coalesce(tblAssets.Username, MonitorQuery.Username) As Username,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress As [IP address],
Coalesce(tsysIPLocations.IPLocation, MonitorQuery.IPLocation) As
[IP location],
tblState.Statename As State,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Date],
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.Custom1 As Asset#,
tblAssetCustom.Custom2 As [Deploy Date],
Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
End As [not affected by cleanup options],
tsysAssetTypes.AssetTypeIcon10 As icon,
LocationQuery.Location
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select Top 1000000 tblAssetRelations.ChildAssetID,
tblAssets.AssetName As Location
From tblAssetRelations
Inner Join tblAssets On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Where tsysAssetRelationTypes.Name = 'is located in') LocationQuery
On LocationQuery.ChildAssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
tblAssets1.Username As Username,
tsysIPLocations.IPLocation
From tblAssetRelations
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ParentAssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets1.IPNumeric
And tsysIPLocations.EndIP >= tblAssets1.IPNumeric
Inner Join tblAssets On tblAssetRelations.ChildAssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetRelationTypes.Name = 'connected to' And
tsysAssetTypes.AssetTypename = 'monitor') MonitorQuery
On MonitorQuery.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName
‎12-17-2015 02:10 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
Coalesce(tblAssets.Username, MonitorQuery.Username) As Username,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress As [IP address],
Coalesce(tsysIPLocations.IPLocation, MonitorQuery.IPLocation) As
[IP location],
tblState.Statename As State,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Date],
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.Custom1 As Asset#,
tblAssetCustom.Custom2 As [Deploy Date],
Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
End As [not affected by cleanup options],
tsysAssetTypes.AssetTypeIcon10 As icon,
LocationQuery.Location
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select Top 1000000 tblAssetRelations.ChildAssetID,
tblAssets.AssetName As Location
From tblAssetRelations
Inner Join tblAssets On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Where tsysAssetRelationTypes.Name = 'is located in') LocationQuery
On LocationQuery.ChildAssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
tblAssets1.Username As Username,
tsysIPLocations.IPLocation
From tblAssetRelations
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ParentAssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets1.IPNumeric
And tsysIPLocations.EndIP >= tblAssets1.IPNumeric
Inner Join tblAssets On tblAssetRelations.ChildAssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetRelationTypes.Name = 'connected to' And
tsysAssetTypes.AssetTypename = 'monitor') MonitorQuery
On MonitorQuery.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now