Summary: Richard Siddaway talks about using Windows PowerShell to create capacity planning reports.

 Hey, Scripting Guy! I’ve just starting learning Windows 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 was wondering how I can use 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 final post of three about capacity planning.

• In The Admin’s First Steps: Capacity Planning Part 1, you learned how to gather the data you need.
• In The Admin’s First Steps: Capacity Planning Part 2, you learned how to store the data in SQL Server.
• In this final post, you will learn how to create reports that you can present to your manager so you look good.

Your first step is to get the data back from the data base. This involves using the TSQL SELECT statement. A SELECT statement has a number of options, but for your purposes, you need a simple query of the following form:

SELECT <column names> FROM <table name> WHERE <condition>

Note   If you would like to know more about using SQL Server with Windows PowerShell, leave a comment on this post, and I’ll see what can be done.

If the previous post, we learned that we can use Invoke-SQLcmd to query a remote SQL Server:

$sess = New-PSSession -ComputerName w08r2sql08

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

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

$query = “SELECT * FROM DiskCAP”

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

select Rundate, ComputerName, Drive, Capacity, FreeSpace, PercentUsed, PercentFree

Remove-PSSession $sess 

You create a remote session to the SQL Server and import the Invoke-SQLcmd cmdlet into your current session. Then create a query to extract all data from the disk capacity table, and run the query using Invoke-SQLcmd and select the data.

At this point you have a decision to make about how you want to work with the data. Your choices are:

• Extract all the data into a big .csv file, and use Windows PowerShell to split the data as you create the reports.
• Extract the data by computer into multiple .csv files, and use Windows PowerShell to split the data when your create the reports.
• Extract the data by computer drives into many .csv files, and create individual reports.

I suspect your approach will depend on the number of machines and drives on which you need to report. All you need is a different query to control the data returned:

$query = “SELECT * FROM DiskCAP”

$query = “SELECT * FROM DiskCAP WHERE ComputerName = ‘server05′”

$query = “SELECT * FROM DiskCAP WHERE ComputerName = ‘server05’ AND Drive = ‘C:'”

The queries will select all data in the table, all data for a computer named server05, and all data for drive C of server05 respectively. I’m going to go with plan A and extract all data so I only have one visit to the database. This turns my script into:

$sess = New-PSSession -ComputerName w08r2sql08

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

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

$query = “SELECT * FROM DiskCAP”

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

select Rundate, ComputerName, Drive, Capacity, FreeSpace, PercentUsed, PercentFree |

Export-Csv diskcapacity.csv -NoTypeInformation

Remove-PSSession $sess 

When we were looking at storing data, you saw that you could use ADO.NET to work with the database. This is also possible when extracting data, but it gets a little bit more complicated. To remain consistent, all of the data will be extracted from the table.

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

$conn.Open()

$cmd = $conn.CreateCommand()

$cmd.CommandText = “SELECT * FROM DiskCAP”

$dt = New-Object -TypeName System.Data.DataTable

$dt.Load($cmd.ExecuteReader())

$dt | Export-Csv diskcapacity2.csv -NoTypeInformation

$conn.Close()

A connection string is created for the server and the database as follows:

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

Note  If you need to learn more about connection strings, I recommend The Connection Strings Reference. There, you will discover how to connect to most data stores. Also, Chapter 14 of Windows PowerShell in Practice (Manning 2010) contains more information about using Windows PowerShell with SQL Server.

The connection is opened and a command is created with the text of the command set to your SQL Server query. A data table object is created to store the data in memory. The data is loaded into the data table from SQL Server by executing the Load() method of the data table. This method takes a data reader as its argument. The data reader is created on the fly by executing:

$cmd.ExecuteReader()

You could separate out the steps to create the data reader and load the data if desired:

$dt = New-Object -TypeName System.Data.DataTable

$dr = $cmd.ExecuteReader()

$dt.Load($dr)

$dt | Export-Csv diskcapacity2.csv -NoTypeInformation

The final part of creating your report is to get the data into a format that is suitable for presentation. It would be possible to use SQL Server Reporting Services and report directly from the database, but in this case, you need to publish the reports to your managers. A graphical representation of the data will give you the most impact. Excel is a good tool for performing this task, and it is a tool that you likely have available.

You have another decision to make at this point. Do you report the space used on the disk or the free space left on disk? My preference is to report the percentage free space on the disk. There are several reasons (mainly perceptual) for this, including:

• The trend is downwards, which has more impact
• Threshold appears more serious when reached
• Easier to see how much space you have available

To create a graph for the trend of free space on a particular server drive, you can run this script:

Import-Csv .\diskcapacity.csv |

where {$_.ComputerName -eq ‘server05’ -and $_.Drive -eq ‘C: ‘} |

select Rundate, PercentFree |

Export-Csv server05C.csv -NoTypeInformation

 $xl = New-Object -comobject “Excel.Application”

 $xl.WorkBooks.Open(“c:\scripts\test\Server05C.csv”)

 $xl.visible = $true

  $ws = $xl.ActiveSheet

 $xlChart = [Microsoft.Office.Interop.Excel.XLChartType]

 $chart = $ws.Shapes.AddChart().Chart

 $chart.chartType = $xlChart::xlArea

  $xlDirection = [Microsoft.Office.Interop.Excel.XLDirection]

 $start = $ws.range(“A1”)

 $Y = $ws.Range($start,$start.End($xlDirection::xlDown))

  $start = $ws.range(“B1”)

 $X = $ws.Range($start,$start.End($xlDirection::xlDown))

  $chartdata=$ws.Range(“A$($Y.item(1).Row):A$($Y.item($Y.count).Row),B$($X.item(1).Row):B$($X.item($X.count).Row)”)

 $chart.SetSourceData($chartdata)

  $chart.HasLegend = $false

  $chart.HasTitle = $true

 $chart.ChartTitle.Text = “Server05 C: drive –  Percentage Free Space Trend”

  $xlAxes = [Microsoft.Office.Interop.Excel.XlAxisType]

 $xaxis = $chart.Axes($xlAxes::xlCategory)                                      

 $xaxis.HasTitle = $true                                                        

 $xaxis.AxisTitle.Text = “Date”

  $yaxis = $chart.Axes($xlAxes::xlValue)

 $yaxis.HasTitle = $true                                                        

 $yaxis.AxisTitle.Text = “Percent Free Space”

Start by reading the .csv file that you created by reading the database. Use Where-Object to limit the data. You need to use the old-style syntax because you’re using two tests in the filter. You only need two pieces of data (RunDate and PercentFree), so select them and output to a .csv file that is named after the server and drive.

Create an object for the Excel application, and open the .csv file you just produced. Opening a .csv file is much quicker than writing the data directly into the cells of an Excel spreadsheet. Make Excel visible. The next task is to add a chart and set the chart’s type.

Note  The values of the enumerations used in this script can be found on MSDN at Microsoft.Office.Interop.Excel namespace.

You now need to define the range of data for the chart and set that data as the source for the chart. Your chart doesn’t have a legend—change the value to $true if you want one. Set the chart title and the labels for the axes, and you’re done. Your chart will look like this:

You can experiment with various chart types to find one that suits your style. If you use a line chart, you could combine multiple drives for a single server into one chart. Notice that the dates are set to the beginning of the appropriate month.

Ways you could extend this process include:

• Create a module by using the script in the three posts to gather, store, and report on your capacity data.
• Parameterize the scripts (or module functions) to maximize reuse.
• Schedule the scripts to run overnight to minimize manual work.
• Copy the graphs from the Excel spreadsheets into a Word document to produce your final report. Your goal should be to run this overnight with a single Word document as the output.

PH, that is how you create your capacity planning reports. Good luck with the capacity planning. The Admin’s First Steps Series is going to take a little rest. Next time, I’ll be starting an in-depth look at the various facets of using Windows PowerShell to access remote machines.

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