‎01-02-2020 04:43 PM
‎01-24-2020 10:29 PM
Where
( (Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate())
Or tblAssetCustom.PurchaseDate Is Null)
And tsysAssetTypes.AssetTypename In ('Windows')
And tblAssetCustom.State = 1
And tblAssets.Assettype <> 66
And tblAssetCustom.Model IS NULL
Where
( (Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate())
Or tblAssetCustom.PurchaseDate Is Null)
And tsysAssetTypes.AssetTypename In ('Windows')
And tblAssetCustom.State = 1
And tblAssets.Assettype <> 66
And (tblAssetCustom.Model NOT LIKE '%Virtual%'
OR tblAssetCustom.Model IS NULL)
‎01-24-2020 09:30 PM
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.PurchaseDate) As [Purchase Year],
Year(tblAssetCustom.PurchaseDate) + 5 As [Q Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where ((Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate()) Or
tblAssetCustom.PurchaseDate Is Null) And tsysAssetTypes.AssetTypename In
('Windows') And tblAssetCustom.State = 1 And tblAssets.Assettype <> 66
Except
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.PurchaseDate) As [Purchase Year],
Year(tblAssetCustom.PurchaseDate) + 5 As [Q Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where tblAssetCustom.Custom19 = 'Yes' And ((Year(tblAssetCustom.PurchaseDate) +
5) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [Q Refresh] Desc
‎01-24-2020 06:50 PM
‎01-24-2020 06:46 PM
‎01-24-2020 06:02 PM
‎01-23-2020 09:27 PM
‎01-23-2020 05:12 PM
‎01-22-2020 10:58 PM
‎01-21-2020 09:55 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now