Pages

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 If
Next

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 table
gwmi 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.
 
Here is a link to download it: Simple Dynamically Colored Table Script

Here is a link to a sample of the report: Sample Report

Other articles in the series on managing HTML reports from PowerShell.

PowerShell: Dynamically Color PosH Generated HTML:Part 1
PowerShell: Dynamically Color PosH Generated HTML:Part 2
PowerShell: Dynamically Color PosH Generated HTML:Part 3

Saturday, August 04, 2012

PowerShell: Dynamically Color PosH Generated HTML:Part 3

PowerShell: Dynamically Color PosH Generated HTML:Part 1

PowerShell: Dynamically Color PosH Generated HTML:Part 2
PowerShell: Dynamically Color PosH Generated HTML:Part 3

The Two Table Solution

image

The results of a two table layout look like this in the browser. This is accomplished by using the previously discussed XML techniques to combine the two tables and save to an HTML file.

The style sheet in this example has been injected as part of the template file that I used to produce this layout.  This demo is simple and not very good looking because used contrasting colors to force the layout to be visible.

In Part 2 I sent the output to an email message to show how easy it is to connect a style sheet, a table generated from a PowerShell collection and an email message.

In Part 3 I want to expand on the technique by demonstrating how to add multiple tables with independent styles into an HTML document.  This is useful for generating reports that can be printed, converted to MSWord, Adobe PDF or almost any portable format as well as being displayable on a web page.  This will make  PowerShell into a mini Report Server without the need for a database or a website.

A Simple Two Table Report

The PowerShell script that generates the above output is a composite of the techniques from Part 2.  The code will run on any system as it only calls CmdLets that are builtin.  Copy the script to a file and run it.It will pop up a browser with the two table report.

#HTML-Two-Tables.ps1
#generate table 1
$threshhold=10 # for match to set row color
$html=gwmi win32_logicaldisk |
Select-Object @{N='Server';E={$_.__SERVER}},
Deviceid,
@{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} |
ConvertTo-Html -Fragment

#load html as XML
$xmlTable1=[xml]$html
# add a table id
$attr=$xmlTable1.CreateAttribute('id')
$attr.Value=’table1’
$xmlTable1.table.Attributes.Append($attr)
# get rows and set color based ib valuye un last column
$rows=$xmlTable1.table.selectNodes('//tr')
for($i=1;$i -lt $rows.count; $i++){
$value=$rows.Item($i).LastChild.'#text'
if($value.Length -gt 0 -and [int]$value -le $threshhold){
Write-Host "Candidate found - setting color to red at $value" -fore green
$attr=$xmlTable1.CreateAttribute('style')
$attr.Value='background-color: red;'
[void]$rows.Item($i).Attributes.Append($attr)
}
}

# table 2
$html=get-process|
sort cpu -desc |
select processname, @{n='WorkingSet';E={$_.WS/1Mb}}, @{n='CPU%';E={$_.CPU}} -First 5 |
ConvertTo-Html -Fragment |Out-String
$xmlTable2=[xml]$html
$attr=$xmlTable2.CreateAttribute('id')
$attr.Value=’table2’
$xmlTable2.table.Attributes.Append($attr)

# load the report template
$page=[xml](cat template1.htm|?{$_ -notmatch 'DOCTYPE'}|out-string)
$title=$page.selectSingleNode('/html/head/title')
$title.InnerText="Two Table Report Demo"
$pageTitle=$page.selectSingleNode('/html/body/h1')
$pageTitle.innerText="Two Table Report Demo - $([datetime]::Today)"
$body=$page.selectSingleNode('/html/body')
$tbl1=$page.ImportNode($xmlTable1.DocumentElement,$true)
$body.AppendChild($tbl1)
$tbl2=$page.ImportNode($xmlTable2.DocumentElement,$true)
$body.AppendChild($tbl2)
$page.Save("$PWD\tempreport.htm")
.\tempreport.htm


This is the simplest of all ways to generate a multiple table report.  The style sheet is used to control the placement of the tables. . In this report the tables are placed by “floating” them left and right.  The technique is standard in CSS and is useful however it is hard for those who do not know CSS to get things to work as desired.

How might we improve on this method to make it easier to create custom reports of some complexity?

A Template Driven Solution

I will start by creating a template that we will use as a base for our report. The template will be an XHTML file that can be loaded as XML and edited to insert the new tables.  This will allow us to use any of many tools to design our page layout.

Here is the basic template for the page we are going to build.

<html>
<head>
<meta name="GENERATOR" content="SAPIEN Technologies PrimalScript 2011"/>
<title>Document Title</title>
<style type="text/css">
#reportTitle {
text-align: center;
color: darkblue;
}
#reportTable{
width: 900px;
background-color: red;
}
#leftTable,#rightTable{
background-color: lightgreen;
color: yellow:
}
</style>
</head>
<body>

<table id="reportTable">
<tr>
<td><h1 id="reportTitle">DUMMY TITLE</h1></td>
</tr>
<tr>
<td id="leftTable">Hello World</td>
<td id="rightTable">Hello World</td>
</tr>
</table>
</body>
</html>

The HTML is trivial.  It has only one element of interest.  There is a table that positions the elements.  It does not matter how you choose to position this table.  You can do it manually by setting exact sizes for all of the pieces.  The real trick is in giving the table’s  ‘td’ elements an id that can be addressed and loaded with our HTML from the ConvertTo-Html CmdLet.

Here is how we load the tables into the report.

1. Get the template as an XML object

$htmlReport=(Get-Content template1.htm)

2.  Generate the first data table
      This is the same as we did before

3.  Add the table’s XML string to the $htmlReport target ‘td’ element with an id of “leftTable”.

      $tbl1=$html.SelectSingleNode('//td[@id="leftTable"]')
      $tbl1.innerxml=$t1

4. repeat for all tables to be included.

Here is the complete code.

# load the template file
$htmlReport=[xml](Get-Content template1.htm)

# set the title
$title=$htmlReport.SelectSingleNode('//*[@id="reportTitle"]')
$title.innerText="Two Table Report - $([datetime]::Today.ToString('MMMM dd yyyy'))"

# table 1 fragment
$t1=gwmi win32_logicaldisk |
Select-Object @{N='Server';E={$_.__SERVER}},
Deviceid,
@{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} |
ConvertTo-Html -Fragment

# add to $htmlReport by finding the id for the 'td' target element 'leftTable'
$tbl1=$htmlReport.SelectSingleNode('//td[@id="leftTable"]')
$tbl1.innerxml=$t1

# table 2
$t2=get-process|
sort cpu -desc |
select processname, @{n='WorkingSet';E={$_.WS/1Mb}}, @{n='CPU%';E={$_.CPU}} -First 5 |
ConvertTo-Html -Fragment |Out-String

# add to $htmlReport by finding the id for the 'td' target element 'roghtTable'
$tbl2=$htmlReport.SelectSingleNode('//td[@id="rightTable"]')
$tbl2.innerxml=$t2

# save the report and run it
$htmlReport.Save("$pwd\report1.htm")
.\report1.htm



I used odd colors to help us see how things get placed.

The template has a table that is very simple.  Just a few lines of HTML that can be entered by hand or using one of the many free HTML editors.

<table id="reportTable">
<tr>
<td><h1 id="reportTitle">DUMMY TITLE</h1></td>
</tr>
<tr>
<td id="leftTable">Hello World</td>
<td id="rightTable">Hello World</td>
</tr>
</table>

The table has a dummy title and some dummy text which lets us see the effect of the formatting without loading any data,  We should actually place real representative data into the report so we can see the full effect of the formatting.  The contents of the ‘td’ elements will be replaced by the script.

The table has a one column row and a two column row,  This is very standard for a two column report.  We can have more columns or even a very complex layout.

The title ‘h1’ tag has been given an 'id of “reportTitle'” so we can easily find it in the XML.

$titleNode=$htmlReport.SelectSingleNode(‘//[@id=”reportTitle”]’)
$titleNode.innerText=”Our New Title”

We do the same to retrieve the two table nodes by id.

The placement of the tables can be done in any way.  I used a style sheet and set some basic style elements of the table and the two ‘td’ elements by referencing their IDs in the CSS.

Here is the style:

<style type="text/css">
#reportTitle {
text-align: center;
color: darkblue;
}
#reportTable{
width: 900px;
background-color: red;
}
#leftTable,#rightTable{
background-color: lightgreen;
color: yellow:
}
</style>

Each one of the ids is referenced by id and some styles are set.  The main one being to set the width of the table (900px.  The odd colors are just so we can see what is being affected

That is it.  It is that simple and can be adjusted in many ways.  The plus is we did not play with HTML in PowerShell.  We just generated our HTML data and added to our xml loaded from the template.

In the next part I will add more fancy elements and demonstrate how to create a header section for global information.  With what we have now I can just update the template and inject more auto-generated HTML from PowerShell.