My Adventures with Powershell and Charting Tools

This past week, I had the opportunity to put some of my Powershell skills to work.  In troubleshooting a problem in our Sharepoint farm, I was looking at some data and I needed to quickly get daily data into charts.  In doing a few searches, I found some good starting points to get me going.  I will post the links to those helpful blogs at the end of this post.

To begin with, I had been capturing some application pool memory information from all the servers in the farm.  The information was in the following format:

AppPoolName, TimeStamp, ComputerName, WorkingSetSize

The monitoring component would gather this information on four different (but related) application pools across five different servers.  This was nice that I had all the information in one data file, but bad because now I have to split all the data up to analyze it properly.  The data was gathered every 15 minutes over the course of a day.  Doing the math, that comes to 1,920 lines of data that has to be analyzed (24 hours, 4-15 minute points, five servers, and four application pools).

Enter Powershell!

I start by loading the Charting Tools library.  You can download the library here, if you don’t already have it.

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")

Next, I get the current date into a variable that I can use for file names and such.

$currentDate = (get-date).ToString("yyyyMMdd")

Now, I start working with the data.  I load the data from the CSV file into a variable so that I can get information about the application pools and the server names.  I will use these later to build the different charts that I need.  I wanted to create a chart for each server, and show all four application pools for that server on its graph.

$data = import-csv C:\temp\AppPoolMemoryLog_2012_06_13.csv
$servers = $data | select -ExpandProperty ComputerName -Unique | sort
$appPools = $data | select -ExpandProperty AppPool -Unique | sort

Now that I have the servers and application pool information, I can start building some loops.  I will build one loop that enumerates over each unique server in the list and an internal loop that enumerates over each unique application pool.

Now, I start building the charts.  At the top of the outer loop, I create a new instance of the chart and chartarea so that I start from scratch each time and don’t have artifacts from the last chart showing in the next one.

foreach ($server in $servers)
{
$chart = new-object System.Windows.Forms.DataVisualization.Charting.Chart
$chartarea = new-object system.windows.forms.datavisualization.charting.chartarea
$chart.width = 1500
$chart.Height = 600
$chart.Left = 40
$chart.top = 30
$chart.Name = $server
$chart.ChartAreas.Add($chartarea)
$legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
$chart.Legends.Add($legend)

At this point, we have a chart object in memory with the graphing area set up, a name for the chart (the server name), and a legend to show the application pool that each of the data series are for.  Here I start looping through the application pools that I extracted from the data.  The application pool name is added as the series name and the chart type is defined as a line chart.  The data points are filtered out by looking at the current server name and application pool name.  Each data point is then added to the series.

foreach ($AppPool in $AppPools)
{
$chart.Series.Add($AppPool)
$chart.Series[$AppPool].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Line
$points = $data | Where-Object {($_.ComputerName -eq $server) -and ($_.AppPool -eq $AppPool)}
foreach ($point in $points)
{
$datapoint = New-Object System.Windows.Forms.DataVisualization.Charting.DataPoint($point.TimeStamp, $point.WorkingSetSize)
$chart.Series[$AppPool].Points.Add($datapoint)
}
}

After all the application pools are added to the series, we have a completed graph, and it can now be saved.

$filename = "c:\temp\AppPoolMemoryCharts\" + $server + "_" + $currentDate + ".png"
$chart.SaveImage($filename, "PNG")

I was very happy to come up with this solution since it took me 35 minutes to do this manually in Excel the first time.  Using this script, I can now create the graphs I need for analysis in about 35 seconds.  The complete code listing is below.

Here are some links to sites that helped me get through this:

Richard’s Weblog – Charting With Powershell
Simple-Talk – Building a Daily Systems Report Email with Powershell

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
$currentDate = (get-date).ToString("yyyyMMdd")
$data = import-csv C:\temp\AppPoolMemoryLog_2012_06_13.csv
$servers = $data | select -ExpandProperty ComputerName -Unique | sort
$appPools = $data | select -ExpandProperty AppPool -Unique | sort
foreach ($server in $servers)
{
$chart = new-object System.Windows.Forms.DataVisualization.Charting.Chart
$chartarea = new-object system.windows.forms.datavisualization.charting.chartarea
$chart.width = 1500
$chart.Height = 600
$chart.Left = 40
$chart.top = 30
$chart.Name = $server
$chart.ChartAreas.Add($chartarea)
$legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend
$chart.Legends.Add($legend)
foreach ($AppPool in $AppPools)
{
$chart.Series.Add($AppPool)
$chart.Series[$AppPool].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Line
$points = $data | Where-Object {($_.ComputerName -eq $server) -and ($_.AppPool -eq $AppPool)}
foreach ($point in $points)
{
$datapoint = New-Object System.Windows.Forms.DataVisualization.Charting.DataPoint($point.TimeStamp, $point.WorkingSetSize)
$chart.Series[$AppPool].Points.Add($datapoint)
}
}
$filename = "c:\temp\AppPoolMemoryCharts\" + $server + "_" + $currentDate + ".png"
$chart.SaveImage($filename, "PNG")
}
Advertisements
This entry was posted in Graphing and tagged , , . Bookmark the permalink.