The Surly Admin

Father, husband, IT Pro, cancer survivor

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.

Beginnings

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.

The 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?

diskusage1Not bad right, but doesn’t show off much.  So just for grins I lowered the $AlarmPercent to 50:

diskusage2

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.

About these ads

February 27, 2014 - Posted by | PowerShell - HTML Reporting | , , , ,

8 Comments »

  1. […] If you liked reading about this, you might want to take a look at my new advanced function ConvertTo-AdvHTML.  It allows for far greater control over your HTML […]

    Pingback by How to Create HTML Reports « The Surly Admin | February 27, 2014 | Reply

  2. […] a follow-up to the ConvertTo-AdvHTML function, I have also written a much simpler version that simply changes the color of a cell.  The […]

    Pingback by HTML Reporting – A Simple Cell Color Changer « The Surly Admin | March 3, 2014 | Reply

  3. […] you can use your script with any cmdlet, or even other people’s scripts!  That’s why ConvertTo-AdvHTML even works!  I had a lot of extra code in the script that allowed the output to be just an […]

    Pingback by New Scripts – Bandwidth Report and Google Charts « The Surly Admin | April 2, 2014 | Reply

  4. […] limited to CSV.  Out-Gridview, Format-List, Format-Table, ConvertTo-HTML, ConvertTo-CLIXML, ConvertTo-AdvHTML (that’s mine) are all on the table.  When I, and other PowerShell people, talk about the […]

    Pingback by Exporting User Information « The Surly Admin | July 14, 2014 | Reply

  5. Hi Martin,
    great function again !
    I just wanted to know how is supposed to be used the paramter -Css ? Could you give an example please ?
    I have tried things like : -CSS ” $(Get-content $css)” (where $css is the filepath to my css file)
    but it doesnt seem to work.
    thanks

    Comment by Olivier | September 4, 2014 | Reply

    • Forget it, it actually works fine. Great function

      Comment by Olivier | September 4, 2014 | Reply

  6. Hi Martin, Nice script !

    When I try to use the -Fragment parameter. I get an error. Should is option work the same as when using the ConvertTo-Html command ?

    ConvertTo-Html : Parameter set cannot be resolved using the specified named parameters.
    At P:\POSHscripts\BWDBA_WebDashboard\BWDBA_Misc.psm1:279 char:39
    + $Html = $Data | ConvertTo-Html <<< Works
    ————————————————————-
    $BackupResultDetail | Select -property Instance,Database,RecoveryModel,BackupType,BackupStatus,StartTime,EndTime,DaysAgo | ConvertTo-HTML -Fragment

    Using ConvertTo-AdvHTML –> Gives error
    ————————————————————-
    $BackupResultDetail | Select -property Instance,Database,RecoveryModel,BackupType,BackupStatus,StartTime,EndTime,DaysAgo | ConvertTo-AdvHTML -Fragment

    Regards,

    Stefan

    Comment by Stefan den Engelsman | September 18, 2014 | Reply

    • Hi Stefan, no there’s a known bug with -Fragment that I just haven’t cut the time out to fix!

      Comment by Martin9700 | September 18, 2014 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 218 other followers

%d bloggers like this: