cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Computer age report with categories.

Daniel_Red
Engaged Sweeper II
Hi I need some help with my computer age report.
I would like to based on the years old have a new Column called "Risk".


  • So that if a computer is 0 years old = "Unknown age".
  • So that if a computer is 0.001 to 4 years old = "Compliant Device" and row color Green.
  • So that if a computer is 4 to 8 years old = "Medium Risk Device" and row color yellow.
  • So that if a computer is 8 and up years old = "Riskiest Risk Device" and row color red.


This is what I got.
Thank you for your help

Age report

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.PurchaseDate,
Case
When tblAssetCustom.PurchaseDate Is Not Null Then DateDiff(day,
tblAssetCustom.PurchaseDate, GetDate()) / 365.0
Else '0'
End As [Years Old],
tblAssetCustom.Warrantydate,
tblADusers1.Name As [Managed by],
tblADusers.Name As [Last user],
tblAssets.Lastseen,
tsysIPLocations.IPLocation,
tblAssets.IPAddress,
tblOperatingsystem.Caption,
tblADComputers1.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblADComputers tblADComputers1 On tblAssets.AssetID =
tblADComputers1.AssetID
Inner Join tblADusers tblADusers1 On tblADComputers1.ManagerADObjectId =
tblADusers1.ADObjectID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.Manufacturer Not Like '%VMware%' And
tblAssetCustom.Model Not Like '%virtual%' And tblAssetCustom.State = 1 And
tblComputersystem.Domainrole <= 1
Order By [Years Old] Desc
1 REPLY 1

RCorbeil
Honored Sweeper II
Add two columns:
  CASE
WHEN tblAssetCustom.PurchaseDate IS NULL THEN 'Unknown Age'
WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 4 THEN 'Compliant'
WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 8 THEN 'Medium'
ELSE 'Riskiest'
END AS Risk,
CASE
WHEN tblAssetCustom.PurchaseDate IS NULL THEN NULL -- no colour
WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 4 THEN '#d4f4be' -- green
WHEN DateDiff(day, tblAssetCustom.PurchaseDate, GetDate()) / 365.0 < 8 THEN '#ffd152' -- yellow
ELSE '#ffadad' -- red
END AS backgroundcolor,

The ranges you specified overlap at 4 and 8 years, so I chose to assume those were the low point the next-higher group, i.e. 0-3.999..., 4-7.999..., 8+.