Administrative Reporting with PowerShell

Finally, IT professionals are realizing how important Windows PowerShell has become as part of their IT tools. This new technology has so many flavors that it takes time to learn them all. But thanks to our many Internet PowerShell communities, the learning curve can be reduced dramatically. Resources can be found in books, videos, conferences, and blogs all over the Internet. One more thing: This information isn't limited to IT admins. Everyone can learn and use PowerShell.

Now, we all know that PowerShell is an excellent tool for automating repetitive tasks. In this article, I'm going to show how to use PowerShell to create an administrative reporting task. At the same time, you'll learn some PowerShell during this article. But first, you need to meet some requirements:

PowerShell Version 2.0 is already included in Windows 7 and Windows Server 2008 R2. For earlier, legacy OSs such as Windows Vista Service Pack 1 (SP1), Windows XP SP3, Windows Server 2008 SP1, and Windows Server 2003 SP2, you need to download the Windows Management Framework 2.0 RTM to get PowerShell 2.0.

Getting Started

To start building your script solution, PowerShell provides you with a free editor: PowerShell ISE. Choose Start, All Programs, Accessories, Windows PowerShell ISE, as Figure 1 shows.

However, before you start building and executing scripts, you must understand a bit about the PowerShell execution policy. By default, this policy is set to Restricted, meaning that PowerShell will not execute scripts. The most important thing to understand is that the PowerShell execution policy is meant only to prevent you from harming the computer. The policy isn't meant to stop hackers or viruses. In most cases, setting the script execution policy to RemoteSigned is enough to get you working with PowerShell scripts:

Set-ExecutionPolicy RemoteSigned 

Be aware that on Windows Server 2008 and later or Windows Vista or later, you must execute this command from an elevated session.

If you want to learn more about setting the execution policy, use the following command:

Help About_Execution_Policy -Full 

PowerShell is loaded with Help information, all accessible at your fingertips. Here's another command, which displays a list of available Help content, as Figure 2 shows:

(get-help About_*) | Select name | More 

All these commands are known as PowerShell oneliners.

Now that you have the basics of getting started, let's look at a practical scenario. Suppose our IT manager is performing an audit and requests an administrative report to gather the OS serial number and system IP address on three specific systems: Server1, Desktop1, and Server2. We'll need to follow these steps:

Input: Storing Computer Names

First, we need to store our three selected computer names in a PowerShell variable:

$ComputerList = @("Server1","Desktop1","Server2");$ComputerList .gettype() 

As you can see, the PowerShell variable name starts with a dollar sign ($). This variable is a collection of Microsoft .NET Framework objects. Saving the series of computer name values, enclosed in quotation marks (") and separated by commas, is the simplest way to create a list string array. To check the type of variable that you created in PowerShell, you can add the .NET .gettype() method to the end of the variable.

By the way, if the list of computers comes in the form of a text file, then you can use the following command to create the $ComputerList variable:

$ComputerList = Get-content c:tempComputerList.txt 

This command automatically creates a string array type PSObject, and you can easily maintain the text file on a local or network drive.

Process: Using Get-WMIobject to Gather Information

Here's where PowerShell excels in getting information. We are going to use the Get-WMIObject command. (Again, you can use the Help command to read about this particular command.) Based on our manager request, we have identified that we need to use two WMI classes to produce our report, which we'll call Win32_OperatingSystem, and one WMI query to retrieve the machine IP addresses.

It's a good idea to familiarize yourself with your command by writing a oneliner. Let's run the Get-WMIObject command against our local machine, creating a PSObject variable. By saving the results to a variable, we can use the Get-Member command to display all the values that are saved in this .NET object, as Figure 3 shows.

$SavedAuditOS = Get-WMIobject -Class Win32_OperatingSystem$SavedAuditOS | GM -MemberType Property 

We use the pipe (|) to pass our variable results to another command, in this case GM. GM is an alias of the Get-Member command, which helps us to discover and expose all the fields (or properties) that are stored in our recently created variable.

From the $SavedAuditOS variable, we select the properties: CSName, SerialNumber, and Name. Here's our oneliner command to display the selected properties:

$SavedAuditOS | Select-Object CSname, SerialNumber, Name | FT -AutoSize 

We're now passing the variable to two other commands: Select-Object, and FT (alias for Format-Table). The Select-Object command helps you to display the value of the PSObject properties. The Format-Table command helps you to display the results in a table-formatted view and, with the help of the -AutoSize parameter, eliminates the additional spaces between the displayed columns.

Now, let us proceed in getting the IP address information. We're going to take a different approach with our next Get-WMIobject command by including the WMI -Query parameter:

$SavedAuditIPAddr = Get-WmiObject `-query "SELECT * FROM Win32_PingStatus WHERE Address='$MyMachineName'" `| select StatusCode, IPv4Address | ft -auto; 

This block of code is still considered a oneliner, even though it uses the backtick operator to split and make the line more readable. Also, the semicolon (;) can be used to set the end of the line. In the WMI -Query string, make sure to change the variable following Address= to include the computer name.

Note that we're using Win32_PingStatus to gather information; we aren't using Test-Connection because it returns data only if you can ping the computer. In addition, you could shorten your code a bit:

Get-WmiObject Win32_PingStatus -filter "Address=""$MyMachineName""" 

Output: Using Export-CSV to Create an Excel CSV File

Here's the last piece of the puzzle, which will allow us to create an Excel CSV file. This will take only one PowerShell command, using the previously created $SavedAuditOS variable:

$SavedAuditOS | Export-CSV -Path C:tempTestReport.csv -NoTypeInformation;ii C:tempTestReport.csv; 

This is another oneliner, in which we include the ii (an alias for Invoke-Item) command. This command allows us to open a file with the associated installed application, in this case Excel. In most cases, Excel will be the default application for viewing a CSV file. Be aware, if you don't have a CSV application installed, you'll need to use Microsoft Notepad instead of Invoke-Item. The -NoTypeInformation parameter excludes extra information about the data that was added to our exported CSV file.

Automation: Put It All together

We've gone through the steps to basic understanding of how our commands work. But there is one more puzzle piece. We have three variables: one holding the list computer, two others with WMI computer information. Here's where the PowerShell magic starts.

We need to consolidate all computer information into a single variable named $AuditOSInfo. This variable is a PowerShell hash table that contains the results of both WMI processes: the Win32_OperatingSystem and the Win32_PingStatus query. Then, using the ForEach statement, we'll go through each computer name, building the hash table with all the information.

Inside the ForEach command, we'll create two variables:

Inside the ForEach code, we build a hash table object $MyPSObject to consolidate the information for each computer and add it our variable $AuditOSInfo. Finally, after collecting all our information, we use the Export-CSV command to create our output CSV file. Listing 1 shows the full script. The script will generate the sample administrative report that Figure 4 shows.

About Our Custom Hash Table Object

This is the heart of our process. Here is where we consolidate our data into one custom object, known as the hash table. We can customize our results, merging data from different sources, and changing the label of our data value when necessary. This all happens under the -Property parameter when we create our new PSObject, as the code in Listing 2 shows.

Note that I'm using an advanced technique when creating my OSName property to get the OS description value stored in the $SavedAuditOS variable Name property using the String Split() method. This property contains three values, separated by a pipe (|), but we want only one, which is identified as element 0 in our code:

OSName = $SavedAuditOS.Name.Split("|")[0]; 

One of the three values will be stored in our custom hash table's OSName property. Taking this multi-value, pipeline-delimited string, we use the Split("|") method followed by the square bracket "[0]" to tell PowerShell to return the first element of the string.

Here's an example of a multi-value string containing three delimited elements:

$Str_name = "Microsoft Windows 2008R2|C:Windows|DeviceHarddisk0Partition2"Element # -> [0] -> [1] -> [2] 

Using the example line $Str_Name.Split("|")[0], we grab only the first element, selecting the string value "Microsoft Windows 2008 R2". As you can see, this uses the .NET string Split() method in the scenario when you need to extract a piece of the data with a delimited character.

Get Creative

For the most part, this is not a sophisticated script and is meant to be executed from a desktop. There's no reason you need to be on the server to use PowerShell. We can still add more logic or code to add more fields. You can even make the script capable of sending an email message with the CSV file attached, or you can create a scheduled task to run the script at a set date and time. There are many ways to use this script, and you can be very creative.

On a side note, when creating your custom hash tables, you will notice that your .NET object properties aren't in the same order as was initially defined. This is normal in version 2.0. Just use either the Select-Object or Format-* command and type the properties in the order that you want to be displayed.

Just remember, PowerShell has become an essential tool for administrators and can no longer be ignored. Take the time and get to know it.

Listing 1: Code to Create Output CSV File

################################################ AdminOSReport.ps1## 12/04/2011############################################## ## - Create variable with list of ComputerNames:$ComputerList = "WIN8Server1","WIN764SQL01","W764SQL02Merged"; ## - Collect and Consolidate data Task:[Array]$AuditOSinfo = `ForEach($ComputerName in $ComputerList){    ## - Getting to our WMI information:    $SavedAuditOS = get-wmiobject -Class Win32_OperatingSystem `        -ComputerName $ComputerName;      $SavedAuditIPAddr = Get-WmiObject -query `"SELECT * FROM Win32_PingStatus WHERE Address = '$ComputerName'";     ## - Step consolidating WMI information into a hash table :    $MyPSObject = New-Object PSObject -Property @{        ComputerName  = $SavedAuditOS.csname;        IPv4Address   = $SavedAuditIPAddr.IPv4Address;        IPStatusCode  = $SavedAuditIPAddr.StatusCode;        SerialNumber  = $SavedAuditOS.SerialNumber;        OSversion     = $SavedAuditOS.Version;        SystemDescrip = $SavedAuditOS.Description;        OSName        = $SavedAuditOS.Name.Split("|")[0];        };    $MyPSObject;}; ## - Generate and Open report:$AuditOSinfo | Export-CSV -Path C:tempTestReport.csv -NoTypeInformation;ii C:tempTestReport.csv;

Listing 2: Code to Create Custom Hash Table Object

## - Step consolidating WMI information into a hash table :    $MyPSObject = New-Object PSObject -Property @{        ComputerName  = $SavedAuditOS.csname;        IPv4Address   = $SavedAuditIPAddr.IPv4Address;        IPStatusCode  = $SavedAuditIPAddr.StatusCode;        SerialNumber  = $SavedAuditOS.SerialNumber;        OSversion     = $SavedAuditOS.Version;        SystemDescrip = $SavedAuditOS.Description;        OSName        = $SavedAuditOS.Name.Split("|")[0];    };