## 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. 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 1PowerShell: Dynamically Color PosH Generated HTML:Part 2PowerShell: 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 2PowerShell: Dynamically Color PosH Generated HTML:Part 3 The Two Table Solution 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 objecthtmlReport=(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.

## Monday, July 23, 2012

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

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

In the first part of this article I discussed using CSS styles, table ids and classes to colorize individual tables.  I ended with a question about how we might be able to colorize individual rows of a table in a way that would show up in an email message.

How about coloring individual lines?  Can we use this to set a lines color depending on the value of a column? Of course.  Next time I will show how easy it is to do that although I have already given you almost all of the pieces for doing it.  We just need about three more lines of code and some knowledge about how XML works and how to make HTML do some of the work for us.

Here is the code that will colorize the individual rows based on a columns value in that row.

If you remember from before we loaded the HTML fragment into an XML DOM for manipulation.  We will use the same DOM to find and colorize the rows.

$rows=$xml.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=$xml.CreateAttribute('style')$attr.Value='background-color: red;'       [void]$rows.Item($i).Attributes.Append($attr) }} As you can see we are just adding an attribute to the row. First we find all of the row objects which are represented by the ‘tr’ tag:$rows=$xml.table.selectNodes('//tr') This is the XPath that will return all rows of a table. Next I enumerate the rows but skip the first row as it is the header. I use an explicit ‘for’ loop because we need the index to return the row object. The index is$i and is incremented in the ‘for’ statement.  The index allows us to dereference the ‘#text’ node of the ‘last child’ which is the end column of the row.

$value=$rows.Item($i).LastChild.'#text' Now that I have the value of the column it is time to test the value against our ‘threshold’. We have to cast it to an integer for this to work correctly. We also need to filter out all nodes that may be blank so I check for zero length. if($value.Length -gt 0 -and [int]$value -le$threshhold){

Once we find a column that meets our criteria we can add our attributes and set it as a color style:  This is done by creating an attribute named ‘style’ and assigning its value to be ‘background-color: red;’  just like we would type it into the HTML tag

$attr=$xml.CreateAttribute('style')
$attr.Value='background-color: red;' There we have successfully edited HTML without playing with strings again. Creating and modifying HTML this way prevents breaking the HTML because the XML DOM will never generate broken XML or broken XHTML. ##### Send Email Table With Rows Colored to Show Alerts $threshhold=10$html=gwmi win32_logicaldisk | Select-Object __SERVER,deviceid,@{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} | ConvertTo-Html -Fragment$xml=[xml]$html$rows=$xml.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=$xml.CreateAttribute('style')       $attr.Value='background-color: red;' [void]$rows.Item($i).Attributes.Append($attr)    }}$html=$xml.OuterXml|Out-String$style='<style type=text/css>#diskTbl { background-color: blue; }</style>'$body=ConvertTo-Html -head $style -body$html -Title "Disk Usage Report"|Out-String$msg=@{ To=$to   From=$from Subject="Disk usage report for$([datetime]::Now)"   BodyAsHTML=$true Body=$body}

This is what the email looks like:

Of course we can do more but look at what has been accomplished in only a couple of lines.  We can use theses two techniques to add more tables all colorized and alerted or highlighted in any manner we choose and all without playing with strings. We can be as fancy as we like now because the data is being stuffed into html fragments for us and we can then decorate the data in a very controlled fashion.

##### What is next?

Next time I will attempt to combine these techniques into a web page with multiple tables and FTP it to a public web site.  I will also show how to add external style sheets and how to inject VBScript or JavaScript into the pages generated by PowerShell.  Maybe we can even look at PowerShell generated ASP.NET pages.

Until next time….

## Sunday, July 22, 2012

### PowerShell: Dynamically Color PosH Generated HTML. - Part 1

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

PowerShell is excellent at dumping data into HTML tables.  We can gather ‘fragments’ of tabled data and combine these tables into a web page to create very complex reports. With PowerShell tools we can do this almost effortlessly and with little knowledge of HTML.  So why is this not used more often?  How come we keep seeing scripters generating hand gathered html written line by line into a file?

The best way to manage HTML is through the DOM (Document Object Model).  In IE we can access the DOM through the ‘window.document’ object.  Why not just load the document into IE and create or modify reports?

There are issues with doing this. Besides being cumbersome it does not play well when the script is run unattended under the Task Scheduler.  We also may only want to set a color or two then send the HTML as an email.  IE is overkill in most cases.

PowerShell has the ConvertTo-Html CmdLet which can do many things including generating fragments and combining fragments into a page.Here is a link to a great two-part article on how to generate HTML multi-table reports:

The strength in the above-linked approach is that it allows us to gather all manner of data into simple tables and throw them onto a page.  The page display can be modified via the injected CSS with one drawback.  If we need to send the HTML by email much of the power of CSS2 will be lost as most email HTML supports only a subset of CSS1.

Here are a couple of simple ideas and techniques that can help to extend the usefulness of the PowerShell HTML CmdLet.

1. Use the XML DOM to edit the HTML fragment.
2. Add an ID to each table fragment which will allow you to control the style of individual tables independently.
3. Use XPath to select rows and set color based on the row’s value.

Here are the techniques to accomplish the above items.

##### Load HTML Fragment into XML DOM
# get some data for demo and generate a fragment$html=gwmi win32_logicaldisk | Select-Object deviceid,@{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} | ConvertTo-Html -Fragment# load HTML into XML DOM$xml=[xml]$html That’s it. Just generate the output to the HTML converter and capture in a variable. Send the variable through the XML type accelerator and save it in the variable$xml.

But you are thinking it is HTML and not XML right?  Well it is really HTML. PowerShell generates XHTML compliant HTML which is also XML.  While we cannot load a full page because of the ‘DOCTYPE’ header line we can load a fragment because it is raw and legal XML.

The HTML we just loaded looks like this:

<table><colgroup><col/><col/></colgroup><tr><th>deviceid</th><th>PercentFree</th></tr><tr><td>A:</td><td></td></tr><tr><td>C:</td><td>3</td></tr><tr><td>D:</td><td></td></tr><tr><td>E:</td><td>87</td></tr><tr><td>F:</td><td>89</td></tr></table>

Perfectly legitimate XML because all tags are closed correctly and there is no conflicting DOC header.

Now we can modify the HTML very easily using out XML editing tools.

##### Add an ID to the Table

First let’s put an ID on the table. We will call the table id=”diskTbl”.  To add an ID we need to add an attribute to the table tag and set its value.  We do this by using the XML CreateAttribute method.

$attr=$xml.CreateAttribute('id')
$attr.Value=’diskTbl’ Next we get the table tag and append the new attribute.$xml.table.Attributes.Append($attr) That is it. The table now has an attribute called ‘id’ with our value. Want to see? <table id="diskTbl"><colgroup><col /><col /></colgroup><tr><th>deviceid</th><th>PercentFree</th></tr><tr><td>A:</td><td></td></tr><tr><td>C:</td><td>3</td></tr><tr><td>D:</td><td></td></tr><tr><td>E:</td><td>87</td></tr><tr><td>F:</td><td>89</td></tr></table> See. The attribute gets nicely tucked into the table tag and no playing with messy strings and broken HTML. Ok. The HTML is no longer ‘pretty’ printing. We can fix that later. HTML and XML don’t care what they look like so we can change it around using our XML Stream Writer later. So boys and girls, that is the story of how to manipulate HTML in the DOM. You can now add a tag “id” into your CSS and make every table a different color. ##### Add Other Attributes The Same Way #diskTbl { background-color: blue; } You could also use the technique to add a class attribute and add multiple cascading classes to your table. $attr=$xml.CreateAttribute('class')$attr.Value=’red box wrap’

Now our CSS can look like this:

,red { background-color: red; }
.blue {…. }
.box { border-collapse: collapse; border-style: solid; border-width: 1px; }
.nobox {…..}
.wrap { ….}
.nowrap {….}

Using that technique we can add additive styles to an object from a general purpose style sheet.  Now ConvertTo-Html is becoming potentially very useful.

We can use the returned html to send an HTML mail message

##### Send Colorized Email With PowerShell
$html=gwmi win32_logicaldisk | Select-Object deviceid,@{N='PercentFree';E={[math]::Round($_.Freespace/$_.Size * 100,0)}} | ConvertTo-Html -Fragment$xml=[xml]$html$attr=$xml.CreateAttribute('id')$attr.Value=’diskTbl’$xml.table.Attributes.Append($attr)$html=$xml.OuterXml|Out-String$style='<style type=text/css>#diskTbl { background-color: blue; }</style>'$body=ConvertTo-Html -head $style -body$html -Title "Disk Usage Report"|Out-String$msg=@{ To=$to   From=$from Subject="Disk usage report for$([datetime]::Now)"   BodyAsHTML=$true Body=$body}Send-MailMessage @msg

Of course this only shows one table but, if we can color one, we can color as many as we like.  The important thing is that we can set style elements for individual table.  We can also add additive style for convenience.  We have also done this without dumping to and from a file and without playing with strings.

##### More?

How about coloring individual lines?  Can we use this to set a lines color depending on the value of a column? Of course.  Next time I will show how easy it is to do that although I have already given you almost all of the pieces for doing it.  We just need about three more lines of code and some knowledge about how XML works and how to make HTML do some of the work for us.