Notification

Icon
Error

Modifying Purchase Date / Yearly Refresh Report

Posted: Friday, May 7, 2021 7:06:47 PM(UTC)
Cripple.Zero

Cripple.Zero

Member Original PosterPosts: 46
0
Like
Using resources provided here, a couple years ago I was able to create a report that would assist in a 5 year refresh program based off of Purchase Date (+5 years: so if this is 2021, and asset purchased in 2016 or earlier will ONLY show in the list). The report is/was immaculate. I recently moved to a NEW company and am trying to modify that same report to base everything off of Warranty Date.

Where I've moved to, they only use Lenovo and are phasing out their Dells and HPs (ironically the manufacturer's that correctly report purchase dates!). The new refresh plan, given the age of the hardware here, is to base everything off of +2 years from the Warranty End date. That is the only thing reporting properly. Then, once everything is base-lined, we can start reducing our needs for extended warranties, stick to the 3 year and reduce the refresh down to +1 beyond warranty date.

I've included the original code that worked for Purchase Date, then following included the edited code to adjust for Warranty Date. For some reason, everything beyond the current year (i.e. 2021) is showing up; where I want to only have assets where their warranty year +2 = 2021 or older (2020, 2019, 2018, etc.)

I've gone over the code 100s of times and can't figure out what I'm missing. Brick wall Brick wall

Apologies for the sloppy code!

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


And here is the modified code:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI 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.Warrantydate) + 2) <= 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.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI 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.Custom10 = 'Yes' And ((Year(tblAssetCustom.Warrantydate) +
2) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [AMI Refresh] Desc
Cripple.Zero
#1Cripple.Zero Member Original PosterPosts: 46  
posted: 5/18/2021 6:20:17 PM(UTC)
So I've gotten a bit closer, but now I need to add in the OS (like add column tsysOS.OSname As OS) and remove the code the ignores "Surface", "Microsoft" and "Tablet" since our environment relies heavily on Surface Tablets for a lot of our manufacturing and distribution.

I think the fault lies in my innerjoin statements, but I can't be sure. I can't simply can't add the tsysOS.OSname As OS line as I get a "multi-part" SQL error. I don't know which code to strip for the "Surface" exclusions either. Brick wall Brick wall

The original coding was built around Purchase Date. However Lenovo does not report that information correctly like HP and Dell and I had to modify based on Warranty Expiration Date (year).

Below is the updated code that utilizes Warranty Date.

Any help is appreciated.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI 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,
tsysOS
Where (Year(tblAssetCustom.Warrantydate) + 2) <= Year(GetDate()) 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.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI 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.Custom10 = 'Yes' And ((Year(tblAssetCustom.Warrantydate) +
2) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [AMI Refresh]
Cripple.Zero
#2Cripple.Zero Member Original PosterPosts: 46  
posted: 5/19/2021 8:45:26 PM(UTC)
So I was able to remove the exception code from the report that 'ignored' anything "Microsoft", "Surface" and "Tablet".

The report count did not change.

Upon further review, I found that our Surfaces report neither Purchase Date, nor Warranty Expiration Date - both are empty/null.

So, outside of manually tracking down the documentation per purchased unit and inputting it into the LS asset for Surfaces, there's nothing I can do to get them to show up for any date-related fields.

All that's left is to try and get the OS name/field to show up on the report. I manually linked tsysOS to tblAssets. I feel I have tried every combination of linking OScode of tblAssets to either OScode or OSname of tsysOS. I keep getting this error:

"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Here is the "alleged" modified code:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer,
tblAssets.OScode,
tsysOS.OSname As OS
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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode And tblAssets.OScode =
tsysOS.OSname
Where (Year(tblAssetCustom.Warrantydate) + 2) <= Year(GetDate()) 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.Warrantydate) As [EOW Year],
Year(tblAssetCustom.Warrantydate) + 2 As [AMI Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer,
tblAssets.OScode,
tsysOS.OSname As OS
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.Custom10 = 'Yes' And ((Year(tblAssetCustom.Warrantydate) +
2) <= Year(GetDate()) Or tblAssetCustom.PurchaseDate Is Null) And
tsysAssetTypes.AssetTypename In ('Windows') And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [AMI Refresh]

Active Discussions

Lansweeper Enterprise Options in Menu Bar/Configuration
by  mk@allan   Go to last post Go to first unread
Last post: 6/18/2021 7:38:43 PM(UTC)
Lansweeper No One getting back to me from Lansweeper
by  Kenneth Lindsay  
Go to last post Go to first unread
Last post: 6/18/2021 3:31:06 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: 6/18/2021 10:48:57 AM(UTC)
Lansweeper RPC Unavailable error
by  Greeno  
Go to last post Go to first unread
Last post: 6/17/2021 7:15:07 PM(UTC)
Lansweeper Exclude Search
by  pryan67  
Go to last post Go to first unread
Last post: 6/16/2021 4:01:43 PM(UTC)
Lansweeper Report: All Apple Mac devices with Memory RAM asset
by  gabrielo   Go to last post Go to first unread
Last post: 6/16/2021 3:17:24 PM(UTC)
Lansweeper Does technical support for LS really respond?
by  tosch  
Go to last post Go to first unread
Last post: 6/16/2021 12:48:50 PM(UTC)