Need assistance in modifying the SQL PS script to do conditional formatting

PowerShell Help
1

Hi PS Experts,

Need assistance in modifying the PS script to do conditional formatting.

I have a default sql instance and a table created in master db.

create table diskspace
(drive char(3),
SizeGB int,
FreeGB int,
PercentFree int
)
go
–dummy data .
Note: precentfree are just sample values n not real ones.

insert into diskspace values(‘C:’,500,100,10)
insert into diskspace values(‘D:’,500,300,50)
insert into diskspace values(‘E:’,500,250,20)
insert into diskspace values(‘F:’,500,400,5)
insert into diskspace values(‘G:’,500,200,60)
go

Now, I have powershell script which does conditional formatting for the entire row based on “PercentFree” column.
If PercentFree < 10 then the entire TABLE row() is displayed in RED indicating its CRITICAL.
If PercentFree < 20 then the entire TABLE row() is displayed in YELLOW indicating a Warning.

But I want to only change the background column of the PercentFree cell() and not entire row.
How to achieve it? Any help ?

Complete code

$machine = "Srv-1"
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("$machine")
$qry = @"
select drive,SizeGB,FreeGB,PercentFree from master..diskspace order by drive
"@ 
$myOutput = Invoke-Sqlcmd -Query $qry `
-ServerInstance $env:COMPUTERNAME   #default instance
#embed the style, image and title in the html header
$head = @"
Space Utilization Report
body 
{ 
background-color:#FFFFFF;
font-family:Arial;
font-size:12pt; 
}
td, th 
{ 
border:1px solid black; 
border-collapse:collapse; 
}
th 
{
color:white;
background-color:gray; 
}
table, tr, td, th { padding: 5px; margin: 0px }
table { margin-left:50px; }
.danger {background-color: red}
.warn {background-color: yellow}
Disk Space Utilization on $Computername
"@
[xml]$html = $myOutput | Select drive,SizeGB,FreeGB,PercentFree | ConvertTo-HTML -fragment
Clear-Host
#check each row, skipping the TH header row
for ($i=1;$i -le $html.table.tr.count-1;$i++) 
{
$class = $html.CreateAttribute("class")
#check the value of the last column and assign a class to the row
#td[3] means 4th column i.e. last column in my table 
# drive - td[0], SizeGB - td[1],FreeGB - td[2],PercentFree - td[3]
if (($html.table.tr[$i].td[3] -as [int]) -le 10) 
{                                          
$class.value = "danger"  
$html.table.tr[$i].Attributes.Append($class) | Out-Null
}
elseif (($html.table.tr[$i].td[3] -as [int]) -le 20) 
{                                               
$class.value = "warn"    
$html.table.tr[$i].Attributes.Append($class) | Out-Null
}
}
#create the final report from the innerxml which should be html code
$body = @"
$($html.innerxml)
"@
#check out the html
$body
#create the HTML file
ConvertTo-HTML -head $head -PostContent "$(Get-date)" -body $body | Out-File "$env:temp\driveSpaceusage.htm" -Encoding ascii
#view it
Invoke-Item "$env:temp\driveSpaceusage
2

M1981, I just registered in PowerShell.org Would you mind telling me how to post a question? Thx. Wildcat4

3

Not sure. Is there anything wrong in the post. Please let me know !!

4

@Wildcat4 : When you’re looking at the list of toppics - scroll down and fill in the ‘Create New Topic in “PowerShell Q&A” - form’ and hit ‘Submit’

5

Thank you for your quick reply! I found it.

6

Hi,

You need to change 2 lines (45 and 50). Change the parts that say:

$html.table.tr[$i].Attributes.Append($class)

to

$html.table.tr[$i].td[3].Attributes.Append($class)

That should give you the result you are looking for.

7

Hi Stuart,

Thank you for the response.

Made the change but it didn’t work!!

8

Hi,

Sorry about that. This should have worked, but for some reason the table object model breaks once you get down to td elements. Try this instead. Replace the 2 lines in question (45 and 50) with:

$html.table.tr[$i].GetElementsByTagName("td")[3].Attributes.Append($class) | Out-Null

This works for me on IE11 and Edge.

9

I just wanted to share a fun way to do reporting with LINQ. Keep in mind that if you are emailing this report, email clients are finicky and you need to keep things as simple as possible. You need to keep as much inline as possible and you should really avoid CSS in a header to have it render in mail and web clients. Anywho, take a look at this approach:

10

Thank you Sir Stuart. Thank you very much for the help.

11

Hi Rob,

Its little over my head but thank you for the alternative solution.

12

Hello, I got a reply having to do with “Need assistance in modifying the SQL PS script to do conditional formatting,” but not dealing with data associated with Exchange/office 365 with PowerShell? Thank you.