OpsMgr: Choose source table based on date range

Author: Ingmar Verheij

When creating a report in System Center Operations Manager (SCOM) showing performance data you’ll need to make a decision about the data you’re going to show. Will you use raw data (Perf.vPerfRaw), hourly aggregated data (Perf.vPerfHourly) or daily aggregated data (Perf.vPerfDaily).

Query_thumb2Do you want to show detailed information or for a longer period? The most detail can be achieved with the data stored in Perf.vPerfRaw but this comes at a cost, the time to query the database and render the report increases massive. So if you want to show data for a longer period (like over a week) you’ll probably better use the data stored in Perf.vPerfDaily.

But what if you want the user the ability to change the date range? If the user specifies a small range (for instance a day) you want high detail, but when the range is increased (for instance a month) less detail is required.

Unfortunately the reports created in the Business Intelligence Development Studio (BIDS) does not allow you to create a conditional SQL statement. So in order to achieve this, a stored procedure needs to be created.

Stored procedure

Let’s create a stored procedure in the OperationsManagerDW database. The stored procedure queries performance data for the rule with object name ‘Processor’, instance name ‘_Total’ and counter name ‘% Processor Time’.

There are only two parameters, the start and end time.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spPerfDemo')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[spPerfDemo] AS RETURN 1')
END
GO

ALTER PROCEDURE [dbo].[spPerfDemo]
    @StartDateTime VARCHAR(255),
    @EndDateTime VARCHAR(255)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Declare variables
    DECLARE @tableSource VARCHAR(25)
    DECLARE @fieldsSource VARCHAR(100)
    DECLARE @SQLCommand  VARCHAR(MAX)

	--- Determine source table based on time range
   IF DATEDIFF(day, @StartDateTime, @EndDateTime) <= 1
      BEGIN
         SET @tableSource = 'Perf.vPerfRaw'
         SET @fieldsSource = 'vPerf.SampleValue As AverageValue, vPerf.SampleValue As MinValue, vPerf.SampleValue As MaxValue, 0 as StandardDeviation'
       END
   ELSE IF DATEDIFF(day, @StartDateTime, @EndDateTime) < 7
      BEGIN
         SET @tableSource = 'Perf.vPerfHourly'
         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'
      END
   ELSE
      BEGIN
         SET @tableSource = 'Perf.vPerfDaily'
         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'
      END

   --- Create SQL command
   SET @SQLCommand = 'SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, '+@fieldsSource+', vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName
                      FROM '+@tableSource+' As vPerf
                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId
																   FROM vPerformanceRuleInstance
																   WHERE RuleRowId in (SELECT RuleRowId
																					   FROM vPerformanceRule
																					   WHERE ObjectName = ''Processor'' AND CounterName = ''% Processor Time''
																					  )
																		 AND
																		 InstanceName = ''_Total''
																   )
	                        AND
	                        (vPerf.DateTime >= '''+@StartDateTime+''')
	                        AND
	                        (vPerf.DateTime <= '''+@EndDateTime+''')'

   --- Execute SQL command
   PRINT @SQLCommand
   EXEC (@sqlCommand)
END
GO

GRANT EXEC on [dbo].[spPerfDemo] TO OpsMgrReader
GO

Now if we run the stored procedure with a start and end time with a range of less then one day (23 hours) then the data is retrieved from Perf.vPerfRaw. Since there is no average, minimum, maximum of standard deviation I’ve changed the source fields so it always returns the same columns (which is required if you want to create a universal report.

EXEC	[dbo].[spPerfDemo]
		@StartDateTime = N'2012-04-01 0:00:00',
		@EndDateTime = N'2012-04-01 23:00:00'
GO

If you look at the Message tab (where the SQL query is printed) you’ll see that the source table is Perf.vPerfRaw.

SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, vPerf.SampleValue As AverageValue, vPerf.SampleValue As MinValue, vPerf.SampleValue As MaxValue, 0 a, vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName
                      FROM Perf.vPerfRaw As vPerf
                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId
																   FROM vPerformanceRuleInstance
																   WHERE RuleRowId in (SELECT RuleRowId
																					   FROM vPerformanceRule
																					   WHERE ObjectName = 'Processor' AND CounterName = '% Processor Time'
																					  )
																		 AND
																		 InstanceName = '_Total'
																   )
	                        AND
	                        (vPerf.DateTime >= '2012-04-01 0:00:00')
	                        AND
	                        (vPerf.DateTime <= '2012-04-01 23:00:00')

If we change the date range to more than a day (5 days)…

EXEC	[dbo].[spPerfDemo]
		@StartDateTime = N'2012-04-01 0:00:00',
		@EndDateTime = N'2012-04-05 0:00:00'
GO

…the data is retrieved from Perf.vPerfHourly .

SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation, vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName
                      FROM Perf.vPerfHourly As vPerf
                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId
																   FROM vPerformanceRuleInstance
																   WHERE RuleRowId in (SELECT RuleRowId
																					   FROM vPerformanceRule
																					   WHERE ObjectName = 'Processor' AND CounterName = '% Processor Time'
																					  )
																		 AND
																		 InstanceName = '_Total'
																   )
	                        AND
	                        (vPerf.DateTime >= '2012-04-01 0:00:00')
	                        AND
	                        (vPerf.DateTime <= '2012-04-05 0:00:00')

Dataset

Now add a dataset to the ‘Report Project’ report in the Business Intelligence Development Studio (BIDS) and select the stored procedure you’ve created earlier. Don’t forget to add the parameters for the begin and end date.

datasetPerformanceData---Query_thumbdatasetPerformanceData---Parameters_


And create the report as you normally would.

Authoring the management pack

The next step is to add the report and the stored procedure to the management. This is done by creating a Data Warehouse Script.

Data-Warehouse-Script---1_thumb2Data-Warehouse-Script---2_thumb2Data-Warehouse-Script---3_thumb2Data-Warehouse-Script---4_thumb2Data-Warehouse-Script---5_thumb2

Install script
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spPerfDemo')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[spPerfDemo] AS RETURN 1')
END
GO

ALTER PROCEDURE [dbo].[spPerfDemo]
    @StartDateTime VARCHAR(255),
    @EndDateTime VARCHAR(255)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Declare variables
    DECLARE @tableSource VARCHAR(25)
    DECLARE @fieldsSource VARCHAR(100)
    DECLARE @SQLCommand  VARCHAR(MAX)

	--- Determine source table based on time range
   IF DATEDIFF(day, @StartDateTime, @EndDateTime) <= 1
      BEGIN
         SET @tableSource = 'Perf.vPerfRaw'
         SET @fieldsSource = 'vPerf.SampleValue As AverageValue, vPerf.SampleValue As MinValue, vPerf.SampleValue As MaxValue, 0 as StandardDeviation'
       END
   ELSE IF DATEDIFF(day, @StartDateTime, @EndDateTime) < 7
      BEGIN
         SET @tableSource = 'Perf.vPerfHourly'
         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'
      END
   ELSE
      BEGIN
         SET @tableSource = 'Perf.vPerfDaily'
         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'
      END

   --- Create SQL command
   SET @SQLCommand = 'SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, '+@fieldsSource+', vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName
                      FROM '+@tableSource+' As vPerf
                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId
																   FROM vPerformanceRuleInstance
																   WHERE RuleRowId in (SELECT RuleRowId
																					   FROM vPerformanceRule
																					   WHERE ObjectName = ''Processor'' AND CounterName = ''% Processor Time''
																					  )
																		 AND
																		 InstanceName = ''_Total''
																   )
	                        AND
	                        (vPerf.DateTime >= '''+@StartDateTime+''')
	                        AND
	                        (vPerf.DateTime <= '''+@EndDateTime+''')'

   --- Execute SQL command
   PRINT @SQLCommand
   EXEC (@sqlCommand)
END
GO

GRANT EXEC on [dbo].[spPerfDemo] TO OpsMgrReader
GO
Uninstall script
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPerfDemo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].spPerfDemo]
GO
Upgrade script
ALTER PROCEDURE [dbo].[spPerfDemo]
    @StartDateTime VARCHAR(255),
    @EndDateTime VARCHAR(255)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Declare variables
    DECLARE @tableSource VARCHAR(25)
    DECLARE @fieldsSource VARCHAR(100)
    DECLARE @SQLCommand  VARCHAR(MAX)

	--- Determine source table based on time range
   IF DATEDIFF(day, @StartDateTime, @EndDateTime) <= 1
      BEGIN
         SET @tableSource = 'Perf.vPerfRaw'
         SET @fieldsSource = 'vPerf.SampleValue As AverageValue, vPerf.SampleValue As MinValue, vPerf.SampleValue As MaxValue, 0 as StandardDeviation'
       END
   ELSE IF DATEDIFF(day, @StartDateTime, @EndDateTime) < 7
      BEGIN
         SET @tableSource = 'Perf.vPerfHourly'
         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'
      END
   ELSE
      BEGIN
         SET @tableSource = 'Perf.vPerfDaily'
         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'
      END

   --- Create SQL command
   SET @SQLCommand = 'SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, '+@fieldsSource+', vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName
                      FROM '+@tableSource+' As vPerf
                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId
																   FROM vPerformanceRuleInstance
																   WHERE RuleRowId in (SELECT RuleRowId
																					   FROM vPerformanceRule
																					   WHERE ObjectName = ''Processor'' AND CounterName = ''% Processor Time''
																					  )
																		 AND
																		 InstanceName = ''_Total''
																   )
	                        AND
	                        (vPerf.DateTime >= '''+@StartDateTime+''')
	                        AND
	                        (vPerf.DateTime <= '''+@EndDateTime+''')'

   --- Execute SQL command
   PRINT @SQLCommand
   EXEC (@sqlCommand)
END
GO

GRANT EXEC on [dbo].[spPerfDemo] TO OpsMgrReader
GO


Finally you need to reference the data warehouse script from the report that’s using the stored procedure.

Report_thumb1

Pitfalls

Here are some pitfalls that you might encounter when creating your own.

Upgrade script not set

If you don’t set the upgrade script (the fourth tab) the management pack won’t import and an Event is logged from source OpsMgr SDK Service with event ID 26319.

An exception was thrown while processing ExportManagementPack for session id uuid:0cd28904-1168-4c6a-b077-e2820605d1ee;id=591.
 Exception Message: The creator of this fault did not specify a Reason.
 Full Exception: System.ServiceModel.FaultException`1[Microsoft.EnterpriseManagement.Common.ManagementPackException]: The creator of this fault did not specify a Reason. (Fault Detail is equal to : An error occured while loading management pack Id: [38484469-d094-4dd8-da34-261492f13a45] from the database
XSD verification failed for management pack. [Line: 759, Position: 15]The element 'DataWarehouseScript' has incomplete content. List of possible elements expected: 'Upgrade, UpgradeUnsupported'.).

Event-26319_thumb1

 

Permissions not granted

If you forget to grant the execute permissions to the OpsMgrReader login (see the last two lines in the install / upgrade script) the report is unable to retrieve data and the following error is thrown.

An error occurred during report processing.
Query execution failed for dataset 'datasetPerformanceData'
The EXECUTE permission was denied on the object 'spPerfDemo', database 'OperationsManagerDW', schema 'dbo'.

The-EXECUTE-permission-was-denied_th

No data in chart shown with raw data

When the data is shown in a chart and the source data is from Perf.vPerfRaw the data might not be shown. However, if you add markers you do see the data (so there is data). This happens when the following conditions apply:

  • The data from Perf.vPerfRaw
  • Displayed in a (line) chart
  • Category Groups is [DateTime]
  • There is a series group with more than one instance.

If you export the report to a comma separated file (CSV) you’ll notice that for each instance multiple records are stored but only one has a value, the number of records match the number of instances.

This is caused by the category group datetime. The line chart tries to show the data for each instance in time. Since the data is stored in raw format the timestamp doesn’t match exactly (probably off by a few milliseconds).

The is “solved” by removing the milliseconds from the datetime with the CONVERT statement and using style 120 (ODBC canonical).

SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime,