Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 132
Part I Laying the Foundation
However, a string object variable with the value of ‘7’ can be created as follows:
PS> [string] $counter = ‘7’
Variables can also contain objects or collections. Collections are just a group of objects, such as an array.
It’s easy to create a collection just by assigning a list of values to a variable, like this:
PS> $stuff = 1,2,4,6,8
The list of numbers is grouped into a collection of integer objects and placed in the variable $stuff.
Individual elements within the collection can be accessed by using their ordinal number:
PS> $stuff[2]
4
PS>
Addressing individual elements of an array is nice, but the power of collections is realized by being able
to iterate through the members of the collection. PowerShell offers two versions of
foreach logic, the
first being a cmdlet to which a collection is piped, like this:
PS> $stuff | foreach-object {write-output $_}
1
2
4
6
8
Notice the variable $_, which is defined as the current object in the set of objects the cmdlet is iterating
through. The other version is the
foreach language element, which enables naming of the member:
PS> foreach ($thing in $stuff) {write-output $thing}
1
2
4
6
8

Now, within the script block operating on the members of the collection, conditions can be checked.
For example, if the script should only operate on elements with a value not greater than 4, the script
would read as follows:
PS> $stuff | foreach-object { if ($_ -gt 4) {break}
else {write-output $_}}
1
2
4
Table 7-1 shows most of the comparison operators within PowerShell.
132
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 133
Scripting with PowerShell 7
TABLE 7-1
Comparison Operators
Operator Description
-lt less than
-le less than or equal to
-gt greater than
-ge greater than or equal to
-eq equal to
-ne not equal to
-like like wildcard pattern matching
Suppose you have a text file called servers.txt that contains a list of servers, one server name per
line. The file might resemble something like this:
SQLTBWS
SQLTBXP
SQLTBW7
SQLPROD1
SQLPROD2

By using the Get-Content cmdlet, this list of servers can easily be brought into a PowerShell variable
as a collection by issuing the following command:
$servers = Get-Content ‘servers.txt’
Each element in the collection can be addressed by its ordinal number, so the first item is referenced
by
$servers[0], the second item by $servers[1], and so on. This ability to create collections will
come in handy later in this discussion.
Comments, always good in any language, are specified in PowerShell 1.0 by using the pound (#) charac-
ter, with the comments following that character on the line. (PowerShell 2.0 will support an additional
comment operator, enabling multi-line comments, e.g.,
<# Multi-Line Comment #>.)
Referencing the
$servers collection, each element in the collection is a string object, and string objects
have a
Length property, so the element can be tested to determine whether it has a value using the
following commands:
if ($servers[0].Length -gt 0) {
#work
}
133
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 134
Part I Laying the Foundation
Control flow is handled by the commands shown in Table 7-2.
To help with logic flow, PowerShell provides a group of ‘‘object’’ cmdlets, as shown in Table 7-3.
For example, in the earlier example of the
$servers collection, the collection can be iterated through
using the following commands:
$servers | Foreach-Object {
Write-Output $_

}
TABLE 7-2
Control Flow Commands
If
if ($val -eq "target") {
#work
}
For
For ($i=0; $i -lt 10; $i++) {
#work
}
Switch
Switch ($val) {
"Val1" {
#work
}
"Val2" {
#work
}
}
Do Until
Do {
#work
}
Until ($val -eq "target")
Do While
Do {
#work
}
While ($val -eq "target")

While
While ($val -eq "target") {
#work
}
134
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 135
Scripting with PowerShell 7
TABLE 7-3
Object cmdlets in PowerShell
Cmdlet Alias Description
ForEach-Object % Executes once for each member in the collection
Where-Object ? Filters objects based on conditions
Select-Object select Pipes only the specified properties
Sort-Object sort Sorts the objects
Tee-Object tee Sends the objects in two directions
These cmdlets will be very useful when scripting. For example, they enable iteration through a collection
of properties in objects, as shown here:
get-service | where-object {$_.Status -eq "Running"}
The preceding produced the following results:
Status Name DisplayName

Running 1-vmsrvc Virtual Machine Additions Services
Running AeLookupSvc Application Experience Lookup Service
Running Browser Computer Browser
Running CryptSvc Cryptographic Services
Running DcomLaunch DCOM Server Process Launcher
Running Dhcp DHCP Client
Running dmserver Logical Disk Manager
Running Dnscache DNS Client

Running ERSvc Error Reporting Service
Running Eventlog Event Log
Running EventSystem COM+ Event System
Running helpsvc Help and Support
Running HTTPFilter HTTP SSL
Running lanmanserver Server
Running lanmanworkstation Workstation
Running LmHosts TCP/IP NetBIOS Helper
Running MSDTC Distributed Transaction Coordinator
Running MsDtsServer100 SQL Server Integration Services 10.0
Running MSOLAP$INST01 SQL Server Analysis Services (INST01)
Running MSSQL$INST01 SQL Server (INST01)
Running MSSQL$INST02 SQL Server (INST02)
In this example, PowerShell sifts through the current services on the system and returns only those
services that are currently running. Note the braces ({}) delimiting the operation of the
where-object
135
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 136
Part I Laying the Foundation
cmdlet. These braces allow embedded operations within loop-type structures; and together with the
script content between them, are referred to as a script block.
Most important, help is always available using the Get-Help cmdlet. It can also be called
using the
help or man aliases. Either way, help can be obtained for any cmdlet; and it will
return syntax, description, and related links.
Get-Help has options to return normal, full, detailed, or
examples.
To get help on any cmdlet, type Get-Help followed by the cmdlet. To get help on Get-Help,typethe
following:

PS> Get-Help Get-Help
NAME
Get-Help
SYNOPSIS
Displays information about Windows PowerShell cmdlets and concepts.
SYNTAX
Get-Help [[-name] <string>] [-component <string[]>]
[-functionality <string[]>] [-role <string[]>] [-category <stri
ng[]>] [-full] [<CommonParameters>]
Get-Help [[-name] <string>] [-component <string[]>]
[-functionality <string[]>] [-role <string[]>] [-category <stri
ng[]>] [-detailed] [<CommonParameters>]
Get-Help [[-name] <string>] [-component <string[]>]
[-functionality <string[]>] [-role <string[]>] [-category <stri
ng[]>] [-examples] [<CommonParameters>]
Get-Help [[-name] <string>] [-component <string[]>]
[-functionality <string[]>] [-role <string[]>] [-category <stri
ng[]>] [-parameter <string>] [<CommonParameters>]
DETAILED DESCRIPTION
The Get-Help cmdlet displays information about Windows PowerShell
cmdlets and concepts. You can also use "Help {<cm
dlet name> | <topic-name>" or "<cmdlet-name> /?".
"Help" displays the help topics one page at a time. The "/?" disp
lays help for cmdlets on a single page.
RELATED LINKS
Get-Command
Get-PSDrive
Get-Member
136
www.getcoolebook.com

Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 137
Scripting with PowerShell 7
REMARKS
For more information, type: "get-help Get-Help -detailed".
For technical information, type: "get-help Get-Help -full".
Creating scripts
While it’s sometimes useful to enter ad hoc commands into PowerShell to evaluate system state or other
information, the real power of PowerShell comes with writing scripts. A good collection of scripts to
perform normal administrative functions is a sign of an effective administrator.
For example it’s a good idea to have information about the physical servers on which SQL Server is
running. Windows Management Instrumentation (WMI) provides this information through simple
queries, available to PowerShell through the
Get-WMIObject cmdlet (aliased as gwmi). A simple
script to gather this information is shown in Listing 7-1. In this script, four different WMI classes are
polled, and their results are piped into the
select-object cmdlet (aliased as select), where the
specific properties needed are retrieved. Those results are then piped into the
format-list cmdlet for
presentation.
LISTING 7-1
Get System Info
#getsysinfo.ps1
# Use WMI queries to retrieve information about the computer, operating
# system and disk devices
gwmi -query "select * from Win32_ComputerSystem" | select Name, Model,
Manufacturer, Description, DNSHostName, Domain, DomainRole,
PartOfDomain, NumberOfProcessors, SystemType, TotalPhysicalMemory,
UserName, Workgroup | format-list
gwmi -query "select * from Win32_OperatingSystem" | select Name,
Version, FreePhysicalMemory, OSLanguage, OSProductSuite, OSType,

ServicePackMajorVersion, ServicePackMinorVersion | format-list
gwmi -query "select * from Win32_PhysicalMemory" | select Name,
Capacity, DeviceLocator, Tag | format-table -Autosize
gwmi -query "select * from Win32_LogicalDisk where
DriveType=3" | select Name, FreeSpace, Size | format-table -Autosize
When this script is run on a server it will return results like this:
Name : SQLTBWS
Model : Virtual Machine
Manufacturer : Microsoft Corporation
Description : AT/AT COMPATIBLE
DNSHostName : sqltbws
137
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 138
Part I Laying the Foundation
Domain : WORKGROUP
DomainRole : 2
PartOfDomain : False
NumberOfProcessors : 1
SystemType : X86-based PC
TotalPhysicalMemory : 1073192960
UserName : SQLTBWS\Administrator
Workgroup :
Name : Microsoft Windows Server 2003 R2
Enterprise Edition|C:\WINDOWS|
Version : 5.2.3790
FreePhysicalMemory : 340816
OSLanguage : 1033
OSProductSuite : 274
OSType : 18

ServicePackMajorVersion : 2
ServicePackMinorVersion : 0
Name Capacity DeviceLocator Tag

Physical Memory 16777216 DIMM1 Physical Memory 0
Physical Memory 16777216 DIMM2 Physical Memory 1
Physical Memory 16777216 DIMM2 Physical Memory 2
Physical Memory 16777216 DIMM2 Physical Memory 3
Name FreeSpace Size

C: 60792463360 68705730560
E: 16124747776 17173573632
F: 17087807488 17173573632
Anytime a set of commands will be used repeatedly, it’s useful to encapsulate those commands in a
function. Functions must be defined before they can be used, because PowerShell is an interpretive
language and doesn’t ‘‘read ahead’’ to see what functions might be defined later.
Best Practice
B
egin all PowerShell scripts with the set of functions that will be used in the script, with the main part
of the script listed at the very end. PowerShell cannot forward-reference function code, so the function
must have been read before it’s called in the main part of the script.
138
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 139
Scripting with PowerShell 7
The basic format of a function is as follows:
Function MyFunction {
#work
}
This, of course, doesn’t do anything. Between the braces the real work needs to be coded, but most

often functions need parameters. You can add functions in a number of ways, but two ways are most
commonly used. The first, most obvious, format is like this:
Function MyFunction ($param) {
#work
}
This works fine, but the recommended method is to use a param block within the function, which
enables the specification of multiple parameters, the specification of the data type for each parameter,
and even default values for each parameter:
Function MyFunction {
param (
[int]$x = 7,
[int]$y = 9
)
#work
}
The GetSysInfo.ps1 script shown in Listing 7-1 is useful when run on an individual server, but
wouldbeevenmoresoifitcouldberunagainstallservers in the data center. By putting the working
code from
GetSysInfo.ps1 into a function (and adding the –computername parameter to each
Get-WMIObject command within the function to specify which server to run the command against) it’s
possible to iterate through the set of servers in the
$servers collection discussed earlier.
Listing 7-2 shows exactly how to do this. The function with the calls to the four WMI classes is defined
first, followed by the main part of the script. Note that before attempting to get the server information,
the main part of the script uses the WMI class
Win32_PingStatus to determine whether the com-
puter is reachable through the network. This saves time because the script doesn’t attempt to run the
four main queries against a server that doesn’t respond.
LISTING 7-2
ServerStatus.ps1

function getwmiinfo ($svr) {
gwmi -query "select * from
Win32_ComputerSystem" -computername $svr | select Name,
Model, Manufacturer, Description, DNSHostName,
Domain, DomainRole, PartOfDomain, NumberOfProcessors,
139
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 140
Part I Laying the Foundation
SystemType, TotalPhysicalMemory, UserName,
Workgroup | format-list
gwmi -query "select * from
Win32_OperatingSystem" -computername $svr | select Name,
Version, FreePhysicalMemory, OSLanguage, OSProductSuite,
OSType, ServicePackMajorVersion,
ServicePackMinorVersion | format-list
gwmi -query "select * from
Win32_PhysicalMemory" -computername $svr | select
Name, Capacity, DeviceLocator, Tag | format-table -Autosize
gwmi -query "select * from Win32_LogicalDisk
where DriveType=3" -computername $svr | select Name, FreeSpace,
Size | format-table -Autosize
}
$servers = get-content ‘servers.txt’
foreach ($server in $servers) {
$results = gwmi -query "select StatusCode from Win32_PingStatus
where Address = ‘$server’"
$responds = $false
foreach ($result in $results) {
if ($result.statuscode -eq 0) {

$responds = $true
break
}
}
if ($responds) {
getwmiinfo $server
} else {
Write-Output "$server does not respond"
}
}
The results of this script look like this:
Name : SQLTBWS
Model : Virtual Machine
Manufacturer : Microsoft Corporation
Description : AT/AT COMPATIBLE
DNSHostName : sqltbws
Domain : WORKGROUP
DomainRole : 2
140
www.getcoolebook.com
Nielsen c07.tex V4 - 07/23/2009 9:03pm Page 141
Scripting with PowerShell 7
PartOfDomain : False
NumberOfProcessors : 1
SystemType : X86-based PC
TotalPhysicalMemory : 1073192960
UserName : SQLTBWS\Administrator
Workgroup :
Name : Microsoft Windows Server 2003 R2
Enterprise Edition|C:\WINDOWS|

Version : 5.2.3790
FreePhysicalMemory : 341320
OSLanguage : 1033
OSProductSuite : 274
OSType : 18
ServicePackMajorVersion : 2
ServicePackMinorVersion : 0
Name Capacity DeviceLocator Tag

Physical Memory 16777216 DIMM1 Physical Memory 0
Physical Memory 16777216 DIMM2 Physical Memory 1
Physical Memory 16777216 DIMM2 Physical Memory 2
Physical Memory 16777216 DIMM2 Physical Memory 3
Name FreeSpace Size

C: 60792463360 68705730560
E: 16124743680 17173573632
F: 17088528384 17173573632
SQLTBXP does not respond
SQLTBW7 does not respond
SQLPROD1 does not respond
SQLPROD2 does not respond
The day after I wrote this script for this book, I was on a consulting engagement
that called for collecting server data for more than 70 SQL Server instances,
and I was able to use this script as a starting point for my data collection. After I completed
the script I wrote an article about it for the
Simple-Talk
newsletter. The article is available at
www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-
of-your-servers/

.
Errors are handled using a Trap function. The Try-Catch mechanism for error handling will be added
to PowerShell in version 2.0, but for the time being the best method is to create a
Trap function at the
beginning of the script, like this:
Function Error_Handler {
$errmsg = "Error Category: " + $error[0].CategoryInfo.Category
$errmsg = $errmsg + ". Error Object: " + $error[0].TargetObject
141
www.getcoolebook.com