SCOM: An SSRS Custom Report for SNMP Device Performance Data Collected by Rules

While the SCOM Reporting implementation provides a great set of reports out of the box, there are a number of custom reports which I have found useful to develop.   The report described here is one to report on aggregated hourly performance counters collected on SNMP Network Devices.

First up, the queries:

Aggregated performance data can be accessed in the OperationsManagerDW database, in the Perf.vPerfDaily and Perf.vPerfHourly views.   Raw performance data  can be accessed in the Perf.vPerfRaw view, and the underlying data for these views are groomed according to their configuration in the DW database grooming settings.   Raw data are sure to be cumbersome and purged more aggressively than the hourly or daily aggregates, so in this report I utilized the Perf.vPerfHourly view as the primary source of the data, but the Perf.vPerfDaily view could easily be substituted. 

A number of joins are required to map these data values to specific counters and managed entities though.   Assuming a ManagedEntityRowID is passed as a parameter named @Device, a start date and end date are passed as parameters @DateStart and @DateEnd, and an array of performance counter names (vDisplayString.Name) are passed as (@PerfCounter), the query I use is:

SELECT 
   vPerformanceRule.ObjectName,
   vPerformanceRule.CounterName,
   vDisplayString.Name as PerfCounter,
   vPerformanceRuleInstance.InstanceName,
   vPerformanceRuleInstance.LastReceivedDateTime,
   Perf.vPerfHourly.SampleCount,
   Perf.vPerfHourly.AverageValue,
   Perf.vPerfHourly.MinValue,
   Perf.vPerfHourly.MaxValue,
   Perf.vPerfHourly.StandardDeviation,
   Perf.vPerfHourly.DateTime,
   vManagedEntity.ManagedEntityRowID
FROM
vRule
INNER JOIN vPerformanceRule
   ON (vRule.RuleRowID = vPerformanceRule.RuleRowID)
INNER JOIN vPerformanceRuleInstance
   ON (vRule.RuleRowID = vPerformanceRuleInstance.RuleRowID)
INNER JOIN Perf.vPerfHourly
   ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowID =
      Perf.vPerfHourly.PerformanceRuleInstanceRowID)
INNER JOIN vDisplayString
   On (vRule.RuleGUID = vDisplayString.ElementGUID)
INNER JOIN vManagedEntity 
   ON (Perf.vPerfHourly.ManagedEntityRowID =
    vManagedEntity.ManagedEntityRowID)
WHERE
   vPerformanceRule.ObjectName = 'Snmp Performance Object'
AND
   vManagedEntity.ManagedEntityRowID = @Device
AND
   vDisplayString.Name IN (@PerfCounter)
AND
   (Perf.vPerfHourly.DateTime >= @DateStart
    AND Perf.vPerfHourly.DateTime <= @DateEnd)
ORDER BY [DATETIME] DESC

A quick diagram of the view relationships in this query looks like:

Note: Though many of these views are identical to the table by the same name, the views are defined with the NOLOCK query hint, so it’s a good idea to use the views to retrieve the data instead of the raw table when reporting. 

As for retrieving a list of SNMP Network Devices, it’s not quite as simple as querying the ManagedEntity view, assuming we want to return the actual SNMP device name, and not just the entity’s display name (which is the IP address in SCOM).   The SNMP device name is a property, retrievable from the vManagedEntityPropertySet view.  However, a GUID is used to identify the property, and this will vary in different OperationsManagerDW databases.   So, to query the list of SNMP Network Devices along with the Device Name property, I first perform a query to the vManagedEntityType view joined with the ManagedEntityTypeProperty view in order to determine the PropertyGUID for the SNMP Network Device’s Device Name property.

Declare @PropGUID as varchar(40)
Set @PropGUID =
(SELECT
  TOP 1 vManagedEntityTypeProperty.PropertyGuid
FROM
  vManagedEntityType
INNER JOIN
  vManagedEntityTypeProperty
ON
  (vManagedEntityType.ManagedEntityTypeRowID =
   vManagedEntityTypeProperty.ManagedEntityTypeRowID)
WHERE
  (vManagedEntityType.ManagedEntityTypeSystemName =
   'Microsoft.SystemCenter.NetworkDevice')
AND
  (vManagedEntityTypeProperty.PropertyDefaultName =
   'Device Name')
)
 

This assigns the GUID to the @PropGUID variable.   Then using that variable, I query the vManagedEntity view joined with the vManagedEntityType and vManagedEntityPropertySet views to return a list of SNMP Network Devices with their ManagedEentityRowID, Name, and Device Name (as DisplayName).

SELECT
   vManagedEntity.ManagedEntityRowID,
   vManagedEntity.Name,
   Upper(vManagedEntityPropertySet.PropertyValue) as DisplayName
FROM
   vManagedEntity
INNER JOIN
   vMangedEntityType
ON
  (vManagedEntity.ManagedEntityTypeRowID =
   vMangedEntityType.ManagedEntityTypeRowID)
INNER JOIN
   vManagedEntityPropertySet
ON
   (vManagedEntity.ManagedEntityTypeRowID =
    vManagedEntityPropertySet.ManagedEntityTypeRowID)
WHERE
   (vMangedEntityType.ManagedEntityTypeSystemName =
    'Microsoft.SystemCenter.NetworkDevice')
AND
   (vManagedEntityPropertySet.PropertyGUID = @PropGUID)
AND
   (vManagedEntityPropertySet.PropertyValue <> '')
ORDER BY
   vManagedEntityPropertySet.PropertyValue

And the last query is to retrieve a list of SNMP performance counters associated with the selected device (the @Device parameter is the report parameter defined by the selected SNMP Network Device in the previous query).

SELECT DISTINCT
   vDisplayString.Name As PerfCounter
FROM
   vRule
INNER JOIN
   vPerformanceRule
ON
   (vRule.RuleRowID = vPerformanceRule.RuleRowID)
INNER JOIN
   vPerformanceRuleInstance
ON
   (vRule.RuleRowID = vPerformanceRuleInstance.RuleRowID)
INNER JOIN
   Perf.vPerfHourly
ON
   (vPerformanceRuleInstance.PerformanceRuleInstanceRowID =
    Perf.vPerfHourly.PerformanceRuleInstanceRowID)
INNER JOIN
   vDisplayString
ON
   (vRule.RuleGUID = vDisplayString.ElementGUID)
INNER JOIN
   vManagedEntity
ON
  (Perf.vPerfHourly.ManagedEntityRowID =
   vManagedEntity.ManagedEntityRowID)
WHERE
  (vPerformanceRule.ObjectName = 'SNMP Performance Object')
AND
  (vManagedEntity.ManagedEntity.RowID = @Device)
ORDER BY
   PerfCounter

Creating the Report

An overview of the report creation is as follows:

• Assuming a Report Server Project and data source for the OperationsManagerDW database have already been created in Visual Studio, add a new report.  
• Use the first query in the Report Wizard and choose the grouping and display options (these queries accept a single device parameter, but a multivalue performance counter parameter, so configure a group based on the PerfCounter field, with DateTime, SampleCount, AverageValue, MinValue, MaxValue, and StandardDeviation for the Details).
• Once the report is created, add two new DataSets under the Data tab, one for the SNMP Devices query (both the query to set the @PropGUID value and the query to return the device list)  and one for the Performance Rules query.   Insert the queries into the appropriate Datasets
•  Four Report Parameters need to be configured:
  • Device (String typ.  Available values from the SNMP Devices query using ManagedEntityRowID as the value and DisplayName as the label)
  • PerfCounter (String type.  Available values from the Performance Rules query, using PerfCounter as the value and the label)
  • DateStart (DateTime type.  Optionally assign a non-queried default value using dateadd(“d”,-8,now) to set the default start date to 8 days prior)
  • DateEnd (DateTime type. Optionally assign a non-queried default value using dated(“d”,-1,now) to set the default end date to 1 day prior)
  • Format the report fields as you like and add a text box that references Parameters!Device.Label to show the selected device name in the report
  • Add a chart (drag from the toolbox), set its chart type to Smooth Line.  The chart Values should reference Fields!AverageValue.Value and optionally the MinValue and/or MaxValue fields.  The chart Category Groups should reference Fields!DateTime.Value (for the x axis).   The chart Series Groups should reference Fields!PerfCounter.Value.
  • Format the chart size, display settings, and legend to your desired settings.

Once the report is deployed, you can access this report via the Reporting Services web site or in the SCOM console Reporting section.