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,
Nederlands 
English
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