Home

OpsMgr: Choose source table based on date range

Ingmar Verheij1 Comment on OpsMgr: Choose source table based on date range

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).

Do 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.


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.

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.

 

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'.).


 
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'.

 

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,

Gerelateerde berichten

One Comment

  1. I want the Schema of OperationManager & OperationManagerDW, for reprting.
    please suggest for reporting how should I start.
    I want to show some meaningfull/usefull report(s).
    please guide me.
    Thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

en_USEnglish
nl_NLNederlands en_USEnglish