cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
LiquidKaoz
Engaged Sweeper
Trying to get desktops over 5 years old based off of the purchase date. Here's what I have so far... I'm new to SQL, so please forgive and inefficiencies. It doesn't seem to grab everything...am I missing something?


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Where (tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 3) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 4) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 6) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 7) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 13) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 15) Or
(tblAssetCustom.PurchaseDate < GetDate() - 1825 And
tblSystemEnclosure.ChassisTypes = 16)
Order By tblAssetCustom.PurchaseDate
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
If you want to be sure that all computers are being picked up, it might be more safe to list all computers which are not servers, have no entry in tblPortableBattery and which are no virtual machines. The rest should be all desktops. Give the following report a try:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Where tblAssetCustom.Model Not Like '%virtual%' And
tblAssetCustom.Serialnumber Not Like '%virtual%' And
(tblAssetCustom.PurchaseDate < GetDate() - 365 Or
Coalesce(tblAssetCustom.PurchaseDate, '1900-01-01') = '1900-01-01') And
tblComputersystem.Domainrole < 2 And Coalesce(tblPortableBattery.AssetID, 0) =
0
Order By tblAssetCustom.PurchaseDate

View solution in original post

2 REPLIES 2
LiquidKaoz
Engaged Sweeper
It's still showing some laptops, but not a whole ton that I can't parse through. Thanks so much for that!
Daniel_B
Lansweeper Alumni
If you want to be sure that all computers are being picked up, it might be more safe to list all computers which are not servers, have no entry in tblPortableBattery and which are no virtual machines. The rest should be all desktops. Give the following report a try:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate
From tblAssets
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Where tblAssetCustom.Model Not Like '%virtual%' And
tblAssetCustom.Serialnumber Not Like '%virtual%' And
(tblAssetCustom.PurchaseDate < GetDate() - 365 Or
Coalesce(tblAssetCustom.PurchaseDate, '1900-01-01') = '1900-01-01') And
tblComputersystem.Domainrole < 2 And Coalesce(tblPortableBattery.AssetID, 0) =
0
Order By tblAssetCustom.PurchaseDate