Summary: Richard Siddaway talks about using Windows PowerShell to store data for capacity planning.

 Hey, Scripting Guy! I’ve just starting learning PowerShell and I understand how to use it as a scripting language and shell. I’ve been told to start performing capacity planning for my servers, and I am wondering how I can use Windows PowerShell to help with this task.

—PH

 Hello PH,

Honorary Scripting Guy, Richard Siddaway, here today filling in for my good friend, The Scripting Guy. As part of The Admin’s First Steps series, this is the second of three posts about capacity planning. Hopefully, this will answer your question.

• To catch up, see The Admin’s First Steps: Capacity Planning Part 1.
• To view all the previous posts in this series, see The Admin’s First Steps Series.

In the last post, you saw how to use Windows PowerShell to collect data for capacity planning purposes. Techniques to collect data on disk space and network usage were shown. Capacity planning is all about observing trends, which means that you need to store the data somewhere.

Windows PowerShell doesn’t supply a storage mechanism, but it does supply the means to interact with your chosen storage to enable you to add data—and maybe more importantly, get data back out of storage for creating your reports. Your boss isn’t going to want a table of figures; he’ll want nicely formatted pretty graphs…but that’s a problem for next time.

You have a number of options for storing data:

• Flat files, such as .csvfiles
• XML
• Excel
• Database

Which one you choose comes down to your personal choice and the tools that you have available. For the purposes of this post, I’m going to use SQL Server. The techniques presented here could be easily adapted to another database such as Access.

Windows PowerShell is famous for providing multiple ways of achieving a task, and working with SQL Server is no different. You have two main options:

• Use the Invoke-SQLCommand cmdlet from the SQL Server tools
• Use ADO.NET in a script

Using the cmdlet is the simplest method, and it reduces the amount of work you have to perform. ADO.NET isn’t dependent on anything else, so it’s available on any computer. I’m going to show you both methods, starting with the cmdlet.

I’m going to assume that you have a database available with a table created. I’m going to use CAPREP as my database. Following on from last week’s post, I’m going to concentrate on the disk capacity data. You need a table in the database to store this data. I’m going to use DiskCAP as my table name and create it with the structure shown in the following image:

Note  If you don’t know how to create the database and table, ask your nearest friendly database administrator.

I know that this isn’t the best table structure that could be created, but its designed to illustrate how to use Windows PowerShell against a SQL Server table, not be a treatise on database design. I’m going to use SQL Server 2008 for my scripts, but that is only because I already have a machine built with that configuration. Any of the later versions of SQL Server would work just as well for your purposes, as would the free SQL Server Express.

The way you normally learn how to use SQL Server is that you are shown how to select data out of the database first, and then you are shown how to add data. You don’t learn how to create your capacity planning reports until the next week. So I’m going to reverse that order and start with inserting data.

A data insertion query can take a number of forms, but this is the one I prefer:

$query = “INSERT DiskCAP

(Rundate, ComputerName, Drive, Capacity, Freespace, PercentUsed, PercentFree)

VALUES (‘$date’, ‘$($_.PSComputerName)’, ‘$($_.Caption)’, $($_.Capacity_GB), $($_.FreeSpace_GB), $($_.PercentUsed), $($_.PercentFree) )”

Create a string starting with the INSERT command and the name of table (in this case, DiskCap). The table’s column names are listed next in a comma-separated list surrounded by parentheses. There are ways to avoid listing the columns, but I prefer to do it this way so that I have an explicit record of what I’m doing. Never assume—it will bite you one day.

The VALUES command is next, and a comma-separated set of values to insert into the columns is provide in parentheses. String values have to be surrounded by single quotation marks.

Now, how do you use that query? I included the Get-DiskCapacity function from the last post for completeness—although, notice that I’ve added the parameter attribute to enable the function to take pipeline input.

function get-diskcapacity {

[CmdletBinding()]

param (

 [parameter(ValueFromPipeline=$true,

   ValueFromPipelineByPropertyName=$true)]

 [string[]]$computername = $env:COMPUTERNAME

)

PROCESS {

foreach ($computer in $computername) {

Get-CimInstance -ClassName Win32_LogicalDisk -Filter “DriveType = 3” -ComputerName $computer |

select PSComputerName, Caption,

@{N=’Capacity_GB’; E={[math]::Round(($_.Size / 1GB), 2)}},

@{N=’FreeSpace_GB’; E={[math]::Round(($_.FreeSpace / 1GB), 2)}},

@{N=’PercentUsed’; E={[math]::Round(((($_.Size – $_.FreeSpace) / $_.Size) * 100), 2) }},

@{N=’PercentFree’; E={[math]::Round((($_.FreeSpace / $_.Size) * 100), 2) }}

} # end foreach

} # end PROCESS

}

 

$sess = New-PSSession -ComputerName w08r2sql08

Invoke-Command -Session $sess -Command {Add-PSSnapin SqlServerCmdletSnapin100}

Import-PSSession -Session $sess -CommandName ‘Invoke-Sqlcmd’

 

$date = (Get-Date -Format ‘yyyyMMdd HH:mm:ss’).ToString()

$servers = ‘server02′,’w12sus’, ‘win12r2’

$servers | get-diskcapacity |

foreach {

 $query = “INSERT DiskCAP

(Rundate, ComputerName, Drive, Capacity, Freespace, PercentUsed, PercentFree)

VALUES (‘$date’, ‘$($_.PSComputerName)’, ‘$($_.Caption)’, $($_.Capacity_GB), $($_.FreeSpace_GB), $($_.PercentUsed), $($_.PercentFree) )”

Invoke-Sqlcmd -Query $query -ServerInstance w08r2sql08 -Database CAPRep

}

 

Remove-PSSession $sess

A Windows PowerShell remote session is created to the machine running SQL Server after the function definition. The SQL Server snap-in, which contains the cmdlets I want, is loaded into the session. In later versions of SQL Server, this line should become:

Invoke-Command –Session $sess –Command {Import-Module sqlps}

The session is imported into my current Windows PowerShell session, but I limit the commands that are imported to Invoke-Sqlcmd. This avoids all of the warnings you’d normally get about commands not being imported because they already exist.

The date is formatted so that SQL Server will accept it. I’ve used a culture-neutral format of year, month, day and then time. The list of servers from which I want to retrieve data is defined; however, they could just as easily be pulled from Active Directory, a .csv file, or even your capacity reporting database.

The list of servers is piped into the function, a query is created for each object, and Invoke-SQLcmd is used to run it. The database and server instance need to be supplied to Invoke-SQLcmd so that it can find your database table.

The final action of the script removes the remote session to the machine running SQL Server and destroys the module that is holding the imported commands.

You might not be able to use Invoke-SQLcmd for a number of reasons. The most common reason is that you aren’t allowed to enable remoting on machines running SQL Server. This is still a common occurrence. In that case, you need to drop back to ADO.NET, and the code changes to this:

function get-diskcapacity {

[CmdletBinding()]

param (

 [parameter(ValueFromPipeline=$true,

   ValueFromPipelineByPropertyName=$true)]

 [string[]]$computername = $env:COMPUTERNAME

)

PROCESS {

foreach ($computer in $computername) {

Get-CimInstance -ClassName Win32_LogicalDisk -Filter “DriveType = 3” -ComputerName $computer |

select PSComputerName, Caption,

@{N=’Capacity_GB’; E={[math]::Round(($_.Size / 1GB), 2)}},

@{N=’FreeSpace_GB’; E={[math]::Round(($_.FreeSpace / 1GB), 2)}},

@{N=’PercentUsed’; E={[math]::Round(((($_.Size – $_.FreeSpace) / $_.Size) * 100), 2) }},

@{N=’PercentFree’; E={[math]::Round((($_.FreeSpace / $_.Size) * 100), 2) }}

} # end foreach

} # end PROCESS

}

 

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=W08R2SQL08; Initial Catalog=CAPRep; Integrated Security=SSPI”)

$date = (Get-Date -Format ‘yyyyMMdd HH:mm:ss’).ToString()

$servers = ‘server02′,’w12sus’, ‘win12r2’

 

$servers | get-diskcapacity |

foreach {

$conn.Open()

$cmd = $conn.CreateCommand()

$cmd.CommandText = “INSERT DiskCAP

(Rundate, ComputerName, Drive, Capacity, Freespace, PercentUsed, PercentFree)

VALUES (‘$date’, ‘$($_.PSComputerName)’, ‘$($_.Caption)’, $($_.Capacity_GB), $($_.FreeSpace_GB), $($_.PercentUsed), $($_.PercentFree) )”

$cmd.ExecuteNonQuery()

$conn.Close()

}

The Get-DiskCapacity function is defined as previously. This time New-Object is used to create a connection string to the database as follows:

• Data source = the SQL Server instance
• Initial Catalog = the database
• Integrated Security=SSPI means use your Windows account

The date and the servers are defined as previously, and the server names are piped into Get-DiskCapacity. This function’s output is piped into Foreach-Object where the connection to SQL Server is opened and a command object IS created from the connection. The INSERT query you saw in the earlier example is used as the CommandText for the command, which is executed by using the ExecuteNonQuery() method. This method doesn’t expect any return data. The connection is then closed.

There are a number of ways to work with ADO.NET through Windows PowerShell. I prefer this method because I find it simple to use, and I know it works—definitely a result of finding something that works and sticking with it.

Ways you could extend this include:

• Create a module for gathering data, and add a function to manage the queries in SQL Server
• Test that data has been added to SQL Server and it looks correct

PH, that is how you store data for your capacity planning. Next time we’ll look at using the data to create reports.

Bye for now.

~Richard

Thanks, Richard. I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy