Tips and tricks for creating reports - PowerShell scripting and automation - PowerShell in Depth, Second Edition (2015)

PowerShell in Depth, Second Edition (2015)

Part 3. PowerShell scripting and automation

Chapter 33. Tips and tricks for creating reports

This chapter covers

· Working with HTML fragments

· Creating HTML-style reports

· Sending reports by email

There’s definitely a trick to creating reports with PowerShell. Remember that PowerShell isn’t at its best when it’s forced to work with text; objects are where it excels. The more you can build your reports from objects, letting PowerShell take care of turning those into the necessary text, the better off you’ll be.

33.1. What not to do

Let’s start this chapter with an example of what we think is poor report-generating technique. We see code like this more often than we’d like. Most of the time the IT pro doesn’t know any better and is perpetuating techniques from other languages such as VBScript. The following listing, which we devoutly hope you’ll never run yourself, is a common approach that you’ll see less-informed administrators take.

Listing 33.1. A poorly designed inventory report

param ($computername)

Write-Host '------- COMPUTER INFORMATION -------'

Write-Host "Computer Name: $computername"

$os = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $computername

Write-Host " OS Version: $($os.version)"

Write-Host " OS Build: $($os.buildnumber)"

Write-Host " Service Pack: $($os.servicepackmajorversion)"

$cs = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $computername

Write-Host " RAM: $($cs.totalphysicalmemory)"

Write-Host " Manufacturer: $($cs.manufacturer)"

Write-Host " Model: $($cd.model)"

Write-Host " Processors: $($cs.numberofprocessors)"

$bios = Get-WmiObject -Class Win32_BIOS -ComputerName $computername

Write-Host "BIOS Serial: $($bios.serialnumber)"

Write-Host ''

Write-Host '------- DISK INFORMATION -------'

Get-WmiObject -Class Win32_LogicalDisk -Comp $computername -Filt

'drivetype=3' |

Select-Object @{N='Drive';E={$_.DeviceID}},

@{N='Size(GB)';E={$_.Size / 1GB -as [int]}},

@{N='FreeSpace(GB)';E={$_.freespace / 1GB -as [int]}} |

Format-Table -AutoSize

The code in listing 33.1 produces a report something like the one shown in figure 33.1.

Figure 33.1. A text-based inventory report in PowerShell

It does the job, we suppose, but Don has a saying involving angry deities and puppies that he utters whenever he sees a script that outputs pure text like this. First of all, this script can only ever produce output on the screen because it’s using Write-Host. In most cases, if you find yourself using only Write-Host, you’re probably doing it wrong. Wouldn’t it be nice to have the option of putting this information into a file or creating an HTML page? You could achieve that by just changing all of the Write-Host commands to Write-Output, but you still wouldn’t be doing it the right way.

There are a lot of better ways that you could produce such a report, and that’s what this chapter is all about. First, we’d suggest building a function for each block of output that you want to produce and having that function produce a single object that contains all the information you need. The more you can modularize, the more you can reuse those blocks of code. Doing so would make that data available for other purposes, not just for your report. In our example of a poorly written report, the first section, Computer Information, would be implemented by some function you’d write. The Disk Information section is sharing information from only one source, so it’s not that bad off, but all of those Write-Host commands have to go.

Exceptions to every rule

There are exceptions to every rule.

One of us (Richard) spends a lot of time having to audit other people’s systems. This is done by starting with a standard set of scripts. The scripts are designed to produce output that’ll go directly into a Word document to produce the report (either directly written or text files that are copied into the document). In this way, the initial reports can be produced quickly so that the analysis and discussions aren’t delayed.

A number of rules are broken in these scripts, including the following:

· Output is a mixture of text and objects.

· Output is formatted.

This is a deliberate decision because it’s known exactly what’s wanted out of these scripts and how the report has to look.

So the moral of the story is output objects, but be prepared to step outside of that paradigm when you have an exceptional, and compelling, reason.

In this chapter, we’ll focus on a technique that can produce a nicely formatted HTML report, suitable for emailing to a boss or colleague. It’s one of our favorite report-production techniques, and it’s easily adaptable to a wide variety of situations.

33.2. Working with HTML fragments and files

The trick to our technique lies in the fact that PowerShell’s ConvertTo-HTML cmdlet can be used in two different ways, which you’ll see if you examine its help file. The first way produces a complete HTML page, whereas the second produces an HTML fragment. That fragment is a table with whatever data you’ve fed the cmdlet. The example produces each section of the report as a fragment and then uses the cmdlet to produce a complete HTML page that contains all of those fragments.

33.2.1. Getting the information

You begin by ensuring that you can get whatever data you need formed into an object. You’ll need one kind of object for each section of your report, so if you’re sticking with Computer Information and Disk Information, that’s two objects.

Note

For brevity and clarity, we’re going to omit error handling and other niceties in this example. You’d add those in a real-world environment.

Get-WmiObject by itself is capable of producing a single object that has all the disk information you want, so you just need to create a function to assemble the computer information, shown in the following listing.

Listing 33.2. Get-CSInfo function

function Get-CSInfo {

param($computername)

$os = Get-WmiObject -Class Win32_OperatingSystem `

-ComputerName $computername

$cs = Get-WmiObject -Class Win32_ComputerSystem `

-ComputerName $computername

$bios = Get-WmiObject -Class Win32_BIOS `

-ComputerName $computername

#property names with spaces need to be enclosed in quotes

$props = @{ComputerName=$computername

'OS Version'=$os.version

'OS Build'=$os.buildnumber

'Service Pack'=$os.sevicepackmajorversion

RAM=$cs.totalphysicalmemory

Processors=$cs.numberofprocessors

'BIOS Serial'=$bios.serialnumber}

$obj = New-Object -TypeName PSObject -Property $props

Write-Output $obj

}

The function uses the Get-WmiObject cmdlet to retrieve information from three different WMI classes on the specified computer. You always want to write objects to the pipeline, so you use New-Object to write a custom object to the pipeline, using a hash table of properties culled from the three WMI classes. Normally we prefer property names to not have any spaces, but because you’re going to be using this in a larger reporting context, we’re bending the rules a bit.

Note

If you already have a function that produces the output you need but the property names aren’t formatted in a pretty, report-friendly way, you can always change the property names using Select-Object, as we showed you in chapter 21.

33.2.2. Producing an HTML fragment

Now you can use your newly created Get-CSInfo function to create an HTML fragment:

$frag1 = Get-CSInfo –computername SERVER2 |

ConvertTo-Html -As LIST -Fragment -PreContent '<h2>Computer Info</h2>' |

Out-String

This little trick took us a while to figure out, so it’s worth examining:

1. You’re saving the final HTML fragment into a variable named $frag1. That’ll let you capture the HTML content and later insert it into the final file.

2. You’re running Get-CSInfo and giving it the computer name you want to inventory. For right now, you’re hardcoding the SERVER2 computer name. You’ll change that to a parameter a bit later.

3. You’re asking ConvertTo-HTML to display this information in a vertical list rather than in a horizontal table, which is what it’d do by default. The list will mimic the layout from the old, bad-way-of-doing-things report.

4. You’re using the –PreContent switch to add a heading to this section of the report. You added the <h2> HTML tags so that the heading will stand out a bit.

5. The whole thing—and this was the tricky part—is piped to Out-String. You see, ConvertTo-HTML puts a bunch of different things into the pipeline. It puts in strings, collections of strings, all kinds of wacky stuff. All of that will cause problems later when you try to assemble the final HTML page, so you’re getting Out-String to resolve everything into plain-old strings (but remember it’s a string object, not a string of text).

You can also go ahead and produce the second fragment. This is a bit easier because you don’t need to write your own function first, but the HTML part will look substantially the same. The only real difference is that you’re letting your data be assembled into a table rather than as a list:

$frag2 = Get-WmiObject -Class Win32_LogicalDisk -Filter 'DriveType=3' `

-ComputerName SERVER2 |

Select-Object @{Name='Drive';Expression={$_.DeviceID}},

@{Name='Size(GB)';Expression={$_.Size / 1GB -as [int]}},

@{Name='FreeSpace(GB)';Expression={

$_.freespace / 1GB -as [int]}} |

ConvertTo-Html -Fragment -PreContent '<h2>Disk Info</h2>' | Out-String

You now have two HTML fragments, in $frag1 and $frag2, so you’re ready to assemble the final page.

33.2.3. Assembling the final HTML page

Assembling the final page involves adding your two existing fragments, although you’re also going to embed a style sheet. Using Cascading Style Sheet (CSS) language is beyond the scope of this book, but the example in the following listing will give you a basic idea of what it can do. This embedded style sheet lets you control the formatting of the HTML page so that it looks a little nicer. If you’d like a good tutorial and reference to CSS, check out www.w3schools.com/css/.

Listing 33.3. Embedded CSS

$head = @'

<style>

body { background-color:#dddddd;

font-family:Tahoma;

font-size:12pt; }

td, th { border:1px solid black;

border-collapse:collapse; }

th { color:white;

background-color:black; }

table, tr, td, th { padding: 2px; margin: 0px }

table { margin-left:50px; }

</style>

'@

ConvertTo-HTML -Head $head -PostContent $frag1,$frag2 `

-PreContent "<h1>Hardware Inventory for SERVER2</h1>"

You put that style sheet into the variable $head using a here-string to type out the entire CSS syntax you wanted. That gets passed to the –Head parameter and your HTML fragments to the –PostContent parameter. You also add a header for the whole page, where you again hardcode a computer name (SERVER2).

Save the entire script as C:\Good.ps1 and run it like this:

./good > Report.htm

That directs the output HTML to Report.htm, which is incredibly beautiful, as shown in figure 33.2.

Figure 33.2. An HTML report consisting of multiple HTML fragments

Okay, maybe it’s no work of art, but it’s highly functional and frankly looks better than the on-screen-only report you started with in this chapter. Listing 33.4 shows the completed script, where you’re swapping out the hardcoded computer name for a script-wide parameter that defaults to the local host. Notice too that you’re including the [CmdletBinding()] declaration at the top of the script, enabling the –verbose parameter. Write-Verbose will document what each step of the script is doing. The next listing is a script you can build on!

Listing 33.4. An HTML inventory report script

<#

.DESCRIPTION

Retrieves inventory information and produces HTML

.EXAMPLE

./Good > Report.htm

.PARAMETER

The name of a computer to query. The default is the local computer.

#>

[CmdletBinding()]

param([string]$computername=$env:COMPUTERNAME)

# function to get computer system info

function Get-CSInfo {

param($computername)

$os = Get-WmiObject -Class Win32_OperatingSystem `

-ComputerName $computername

$cs = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $computername

$bios = Get-WmiObject -Class Win32_BIOS -ComputerName $computername

$props = @{'ComputerName'=$computername

'OS Version'=$os.version

'OS Build'=$os.buildnumber

'Service Pack'=$os.sevicepackmajorversion

'RAM'=$cs.totalphysicalmemory

'Processors'=$cs.numberofprocessors

'BIOS Serial'=$bios.serialnumber}

$obj = New-Object -TypeName PSObject -Property $props

Write-Output $obj

}

Write-Verbose 'Producing computer system info fragment'

$frag1 = Get-CSInfo -computername $computername |

ConvertTo-Html -As LIST -Fragment -PreContent '<h2>Computer Info</h2>' |

Out-String

Write-Verbose 'Producing disk info fragment'

$frag2 = Get-WmiObject -Class Win32_LogicalDisk -Filter 'DriveType=3' `

-ComputerName $computername |

Select-Object @{Name='Drive';Expression={$_.DeviceID}},

@{Name='Size(GB)';Expression={$_.Size / 1GB -as [int]}},

@{Name='FreeSpace(GB)';Expression={$_.freespace / 1GB -as [int]}} |

ConvertTo-Html -Fragment -PreContent '<h2>Disk Info</h2>' |

Out-String

Write-Verbose 'Defining CSS'

$head = @'

<style>

body { background-color:#dddddd;

font-family:Tahoma;

font-size:12pt; }

td, th { border:1px solid black;

border-collapse:collapse; }

th { color:white;

background-color:black; }

table, tr, td, th { padding: 2px; margin: 0px }

table { margin-left:50px; }

</style>

'@

Write-Verbose 'Producing final HTML'

Write-Verbose 'Pipe this output to a file to save it'

ConvertTo-HTML -Head $head -PostContent $frag1,$frag2 `

-PreContent "<h1>Hardware Inventory for $ComputerName</h1>"

Using the script is simple:

PS C:\> $computer = SERVER01

PS C:\> C:\Scripts\good.ps1 -computername $computer |

Out-File "$computer.html"

PS C:\> Invoke-Item "$computer.html"

The script runs, produces an output file for future reference, and displays the report. Keep in mind that your work in building the Get-CSInfo function is reusable. Because that function outputs an object, and not just pure text, you can repurpose it in a variety of places where you might need the same information.

To add to this report, you’d just do the following:

1. Write a command or function that generates a single kind of object that contains all the information you need for a new report section.

2. Use that object to produce an HTML fragment, storing it in a variable.

3. Add that new variable to the list of variables in the script’s last command, thus adding the new HTML fragment to the final report.

4. Sit back and relax.

Yes, this report is text. Ultimately, every report will be, because text is what we humans read. The point of this one is that everything stays as PowerShell-friendly objects until the last possible instance. You let PowerShell, rather than your own fingers, format everything for you. The working parts of this script, which retrieve the information you need, could easily be copied and pasted and used elsewhere for other purposes (you could even create a module of functions used to retrieve your data that could be used for other purposes). That wasn’t as easy to do with our original pure-text report because the working code was so embedded with all that formatted text.

Note

You can find a free ebook that goes into even greater detail on creating HTML reports at http://powershell.org/wp/ebooks/. Jeff also has a number of examples on his blog here: http://jdhitsolutions.com/blog/?s=html.

33.3. Sending email

What’s better than an HTML report? An HTML report that’s automatically emailed to whoever needs it!

Fortunately, nothing could be simpler in PowerShell, thanks to its Send-MailMessage cmdlet. Just modify the end of your script as follows:

Write-Verbose 'Producing final HTML'

Write-Verbose 'Pipe this output to a file to save it'

ConvertTo-HTML -Head $head -PostContent $frag1,$frag2 `

-PreContent "<h1>Hardware Inventory for $ComputerName</h1>" |

Out-File report.htm

Write-Verbose "Sending e-mail"

$params = @{'To'='whomitmayconcern@company.com'

'From'='admin@company.com'

'Subject'='That report you wanted'

'Body'='Please see the attachment.'

'Attachments'='report.htm'

'SMTPServer'='mail.company.com'}

Send-MailMessage @params

You modify the end of the ConvertTo-HTML command to pipe the output to a file. Then you use the Send-MailMessage command to send the file as an attachment. If you prefer, you can also send the HTML as the message body itself. You don’t need to create the text file but can take the HTML output and use it directly, although you do have to make sure the output is treated as one long string. Here’s an alternative example:

Write-Verbose 'Producing final HTML'

$body=ConvertTo-HTML -Head $head -PostContent $frag1,$frag2 `

-PreContent "<h1>Hardware Inventory for $ComputerName</h1>" | Out-String

Write-Verbose "Sending e-mail"

$params = @{'To'='whomitmayconcern@company.com'

'From'='admin@company.com'

'Subject'='That report you wanted'

'Body'=$Body

'BodyAsHTML'=$True

'SMTPServer'='mail.company.com'}

Send-MailMessage @params

Here you build the parameters for Send-MailMessage command in a hash table, which is saved into the variable $params. That lets you use the splat technique to feed all those parameters to the command at once. There’s no difference between what you did and typing the parameters out normally, but the hash table makes the script a bit easier to read.

33.4. Summary

Building reports is a common need for administrators, and PowerShell is well suited to the task. The trick is to produce reports in a way that makes the reports’ functional code—the parts that retrieve information and so forth—somewhat distinct from the formatting and output-creation code. In fact, PowerShell is generally capable of delivering great formatting with little work on your part, as long as you work the way it needs you to.