ConvertTo-AdvHTML – New Advanced Function for HTML Reporting
I love using ConvertTo-HTML. There, I’ve said it. The vast number of scripts I write are doing some kind of reporting function and the ability to create simple, fast, yet good looking reports with HTML has been a huge plus for PowerShell. That said though, there’s definitely room for improvement. ConvertTo-AdvHTML is an advanced function that addresses some of the current limitations with current cmdlet. But this blog post isn’t going to a long explanation of how to use the new cmdlet, instead I wanted to write a script that actually used it.
The script had it’s beginnings with the Set-AlternatingRows function I wrote some time back, but that function was very limited. Later came a new version of the Employee Directory script and this is where the ideas behind this script really began to germinate. The problem with that script was I couldn’t–well, not easily–create custom HTML line by line because I had no idea how many columns would exist in the table. And that’s the thing with ConvertTo-HTML, because it has no idea what information you’re going to be throwing at it, so how are you going to do anything with it?
One way, of course, is to use CSS and there is a great method for inserting your own CSS into the resulting report goes a long way towards making some nice looking reports, but how do you get down to the cell level? How do you change the color of a particular row? How to you put in a simple link? Or a image? So I set out adding those capabilities, and even added a cool bar graph that I had developed for the Simple Server Status script.
As I said, instead of going into a long, dry explanation of how the script works let’s instead write a simple disk information script that uses the function. First things first, get the advanced function from here. Then read the help file here.
For the script we want to be able to give the script a simple list of servers, either directly in the ComputerName parameter or from pipeline. We could expand this to accept information from Get-ADComputer but for a simple script that seemed a little too much! If the free disk space falls below a certain threshold we want the color of the row to be changed, red for being under the threshold we designated ($AlarmPercent in the Param section below) and yellow for anything that falls within 20 percent of that number–so if we say 90 is where we want red to start, then yellow would occur from 70-89. Here’s the Param section:
DC and DC2 are test servers on my network and for testing I just put them in there to make the testing phase go easier. Then, in my Begin block I put the ConvertTo-AdvHTML function, and I initialize an array variable to hold my data in. $AlarmPercent is when we want the script to react and change the color of the affected row.
The Process block is where all of the magic occurs. First I want to connect to my servers and use Get-CIMInstance to get the data. Get-CIMInstance is the replacement for Get-WMIObject and uses the WSMAN protocol instead of DCOM to get to the WMI database. The problem here is that this really only works on Windows 2012 servers and higher, anything older still wants to use DCOM. This portion of the script handles that by using New-CIMSession and trying both protocols to connect to the server. Assuming that works it will then use the Get-CIMInstance using the CIMSession we created. If for some reason that fails the script pretty much throws up it’s hands and says “whatever” and moves on.
OK, cool, we’ve loaded $Disks with our data and now we need to loop through it (there’s an object for every drive) and create our PSCustomObject with the proper data in it. This is where we start putting in our tags to tell ConvertTo-AdvHTML what we want to do. You did read the help section, right?
The idea here is we want to determine the percentage of used space, then check if the disk has moved above that threshold. $Tag is going to be the text tag that tells ConvertTo-AdvHTML what to do with the row and by clearing it if we decide to do nothing then including that variable in our object property won’t do anything at all (there’ll be nothing there). But if it does fall within our thresholds we can set $Tag to be what we want. If it falls over $AlertPercent then we make the tag [row:red] which will cause the entire row to be turned the color red. Fall between the 70-89 range and we’ll set the tag to [row:yellow]. Now let’s create our object:
I use another tag in the Usage column called “bar”. This essentially creates 2 HTML spans and set’s their color. There’s also a free text portion so I decided to put the available space (in GB) there. The bar is driven by percentages, so the first number is how far the bar should go. So in this case that would be the percentage of space used: ($Disk.Size – $Disk.FreeSpace) / $Disk.Size) * 100. The bar will calculate the rest for you so no need to specify that. After that there are 2 color designations where you can set the used percentage color of the bar, and the remainder bar. For example, if you wanted to you could set the remainder to be fire engine red if the $AlarmPercent threshold was hit. Since I’m changing the whole column no need for that.
Now we’ve created our data, time to move into the End scriptblock and create our HTML:
I don’t normally use the back tick but in this case it does make the usage a little easier to read. Take the data, use Sort by server name and drive letter, then pipe it into ConvertTo-AdvHTML. I don’t supply any CSS but ConvertTo-AdvHTML does supply a simple CSS for you. I should probably take that out the more I think about it. What do you think?
Anyway, I use the PreContent and the PostContent parameters to put a header and footer into the report and Title to name the report in your browser–ever noticed how this doesn’t work with ConvertTo-HTML? FTFY. The use of HeadWidth is critical here. It is what allows me to set the width of a particular column and the column that contains our bar graph absolutely requires this. Otherwise HTML will crush it all down to the smallest amount possible and it will just look awful. Setting a column to 0 will tell HTML to use that default behavior anyway, but using a 600 will set that column to 600px.
So, want to see the final result?
And while the script isn’t meant for production, I expect some people would like to look a the the full code:
Update 3/3/2014: So want to change the color of a cell, but don’t want to go through the hassle of ConvertTo-AdvHTML? Here’s another advanced function that might be up your alley: Set-CellColor.