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")
}
Powershell Reflections 