‎05-01-2017 10:58 PM
Solved! Go to Solution.
‎05-08-2017 11:39 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As Type,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets1.AssetName As AssetLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Domain,
Case tsysAssetTypes.AssetTypename When 'Windows' Then tsysOS.OSname
When 'Linux' Then tblLinuxSystem.OSRelease
When 'Apple Mac' Then tblMacOSInfo.KernelVersion Else '' End As OS,
tblAssets.Mac As [MAC Address],
tblADComputers.OU,
tblState.Statename,
tblAssets.Description,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.LastPatched,
tblAssetCustom.LastFullbackup,
tblAssetCustom.LastFullimage,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssetCustom.Custom4,
tblAssetCustom.Custom5,
tblAssetCustom.Custom6,
tblAssetCustom.Custom7,
tblAssetCustom.Custom8,
tblAssetCustom.Custom9,
tblAssetCustom.Custom10,
tblAssetCustom.Custom11,
tblAssetCustom.Custom12,
tblAssetCustom.Custom13,
tblAssetCustom.Custom14,
tblAssetCustom.Custom15,
tblAssetCustom.Custom16,
tblAssetCustom.Custom17,
tblAssetCustom.Custom18,
tblAssetCustom.Custom19,
tblAssetCustom.Custom20,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Order By tblAssets.AssetName
‎05-08-2017 02:12 PM
‎05-08-2017 11:39 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As Type,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets1.AssetName As AssetLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Domain,
Case tsysAssetTypes.AssetTypename When 'Windows' Then tsysOS.OSname
When 'Linux' Then tblLinuxSystem.OSRelease
When 'Apple Mac' Then tblMacOSInfo.KernelVersion Else '' End As OS,
tblAssets.Mac As [MAC Address],
tblADComputers.OU,
tblState.Statename,
tblAssets.Description,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.LastPatched,
tblAssetCustom.LastFullbackup,
tblAssetCustom.LastFullimage,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssetCustom.Custom4,
tblAssetCustom.Custom5,
tblAssetCustom.Custom6,
tblAssetCustom.Custom7,
tblAssetCustom.Custom8,
tblAssetCustom.Custom9,
tblAssetCustom.Custom10,
tblAssetCustom.Custom11,
tblAssetCustom.Custom12,
tblAssetCustom.Custom13,
tblAssetCustom.Custom14,
tblAssetCustom.Custom15,
tblAssetCustom.Custom16,
tblAssetCustom.Custom17,
tblAssetCustom.Custom18,
tblAssetCustom.Custom19,
tblAssetCustom.Custom20,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Order By tblAssets.AssetName
‎11-01-2023 09:23 PM
This pulled only 13 assets of the over 16,000 we should have...
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now