PowerShell Pro!

PowerShell Tutorial 11 - Part 3: Scripting with Windows Management Instrumentation (WMI) - Creating Reports

In this, the last installment of the WMI tutorials we tackle creating reports. Up until this point we have only been concern with outputting our results to the console. Being that we are in the real world and we work for bosses that like little "picture thingys" and reports, we need to send results to files. There is one small problem… the WMI Scripts on this site (so far) and the scripts in the Microsoft Script Repository only output results to the screen (Write-Host). When using "Write-Host, output is sent to the console and that's it, the data no longer exists, there is nothing to redirect (>) or pipe (|) to another cmdlet or file. Nothing is wrong with the "Write-Host" cmdlet, it's functioning as it was designed (send output to the screen). To provide you with an example of why "Write-Host" is not a good choice for report writing do the following example:

We will use an edited version of the List BIOS Information script found in the Microsoft Scripting Repository.
Here is the code:

Save the code as BIOS.ps1 and let's attempt to redirect the output to a file called BIOS.txt. 

-or-

In both examples; the script runs, outputs results to the screen, creates a text document called BIOS.txt, but no information is redirected (or piped) to the file. The reason this happens it the information has been outputted to the screen, using Write-Host, prior to piping the information. In essence there is nothing to redirect… 

Writing to a text file

The MS scripts are fine if you are running the script against one or two machines and console output is all you need. In the real world you will want to save the information for processing at a later time and/or you will be gathering more data than the console buffer will be able to show (you could always increase the buffer but why?).

To output the BIOS.ps1 script to a file, we are required to modify the code. Most example I have seen look similar to the following:

Changes:

• Replaced "Write-Host" with "Write-Output" cmdlet.
• Piped the result of the "Write-Output" cmdlet to the "Out-File" cmdlet and supplied the proper parameters.

When running the script, no information is output to the console, but information is available in the BIOS.txt file. For my taste this is not exactly what I want, even though we are successfully writing output to a file. I want to be able to choose whether to output to the console or pipe to a file. And I only want to use one script to accomplish both tasks.

Here is my example of how to modify the MS Script to fulfill my requirement. Again, here is the original code:

Here is how I modify Microsoft's' code. Change the code in BIOS.ps1 to:

Changes:

• I remove the "Write-Host" and "Write-Output" cmdlets all together.
• Then I concatenate the text sting and the object variable using the (+) operator.
• I added a NULL string(" ")  to add a blank line(separator) at the end of the output.

Now I have a script that will accommodate my requirements.

1. I can run the script and the results are printed to the console:

2. I can run the script and send the output to a text file:

Outputs to a text filed called Bios.txt

Appends data to the file Bios.txt

Outputs to the text file.

Appends data to the file.

Converting Microsoft PowerShell Scripts

Let's demonstrate, by using the "find" and "replace" functions built into notepad, how to convert the Microsoft Scripts (or any other PowerShell Script you may have) to this format.

Here is to code we are going to work with, yep another Microsoft Script:

1. Save the script as NetAdp.ps1
2. Open the script in Notepad (if not already open).
   
3. Go to the Edit menu and choose "Replace…" -or- Ctrl+H
4. Let's remove the Write-Host cmdlet from the script. In the "Find What:" text box enter - Write-Host. In the "Replace with" text box enter - nothing (nada, nill, null). Should look like this:
5. Click the "Replace All" button. All instances of "write-host" should now be removed. The Script should resemble this:
6. Next, we concatenate the strings and variables. At first glance it looked like we could find ($) and replace with (+ $) but, this wont work as it would find "$strComputer" and replace is with "+ $strComputer" which would blow up the script. We would also screw up $objItem and $colItems variables, rendering the script inoperable. On closer inspection you should see a pattern that is only used in the script block and would not affect $strComputer. That pattern is (" $). So, in "Find what:" we search for " $ and in "Replace with:" we use " + $ in the text box (don't forget the spaces). FYI - in (" + $|) the (|) is my cursor not a pipe:
   
7. Click "Replace All" button to concatenate the lines. Congrats your script looks as follows and is now ready to output data to either the console or a file.
   
8. Let's verify that we have provided a solution for our requirement.
   Run Script and send output to the console:
   
   .\NetAdp.ps1

   Results sent to the console, excellent!
   Run Script and send output to a file called C:\MyScripts\NetworkAdapterInfo.txt
   

   .\NetAdp.ps1 > "C:\MyScripts\NetworkAdapterInfo.txt"

   File was created and results captured.

This issue has been previously written about in "A Problem with the Microsoft Script Repository" article posted previously on this site. Don Jones from Sapien Technologies was kind enough to chime in and explain the issue further.

"Some background: Write-Host sends objects directly to Out-Host, which displays them on the screen. There’s no chance for redirection to step in.

Write-Output, on the other hand, writes objects to the pipeline. Normally, the pipeline ends in Out-Default - which redirects objects to Out-Host and thus displays them on-screen. However, if you do something like

Dir | Out-File c:\dir.txt

The objects go from the pipeline into a file. Because most of the Out-* cmdlets don’t emit objects, the pipeline after Out-File is empty, so nothing is displayed on-screen.

There IS a lot of misunderstanding about the difference between Write-Host and Write-Output. A simple example is:

Write-Output (1,2,3) | Where { $_ -gt 1 }
Write-Host (1,2,3) | Where { $_ -gt 1 }

In the first, three objects are dumped into the pipeline. Where-Object filters out the first, leaving two to go on to Out-Default and then Out-Host. So you see 2 and 3. In the second example, three objects are written to Out-Host. There’s nothing in the pipeline, so Where-Object has nothing to filter. Nothing goes to Out-Default. So you see 1, 2, and 3 - because they bypassed the pipeline and went directly to the screen.

About the only way to “redirect” Write-Host is to use Start-Transcript and Stop-Transcript, since they explicitly grab everything that appears on-screen, no matter how it gets there."

-Don Jones

I want to thank Don for taking the time in assisting the PowerShellPro readers in clearing up a common misunderstanding of the use of the "Write-Host" cmdlet.

Output results to an Excel spreadsheet

We have not discussed using com objects with powershell… yet! I'll be launching a tutorial that covers this subject more in-depth, for now I am going to show an example that uses a com object (Excel) to create a report. The script we will be using gathers Logical Disk Information and outputs the results to an Excel file:

Copy the script code and save it as "LogDiskRpt.ps1"
Run the script:

Executing this PowerShell Script launches Excel and outputs the results to the predetermined cells.

Cool stuff! Let's break down what the script does:

This first block of code Uses the "New-Object" cmdlet and the "-com" parameter to launch Excel and place the object in the $Excel variable. Next it sets the visible property to True enabling the end user to see the Excel process, meaning it is visible on our monitor. The last line of code opens a new workbook.

This block of code creates a new worksheet and places it in the $Sheet variable. Using the $Sheet variable we build the header row of our worksheet. In the $WorkBook variable section, UsedRange is an Excel worksheet property that can be used to set the range of the Excel spreadsheet. Since Excel is now an object in our powershell script, we can utilize Classes, Properties, and Methods of the Excel Com object. I'm not going to turn this into an Excel tutorial but, click here to familiarize yourself with the Methods, Properties, and Classes within Excel. Note: There are no specific examples written for PowerShell (for now) in the MSDN. Since we know that PowerShell is similar to the C# programing syntax, study the C# examples for answers.

We also added some custom formatting (color and bold) to make the header stand out.

This should look familiar. This is a standard script you would find in the Microsoft PowerShell Script Repository. Write-Host has been removed and replaced with the proper syntax for adding results to cells within the worksheet. The $intRow = 2 starts the data input on row 2, just beneath the header. Each time the script loops through the collection (foreach) we start adding data to a new row. This is done by increasing the $intRow variable by one after each pass ($intRow = $intRow + 1).

This last bit of code simply auto sizes the columns so that our report looks clean.

Now that you have a basic idea of how to create a report using an Excel spreadsheet, use this script as a template for creating your own custom reports. Utilize the MSDN to assist with more advanced customization. I also encourage you to assist others by sharing your scripts in the PowerShellPro Forum section of this site.

Alternatives to the Microsoft Script Repository examples

I've included the Microsoft examples for a couple of reasons; it's the most utilized site for beginners and because of that I wanted to assist with examples you're already familiar with. As you start to read PowerShell books you will see that different syntaxes exist for solutions. In the following examples I'm going to show how to use the "-property" parameter to yield the same results. We've discussed PowerShell Formatting and Parameters in an earlier tutorial, so let's use these principles to re-write the Microsoft examples we have used thus far.

Using WMI to get BIOS information

In this example we are going to use the "Format-List" cmdlet and the "-Property" parameter to obtain the same information the Microsoft example presented earlier in this tutorial. The following code is what I consider a proper PowerShell syntax. Notice that we are able to retrieve the same information utilizing MUCH less code than the examples in the MS Script Repository. Less code = easier to write and runs more efficient.

You can now run the code in the console or cut and paste it to a script file. Let's take the new example and create text and Excel reports.

From the console:

Open BIOSinfo.txt, were you able to capture the information? 

The next example uses the "Export-CSV" cmdlet. We are using the "Select-Object" cmdlet to choose which properties will be captured in the report. For more information on why? Get-Help Export-CSV -full. Hint… in the help file you will notice there is not a "-Property" parameter. Example 1 in the help file shows you how to use the "Select-Object" cmdlet.

Open BIOSinfo.csv and verify the data has been captured. 

Using a Script:

Save this code to a script called GetBiosInfo.ps1

Use a redirect to create a text report:

Open the text file to verify you have captured the information. 

You could use a redirect to export to CSV, for example:

But you end up with a one column report. When doing simple reports with Excel I tend to use the "Select-Object" example. 

Bonus:

For the fun of it let's capture the results to an .html file. 

Okay, it's a little ugly but browsing the help file for "ConvertTo-Html" cmdlet you will notice some cool parameters that you can use to create good looking html reports (-Head, -Title, -Body, Etc…).

Conclusion

In this tutorial we have looked at how to convert the Microsoft PowerShell Script Repository examples into a format we can use to capture data to a file. We've also found that by using what I consider "proper" PowerShell syntax, we are able to capture the same data using less script code. Why does Microsoft use the long "Write-Host" format for their examples? I can only guess that the format looks familiar to those who are coming from a VBScript background, the code format looks familiar to me…

In the next PowerShell training session we are going to look at managing Active Directory with ADSI, I'll see you then.