PowerShell and Tables

One of PowerShell’s major strong points is its ability to interface with a variety of technologies and data sources.  This makes it a great candidate for sending ad hoc notifications or generating HTML based reports. There are many ways you can do this. I added several functions to the TechNet Script Gallery leveraging methods from Don Jones’ HTML Reports in PowerShell.

A real world example

Systems Center Operations Manager (SCOM) 2012 is Microsoft’s take on monitoring the health, performance, and availability of the technical services that IT provides, and the individual components behind them.  It provides fantastic flexibility, functionality, and extensibility.  It does not provide shiny e-mail notifications.  You get plain text, limited details on the alarm, and Microsoft’s description of the issue, which isn’t always readable by your audience:

image

So!  We can spice things up.  SCOM lets us define something called a command notification channel, that runs a command rather than sending an e-mail.  We can use this to run a PowerShell script that pulls in extra alert details, including SCOM info associated with the rule or monitor (e.g. knowledge base), information from other sources (e.g. config / inventory management systems, the server in question), and presents this information in a less bland notification e-mail.  I use the functions in this script to create e-mails like the following:

image

Inside this script and several others, I use the following functions.

ConvertTo-PropertyValue

Want to show a set of details for one object?  You end up with a single difficult to read row; the more properties you want to show, the worse it gets.  ConvertTo-PropertyValue will take that one object, extract all or specified properties, and create an array of property value objects.  A quick example:

image

image

You can pick specific properties, member types, and headers for the property and value columns.  The property order is maintained, which can be important for notifications and reports.

New-HTMLHead

This function starts building an HTML page.  It opens the necessary tags, adds a default CSS internal style sheet, and leaves the body open for you to work with.  You can specify an external CSS sheet and it will pull in the definition.

New-HTMLTable

The brunt of the code is in here.  We take in the objects, parse them with the XDocument class, add even and odd class attributes to give rows alternating colors, and spit out the table as an HTML fragment.

For the New-HTMLTable and Add-HTMLTableColor functions, I use the XDocument class from the System.XML.Linq assembly.  Don Jones uses a simpler Set-AlternatingCSSClasses based on PowerShell’s XML type accelerator.

image

Add-HTMLTableColor

Sometimes you need to draw attention to a value or a row.  With this function, you pick a column, set up logic to deduce which cells or rows to act on, specify the CSS declaration to add inline (color in this example), and you get back the modified HTML.

Taking the event table from the last example, we can add color to highlight warning and error events:

image

This function takes in the HTML table string and spits back a string; you can thus pipe it through several iterations to add multiple colors or other attributes. Here is the code for the event log table:

    #gather 20 events from the system log and pick out a few properties
        $events = Get-EventLog -LogName System -Newest 20 | select TimeGenerated, EventID, EntryType, UserName, Message

    #Create the HTML table without alternating rows, colorize Warning and Error messages, highlighting the whole row.
        $eventTable = $events | New-HTMLTable -setAlternating $false |
            Add-HTMLTableColor -Argument "Warning" -Column "EntryType" -AttrValue "background-color:#FFCC66;" -WholeRow |
            Add-HTMLTableColor -Argument "Error" -Column "EntryType" -AttrValue "background-color:#FFCC99;" -WholeRow

    #Build the HTML head, add an h3 header, add the event table, and close out the HTML
        $HTML = New-HTMLHead
        $HTML += "<h3>Last 20 System Events</h3>"
        $HTML += $eventTable | Close-HTML

    #test it out
        set-content C:\test.htm $HTML
        & 'C:\Program Files\Internet Explorer\iexplore.exe' C:\test.htm

If we want to highlight multiple levels of a value, we can use the pipeline. In this example, we add boring pastels to process handle counts above 3000, 2000 and 1000:

image

#NOTE:  When colorizing a table more than one time, be sure to cover the broadest set first (e.g. handles 1000+) moving up to the highest (e.g. handles 3000+).
   #       Otherwise the broader set will override the style.

    #This example requires New-HTMLHead, New-HTMLTable, and Add-HTMLTableColor functions

    #Get process objects to show in the table
        $processes = Get-Process
    
    #build parameter hashtabled.
        $params = @{
            Column = "Handles"
            ScriptBlock = {[double]$args[0] -gt [double]$args[1]}
        }

    #Build initial HTML table using New-HTMLTable.  Pipe the results to color the cells shades of yellow, orange, red
        $handleTable = $processes | sort handles -descending | select Name, Handles -first 30 | New-HTMLTable |
            Add-HTMLTableColor -Argument 1000 -attrValue "background-color:#FFFF99;" @params |
            Add-HTMLTableColor -Argument 2000 -attrValue "background-color:#FFCC66;" @params |
            Add-HTMLTableColor -Argument 3000 -attrValue "background-color:#FFCC99;" @params

    #build HTML head, add a h3 level title
        $HTML = "$(New-HTMLHead) <h3>Process Handles</h3>"
    
    #add the colorized table and close out the HTML tags
        $HTML += $handleTable | Close-HTML

    #create a temporary htm file and open it
        set-content C:\test.htm $HTML
        & 'C:\Program Files\Internet Explorer\iexplore.exe' C:\test.htm

Tying it all together

Lets use these functions in concert – the webpage this provides is somewhat useless, but you can repeat and repurpose the steps here across various solutions.

The basic workflow is as follows:

  • Start the HTML
  • Create and colorize HTML tables if desired
  • Add content, tables and other data to the HTML
  • Close the HTML
# Generate tables with various Process information

#This example requires and demonstrates using the New-HTMLHead, New-HTMLTable, Add-HTMLTableColor, ConvertTo-PropertyValue and Close-HTML functions.
    
    #get processes to work with
        $processes = Get-Process
    
    #Build HTML header
        $HTML = New-HTMLHead -title "Process details"

    #Add CPU time section with top 10 PrivateMemorySize processes.  This example does not highlight any particular cells
        $HTML += "<h3>Process Private Memory Size</h3>"
        $HTML += New-HTMLTable -inputObject $($processes | sort PrivateMemorySize -Descending | select name, PrivateMemorySize -first 10)

    #Add Handles section with top 10 Handle usage.
    $handleHTML = New-HTMLTable -inputObject $($processes | sort handles -descending | select Name, Handles -first 10)

        #Add highlighted colors for Handle count
            
            #build hash table with parameters for Add-HTMLTableColor.  Argument and AttrValue will be modified each time we run this.
            $params = @{
                Column = "Handles" #I'm looking for cells in the Handles column
                ScriptBlock = {[double]$args[0] -gt [double]$args[1]} #I want to highlight if the cell (args 0) is greater than the argument parameter (arg 1)
                Attr = "Style" #This is the default, don't need to actually specify it here
            }

            #Add yellow, orange and red shading
            $handleHTML = Add-HTMLTableColor -HTML $handleHTML -Argument 1500 -attrValue "background-color:#FFFF99;" @params
            $handleHTML = Add-HTMLTableColor -HTML $handleHTML -Argument 2000 -attrValue "background-color:#FFCC66;" @params
            $handleHTML = Add-HTMLTableColor -HTML $handleHTML -Argument 3000 -attrValue "background-color:#FFCC99;" @params
      
        #Add title and table
        $HTML += "<h3>Process Handles</h3>"
        $HTML += $handleHTML

    #Add process list containing first 10 processes listed by get-process.  This example does not highlight any particular cells
        $HTML += New-HTMLTable -inputObject $($processes | select name -first 10 ) -listTableHead "Random Process Names"

    #Add property value table showing details for PowerShell ISE
        $HTML += "<h3>PowerShell Process Details PropertyValue table</h3>"
        $processDetails = Get-process powershell_ise | select name, id, cpu, handles, workingset, PrivateMemorySize, Path -first 1
        $HTML += New-HTMLTable -inputObject $(ConvertTo-PropertyValue -inputObject $processDetails)

    #Add same PowerShell ISE details but not in property value form.  Close the HTML
        $HTML += "<h3>PowerShell Process Details object</h3>"
        $HTML += New-HTMLTable -inputObject $processDetails | Close-HTML

    #write the HTML to a file and open it up for viewing
        set-content C:\test.htm $HTML
        & 'C:\Program Files\Internet Explorer\iexplore.exe' C:\test.htm

If you are interested in using PowerShell for reports and other solutions like this, I would highly recommend checking out the following resources:

Lastly, two resources that I picked up ideas from:

Advertisements

3 thoughts on “PowerShell and Tables

  1. I love this script set and have turned it into a module with a bunch of changes that I like:

    New-HtmlTable takes a collection of column name / class name objects which can be used to quickly right align numbers on creation.
    I also took away the ScriptBlock and strict type checking on Add-HtmlTableColor and replaced it with positional and comparison operators:
    Add-HtmlTableColor Name -like “100*” -AttrValue …
    Add-HtmlTableColor CPU -gt 100 -AttrValue …

    I also found and fixed a bug which I left a note about on the technet script page for you; well, at least it works for me so far.

    I’d like to be able to share my (improved?) version with others. Is that okay? I couldn’t see any proper licensing terms here or on the technet script page.

    • Hi Cody,

      Glad you like it, interested to see the changes!

      You’re more than welcome to share your version. A small attribution in the comment based help notes would be nice but no one will force you.

      Thanks!

      RCM

  2. How do we compare the values within the table itself rather then argument value in Add-HTMLTableColor

    For Ex, I need to compare Usage with Budget and color code Usage accordingly.

    Name | Budget | Usage
    ———————
    ABC | 1000 | 900
    ———————
    XYZ | 500 | 510

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