‎08-28-2015 03:58 PM
Solved! Go to Solution.
‎08-28-2015 05:39 PM
Select Top 1000000 Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop' End End As Type,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblAssetCustom.Custom5 As AssetTag,
tblAssetCustom.Contact As [Allocated ID],
tblADusers.Displayname,
tblState.Statename As [Asset state],
tblAssetCustom.Custom3 As [PO Number],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate As [Warranty End Date],
tblAssetCustom.Custom2 As [Invoice number],
tblOperatingsystem.Caption As Operatingsystem,
tblAssets.Domain,
tblAssetCustom.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Comments
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Order By tblAssets.Assetname
‎09-09-2015 04:51 PM
‎09-09-2015 04:00 PM
‎08-28-2015 05:39 PM
Select Top 1000000 Case When tblComputersystem.Domainrole > 1 Then 'Server'
Else Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop' End End As Type,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.AssetUnique,
tblAssetCustom.Serialnumber As [Service Tag Number],
tblAssetCustom.Custom5 As AssetTag,
tblAssetCustom.Contact As [Allocated ID],
tblADusers.Displayname,
tblState.Statename As [Asset state],
tblAssetCustom.Custom3 As [PO Number],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate As [Warranty End Date],
tblAssetCustom.Custom2 As [Invoice number],
tblOperatingsystem.Caption As Operatingsystem,
tblAssets.Domain,
tblAssetCustom.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Comments
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join tblState On tblAssetCustom.State = tblState.State
Order By tblAssets.Assetname
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now