## Saturday, August 11, 2012

### PowerShell: Use VBScript to Color Table Rows

I decided to take a break from the XML solutions for HTML management of multi-table reports in PowerShell.  This week I will demonstrate an extremely simple method that most Admin scripters can master because it uses the old standby “VBScript” to do the color tagging in the browser.

We use the same method to generate a table and create the HTML:

Param(    $reportpath}Get-WmiObject win32_logicaldisk | Select-Object @{N='Server';E={$_.__SERVER}},                   Deviceid,                   @{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} | ConvertTo-Html | Out-File $reportpath&$reportpath

Same report.  Same HTML.

We can colorize the rows very easily by injecting some VBScript into the output file.

$head=@" <script language="vbscript"> Sub window_onload() Set tbl1 = document.getElementsByTagName("table")(0) For Each r In tbl1.Rows Set cell = r.lastChild if IsNumeric(cell.innerText) Then If cell.innerText <$threshold Then                        cell.style.color="red"                    Else                        cell.style.color="green"                    End If                Else                    If LCase(cell.tagName) <> "th" Then                        cell.innerText="n/a"                    End If                End If            Next        End Sub    </script>"@

The above is just a “here-string”  wrapped around a block of VBScript.  You can put the script into the output file manually to see it work.  I built and tested the script in an HTA in about 5 minutes.

The VBScript is just a “window_onload” event that grabs first the table on the page and goes through each row in a for loop.

Set tbl1 = document.getElementsByTagName("table")(0)For Each r In tbl1.Rows    Set cell = r.lastChild     If IsNumeric(cell.innerText) Then        If cell.innerText < 10 Then            cell.style.color="red"        Else            cell.style.color="green"        End If    Else        If LCase(cell.tagName) <> "th" Then            cell.innerText="n/a"        End If    End IfNext

Note how we skip the header cells and how I test for the existence of a numeric value.  For each row I get the last child of the row which I know is the column with the numbers. All columns are tested against a value that tells us when to color the column red.  We could also color the whole row by using cell.parent.style=”red”.  We might even set the background color.

Here is a copy of the test file: Copy and run it to see that it works.

<html><head><script language="vbscript">   1:     2:     Sub window_onload()   3:         Set tbl1 = document.getElementsByTagName("table")(0)   4:         For Each r In tbl1.Rows   5:             Set cell = r.lastChild    6:             if IsNumeric(cell.innerText) Then   7:                 If cell.innerText < 10 Then   8:                     cell.style.color="red"   9:                 Else  10:                     cell.style.color="green"  11:                 End If  12:             Else  13:                 If LCase(cell.tagName) <> "th" Then  14:                     cell.innerText="n/a"  15:                 End If  16:             End If  17:         Next  18:     End Sub</script></head><body><table><colgroup><col/><col/><col/></colgroup><tr><th>Server</th><th>Deviceid</th><th>PercentFree</th></tr><tr><td>OMEGA2</td><td>A:</td><td></td></tr><tr><td>OMEGA2</td><td>C:</td><td>2</td></tr><tr><td>OMEGA2</td><td>D:</td><td></td></tr><tr><td>OMEGA2</td><td>E:</td><td>87</td></tr><tr><td>OMEGA2</td><td>F:</td><td>89</td></tr><tr><td>OMEGA2</td><td>X:</td><td>60</td></tr></table></body></html>

Once I had this working I just copied the script tag to my PowerShell editor and wrapped it in a here-string.  I also added a variable in place of the hard coded match variable.

Combining all the pieces we get this very simple PowerShell script.

<#    Create-DrivespaceReport.ps1#>Param(    $threshold = 50,$reportpath='\\omega2\test\DriveSpace.htm')$head=@" <style type="text/css"> body{ background-color: antiquewhite; } h4{ font-style: italic; } table{ border-collapse:collapse; background-color: LightGoldenRodYellow; } table td{ border: 1px solid black; font-weight: thinner; } table th{ border: 1px solid black; font-weight: bolder; color: #00008B; background-color: gold; } </style> <script language="vbscript"> Sub window_onload() Set tbl1 = document.getElementsByTagName("table")(0) For Each r In tbl1.Rows Set cell = r.lastChild if IsNumeric(cell.innerText) Then If cell.innerText <$threshold Then                        cell.style.color="red"                    Else                        cell.style.color="green"                    End If                Else                    If LCase(cell.tagName) <> "th" Then                        cell.innerText="n/a"                    End If                End If            Next        End Sub    </script>"@$precontent="<h4>Disk Usage Report for$([datetime]::today.ToLongDateString())</h4>"# generate the tablegwmi win32_logicaldisk |      Select-Object @{N='Server';E={$_.__SERVER}}, Deviceid, @{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} | ConvertTo-Html -head$head -PreContent $precontent | Out-File$reportpath# page has been copied to a trusted share so the script will execute&\$reportpath

For the final version I threw in a simple style sheet along with the script to give the page some color.  Click the link below to see the final sample.