‎06-01-2015 10:48 AM
Solved! Go to Solution.
‎06-01-2015 01:14 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
tblAssets.Domain,
tsysOS.OSname As [OS name],
tblAssetCustom.Manufacturer,
Case
When tblAssetCustom.Manufacturer Like '%lenovo%' Then
tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
tblAssets.IPAddress As [IP address],
tsysIPLocations.IPLocation As [IP location],
tblAssets.Mac As [MAC address],
tblADComputers.OU,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Description,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Date],
tblAssets.FQDN,
tblAssetCustom.DNSName As [DNS name],
tblAssetCustom.LastPatched As [Last Patched],
tblAssetCustom.LastFullbackup As [Last Full Backup],
tblAssetCustom.LastFullimage As [Last Full Image],
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1 As [Asset Model],
tblAssetCustom.Custom2 As [Asset Category],
tblAssetCustom.Custom3 As Region,
tblAssetCustom.Custom4 As [FPR Unit],
tblAssetCustom.Custom5 As Location,
tblAssetCustom.Custom6 As [Sub Location],
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,
Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
End As [not affected by cleanup options],
tComputersystem.type
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
Left Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Left Join (Select Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation' End As type,
tblComputersystem.AssetID
From tblComputersystem) tComputersystem On tblAssets.AssetID =
tComputersystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName
‎12-05-2017 11:56 AM
‎06-01-2015 01:19 PM
‎06-01-2015 01:14 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
tblAssets.Domain,
tsysOS.OSname As [OS name],
tblAssetCustom.Manufacturer,
Case
When tblAssetCustom.Manufacturer Like '%lenovo%' Then
tblComputerSystemProduct.Version Else tblAssetCustom.Model End As Model,
tblAssets.IPAddress As [IP address],
tsysIPLocations.IPLocation As [IP location],
tblAssets.Mac As [MAC address],
tblADComputers.OU,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Description,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Date],
tblAssets.FQDN,
tblAssetCustom.DNSName As [DNS name],
tblAssetCustom.LastPatched As [Last Patched],
tblAssetCustom.LastFullbackup As [Last Full Backup],
tblAssetCustom.LastFullimage As [Last Full Image],
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1 As [Asset Model],
tblAssetCustom.Custom2 As [Asset Category],
tblAssetCustom.Custom3 As Region,
tblAssetCustom.Custom4 As [FPR Unit],
tblAssetCustom.Custom5 As Location,
tblAssetCustom.Custom6 As [Sub Location],
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,
Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
End As [not affected by cleanup options],
tComputersystem.type
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
Left Join tblComputerSystemProduct On tblAssets.AssetID =
tblComputerSystemProduct.AssetID
Left Join (Select Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else 'Workstation' End As type,
tblComputersystem.AssetID
From tblComputersystem) tComputersystem On tblAssets.AssetID =
tComputersystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now