OpsMgr (SCOM) - Create Performance Charts in Powershell

It might be usefull for people who don't have Datazen or just want to create and manage their own costumized HTML views, or perhaps, for fun!

Powershell is one of the things i like the most while exploring everything that SCOM (and other System Center tools) can give to you, it just makes everything you want, a way or another.

So.

What about creating this in a Powershell script ?

This is the script that does all the magic :

!!NOTE!! : For the most problematic servers in specific groups i've i retrieve memory, cpu, and disk usage performance data, but, you change this logic as well as the performance counters.

Please attempt to the comments in-line so you can understand the logic :)

 [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")  
 Import-Module OperationsManager  
 New-SCOMManagementGroupConnection -ComputerName "opsmgr.server.local" #Change here :)  
 $script:scriptpath = "C:\PS\OpsMgr_PerfCharts\images"  
   
 # Foreach Group above list the most problematic agents   
   
 $MyGroups = @()  
 $MyGroups += Get-SCOMGroup -DisplayName 'UNIX/Linux Computer Group'  
 $MyGroups += Get-SCOMGroup -DisplayName 'All Windows Computers'  
 $newTime = (Get-Date).AddHours(-24)  
 $Criteria = New-Object Microsoft.EnterpriseManagement.Monitoring.MonitoringAlertCriteria("TimeRaised > `'$newTime`'")  
 $TransversalDepth = [Microsoft.EnterpriseManagement.Common.TraversalDepth]::Recursive  
 $TopAgents = @()  
 Foreach ( $Group in $MyGroups ) {  
   $TopObjAlerts = ($Group.GetMonitoringAlerts($Criteria , $TransversalDepth) | Group-Object MonitoringObjectPath | Sort-Object count -descending | select -first 5 Values).Values  
   Foreach ($i in $TopObjAlerts) {  
     $TopAgents += ($i -split ";")[0]  
   }  
   $TopAgents = $TopAgents.Split(";",[System.StringSplitOptions]::RemoveEmptyEntries)  
 }  
 $Count = 0  
 $Query = ""  
 Foreach ($Agent in $TopAgents) {  
   If ($TopAgents.Count -eq $Count ) {  
     $Query += "'" + $agent + "'" + ","  
   } Else { $Query += "'" + $agent + "'" }  
   $Count++  
 }  
   
 # Create SQL Query with the most problematic agents criteria   
   
 $Query = $Query -replace "''","','"  
   
 $SqlQuery = "  
 SELECT          
     Path,  
     DateTime,  
     CASE   
     WHEN vpr.ObjectName = 'Processor Information' THEN 'Processor'  
     WHEN vpr.ObjectName = 'LogicalDisk' THEN 'Logical Disk' ELSE vpr.ObjectName END as ObjectName,  
 CASE   
     WHEN vpr.CounterName = 'PercentMemoryUsed' OR vpr.CounterName = '% Available Memory' THEN '% Used Memory'   
     WHEN vpr.CounterName = '% Free Space' THEN '% Used Space'  
     ELSE vpr.CounterName END as CounterName,  
 CASE   
     WHEN len(vpri.InstanceName) < 1 THEN '_Total' ELSE vpri.InstanceName END as InstanceName,  
 CASE   
     WHEN vpr.CounterName = '% Available Memory' or vpr.CounterName = '% Free Space' THEN 100 - pvpr.AverageValue ELSE pvpr.AverageValue END as AverageValue,  
 100.00 as ComparisonValue  
 FROM   
     Perf.vPerfDaily pvpr WITH (NOLOCK)   
     inner join vManagedEntity vme WITH (NOLOCK) on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId   
     inner join vPerformanceRuleInstance vpri WITH (NOLOCK) on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId   
     inner join vPerformanceRule vpr WITH (NOLOCK) on vpr.RuleRowId = vpri.RuleRowId   
 WHERE  
     Path in (  
         select   
             unix.DisplayName  
         from   
             vManagedEntity unix WITH (NOLOCK)   
             join vManagedEntityManagementGroup unixMG WITH (NOLOCK) ON unix.ManagedEntityRowId = unixMG.ManagedEntityRowId  
             join vManagedEntityType unixType WITH (NOLOCK) on unix.ManagedEntityTypeRowId = unixType.ManagedEntityTypeRowId  
         where   
             unixMG.ToDateTime Is Null   
             and (unixType.ManagedEntityTypeSystemName = 'Microsoft.Unix.Computer'   
             or unixType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer' )  
             AND vpr.ObjectName <> 'Process'  
             AND unix.DisplayName IN ( $Query )  
     )  
 AND vpr.CounterName in ('% Processor Time','PercentMemoryUsed','% Available Memory','% Free Space')  
 AND vpri.InstanceName in ('_Total','C:','/','')  
 AND DATEDIFF(DAY, pvpr.DateTime, GETDATE()) < 8  
 "  
   
 $SqlQuery > $env:TEMP\query.sql  
   
 $SqlQuery = Get-Content $env:TEMP\query.sql  
   
 # Create connection to SCOMDW Database   
   
 $script:SQLServer = "SCOMDBSERVER\SCOMDW"  
 $script:SQLDBName = "OperationsManagerDW"  
 $script:connString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;Integrated Security = True"  
 $script:connection = New-Object System.Data.SqlClient.SqlConnection($connString)  
 $connection.Open()  
 $sqlcmd = $connection.CreateCommand()  
 $sqlcmd.CommandText = $SqlQuery  
 $results = $sqlcmd.ExecuteReader()  
 $script:table = new-object “System.Data.DataTable”  
 $script:table.Load($results)  
   
 # Lets GRAPH!  
   
 $Servers = ($table | Select -Unique Path).Path  
 # 1 Graph foreach Agent   
 Foreach ( $Server in $Servers ) {  
   $Server = ($Server -split '\.')[0]  
   $GraphName = $Server  
   # Create CHART   
   $GraphName = New-object System.Windows.Forms.DataVisualization.Charting.Chart  
   $GraphName.Width = 900  
   $GraphName.Height = 300  
   $GraphName.BackColor = [System.Drawing.Color]::White  
   # CHART Title  
   [void]$GraphName.Titles.Add("$Server - Performance Data")  
   $GraphName.Titles[0].Font = "Arial,10pt"  
   $GraphName.Titles[0].Alignment = "MiddleCenter"  
   # CHART Area and X/Y sizes   
   $chartarea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea  
   $chartarea.Name = "ChartArea"  
   $chartarea.AxisY.Title = "$CounterName"  
   $chartarea.AxisX.Title = "Time"  
   $chartarea.AxisY.Maximum = 100  
   $chartarea.AxisY.Interval = 10  
   $chartarea.AxisX.Interval = 1  
   $GraphName.ChartAreas.Add($chartarea)  
   # CHART Legend  
   $legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend  
   $legend.name = "Legend"  
   $GraphName.Legends.Add($legend)  
     # Line Colors  
     $num = 0  
   $LineColours = ('DarkBlue','Brown','DarkMagenta')  
   # Create 'n' SERIES (lines) for each COUNTER  
   Foreach ($Counter in $Counters) {  
     $LineColor = $LineColours[$num]  
     [void]$GraphName.Series.Add("$Counter")  
     $GraphName.Series["$Counter"].ChartType = "Line"  
     $GraphName.Series["$Counter"].BorderWidth = 2  
     $GraphName.Series["$Counter"].IsVisibleInLegend = $true  
     $GraphName.Series["$Counter"].chartarea = "ChartArea"  
     $GraphName.Series["$Counter"].Legend = "Legend"  
     $GraphName.Series["$Counter"].color = "$LineColor"  
     ForEach ($i in ($table | ? { $_.ObjectName -eq $Counter -and $_.Path -like "$Server*" }) ) {   
       $GraphName.Series["$Counter"].Points.addxy( ($i.DateTime -split ' ')[0] , $i.AverageValue )   
     }  
     $num++ # Next Color :)  
   }  
   $GraphName.SaveImage("$scriptpath\$Server.png","png") # Save the GRAPH as PNG  
 }  

Have fun! :)