How to add color when HDD % Free is less than

[brican847]

Hi,

I've Frankensteined a report from a post here and there and I'm getting close to where I want it to be. Here is what I have so far:

Select Distinct Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Memory,
Cast(CorrectMemory.Used As numeric) As [Slots used],
tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used As [Slots free],
tblPhysicalMemoryArray.MemoryDevices As [Slots available],
tblProcessor.Name As [Processor Series],
tblDiskdrives.Caption As Drive,
Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric)
As [Total size (GB)],
Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric) As [Free in GB],
Cast((Cast(dbo.tblDiskdrives.Size As BigInt) -
Cast(dbo.tblDiskdrives.Freespace As BigInt)) / 1024 / 1024 / 1024 As
Numeric) As [Used Space (GB)],
Cast(Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric) / Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 /
1024 As Numeric) * 100 As bigint) As [% Free],
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblPhysicalMemoryArray On tblAssets.AssetID =
tblPhysicalMemoryArray.AssetID
Inner Join (Select tblAssets.AssetID,
Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory,
Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used
From tblAssets
Left Outer Join (TsysMemorytypes
Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType) On tblAssets.AssetID =
tblPhysicalMemory.AssetID
Group By tblAssets.AssetID,
tblPhysicalMemory.MemoryType
Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory
On CorrectMemory.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
Inner Join TsysMemorytypes On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tsysOS.OSname Not Like 'Win 2%' And tblDiskdrives.Caption = 'C:'
And tblPhysicalMemoryArray.[Use] = 3 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

What I'm trying to add is a way to color the machines to red with less than 10% Free Space. As you can probably tell from the mess above I'm a SQL Query novice to put it nicely. Thanks for your help.

[RCorbeil]

If you've got the time and inclination, it's worth getting to know the basics of SQL. LANSweeper's "reports" are just SQL queries, as you've discovered. This site offers some good basics.

[brican847]

Thanks again RC62N! That did it. I appreciate the help and the suggestions for keeping it clean. I'm not a fan of messy confusing scripts when it comes to PowerShell so the SQL query is killing me since I don't know it as well.

[RCorbeil]

Just to be clear, for your two red-result conditions, you'd want to set up your CASE like this:

CASE
  WHEN free_space < 10% THEN red
  WHEN warranty_expired THEN red
  ELSE black
END AS foregroundcolor

[RCorbeil · Accepted solution]

You're on the right track. The CASE statement needn't be used as a simple IF/THEN/ELSE: you can set a variety of conditions and results. Different conditions can lead to the same result or you can OR multiple conditions that lead to the same result. (The former makes the code more legible, in my opinion, but that's a personal preference.)

CASE
  WHEN condition1 THEN red
  WHEN condition2 THEN yellow
  WHEN condition3 THEN red
  WHEN condition4 THEN cyan
  WHEN condition5 THEN green
  WHEN condition6 OR condition7 OR condition8 THEN magenta
  WHEN condition9 THEN blue
  ELSE black
END AS foregroundcolor

[RCorbeil]

Refer to this thread. You should be able to use the information there to set up a CASE statement to assign the colours you want.

[brican847]

RC62N wrote:
Refer to this thread. You should be able to use the information there to set up a CASE statement to assign the colours you want.

Thanks RC62N. I was able to figure it out and make some additional changes as well after taking the weekend away from it. Here is my final result:

Select Distinct Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName As [WorkStation Name],
tsysOS.OSname As OS,
tblAssetCustom.Model,
tblAssets.Domain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation As Site,
tblAssets.Memory,
Cast(CorrectMemory.Used As numeric) As [Slots used],
tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used As [Slots free],
tblPhysicalMemoryArray.MemoryDevices As [Slots available],
Case When tblProcessor.Name Not Like 'Intel Core i%' Then '#fbfb86'
End As backgroundcolor,
tblProcessor.Name As [Processor Series],
tblDiskdrives.Caption As Drive,
Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric)
As [Total size (GB)],
Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric) As [Free Space (GB)],
Cast((Cast(dbo.tblDiskdrives.Size As BigInt) -
Cast(dbo.tblDiskdrives.Freespace As BigInt)) / 1024 / 1024 / 1024 As
Numeric) As [Used Space (GB)],
Case
When Cast(Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric) / Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 /
1024 / 1024 As Numeric) * 100 As bigint) <= 10 Then 'red'
End As foregroundcolor,
Cast(Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric) / Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 /
1024 As Numeric) * 100 As bigint) As [% Free],
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
Convert(Varchar(10),tblAssetCustom.Warrantydate,101) As [Warranty Expiration]
From tblAssets
Inner Join tblPhysicalMemoryArray On tblAssets.AssetID =
tblPhysicalMemoryArray.AssetID
Inner Join (Select tblAssets.AssetID,
Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory,
Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used
From tblAssets
Left Outer Join (TsysMemorytypes
Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType) On tblAssets.AssetID =
tblPhysicalMemory.AssetID
Group By tblAssets.AssetID,
tblPhysicalMemory.MemoryType
Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory
On CorrectMemory.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
Inner Join TsysMemorytypes On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where (tblAssets.AssetName Not Like 'BLAHblah-%' And
tsysOS.OSname Not Like 'Win 2%' And tblAssets.Domain = 'BLAH' And
tblDiskdrives.Caption = 'C:' And tblPhysicalMemoryArray.[Use] = 3 And
tblAssetCustom.State = 1) Or
(tsysOS.OSname Not Like 'Win 2%' And tblAssets.Domain = 'BLAHBLAH' And
tblDiskdrives.Caption = 'C:' And tblPhysicalMemoryArray.[Use] = 3 And
tblAssetCustom.State = 1)
Order By Site,
[WorkStation Name]

I've added warranty information as well. Is there a way to assign a foreground color to:

Convert(Varchar(10),tblAssetCustom.Warrantydate,101) As [Warranty Expiration]

I'd like to change the foreground to 'red' for both the 'Warranty Expiration' and the 'Free %'. I'm thinking that once I add 'End As foreground', it closes the foreground so any additional statements with foreground in it fails saying foreground was already used in another column and needs to be unique.
How can I assign a foreground to both the warranty info and the 'Free %'?