Notification

Icon
Error

Assistance With Colour Coding

Posted: Tuesday, July 28, 2020 7:34:08 AM(UTC)
CyberCitizen

CyberCitizen

Member Original PosterPosts: 377
0
Like
This issue has been solved! Click here to view the solution
Hi Guy's,

I have a report based on BIOS hardware age. Wanting to use this to target our laptop replacements.

I have the odd colour reports but not sure how to colour this one based on the Years Old column.

Basically wanting it green up until 3 years old, yellow from 3 years old to 3 and a half years then red from 3 and a half years and up.

We have currently are replacing every 4 years, but there is a drive to replace every 3 years after the warranty is out and donate to charity.

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  Convert(nVARCHAR(10),tblBIOS.ReleaseDate,101) As [BIOS Date],
  Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00,
  2) As Numeric(8,2)) As [Years Old],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblADusers.Username As Username1,
  tblADusers.email
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblState.Statename = 'Active'
Order By [Years Old] Desc
nnewton
#1nnewton Member Posts: 20  
posted: 7/28/2020 8:52:59 AM(UTC)
Code:
SELECT TOP 1000000 tblAssets.AssetID, 
                   tblAssets.AssetName, 
                   tblAssets.Username, 
                   tblAssets.Domain, 
                   tblAssets.IPAddress, 
                   tsysIPLocations.IPLocation, 
                   tblAssetCustom.Manufacturer, 
                   tblAssetCustom.Model, 
                   tsysOS.OSname AS OS, 
                   COALESCE(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon, 
                   CONVERT(NVARCHAR(10), tblBIOS.ReleaseDate, 101) AS [BIOS Date], 
                   CAST(ROUND(DATEDIFF(day, tblBIOS.ReleaseDate, GETDATE()) / 365.00, 2) AS NUMERIC(8, 2)) AS [Years Old], 
                   tblAssets.Lastseen, 
                   tblAssets.Lasttried, 
                   tblADusers.Username AS Username1, 
                   tblADusers.email,
                   CASE
                       WHEN CAST(ROUND(DATEDIFF(day, tblBIOS.ReleaseDate, GETDATE()) / 365.00, 2) AS NUMERIC(8, 2)) < 3
                       THEN '#d4f4be'
                       WHEN CAST(ROUND(DATEDIFF(day, tblBIOS.ReleaseDate, GETDATE()) / 365.00, 2) AS NUMERIC(8, 2)) < 3.5
                       THEN '#fff1ad'
                       ELSE '#ffadad'
                   END AS backgroundcolor
FROM tblAssets
     INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
     INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
     LEFT JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
     INNER JOIN tblState ON tblState.State = tblAssetCustom.State
     LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
     INNER JOIN tblBIOS ON tblAssets.AssetID = tblBIOS.AssetID
     INNER JOIN tblADusers ON tblADusers.Username = tblAssets.Username
WHERE tblState.Statename = 'Active'
ORDER BY [Years Old] DESC;
CyberCitizen
#2CyberCitizen Member Original PosterPosts: 377  
posted: 8/7/2020 6:20:38 AM(UTC)
Just wanted to say thank you, this was exactly what I was after.

Active Discussions

Lansweeper Email to close ticket
by  wgonzalez_hope   Go to last post Go to first unread
Last post: Yesterday at 3:54:01 PM(UTC)
Lansweeper Mass update assets through csv
by  B.L.  
Go to last post Go to first unread
Last post: Yesterday at 3:30:21 PM(UTC)
Lansweeper Restrict the access to the knowledgebase
by  ABEAL   Go to last post Go to first unread
Last post: Yesterday at 1:38:25 PM(UTC)
Lansweeper Wake on Lan Issues
by  Christophe  
Go to last post Go to first unread
Last post: Yesterday at 1:09:12 PM(UTC)
Lansweeper Reason: No email address found for the following user
by  Juha Otava   Go to last post Go to first unread
Last post: Yesterday at 9:59:41 AM(UTC)
Lansweeper Windows 10 Upgrade to 2004
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 8:47:16 AM(UTC)
Lansweeper Scanning despite exclusion
by  pskup   Go to last post Go to first unread
Last post: Yesterday at 7:27:08 AM(UTC)
Lansweeper Export ticket to CSV
by  KeithBecker  
Go to last post Go to first unread
Last post: 8/13/2020 5:56:10 PM(UTC)