{"id":3593,"date":"2012-04-10T22:25:24","date_gmt":"2012-04-10T20:25:24","guid":{"rendered":"https:\/\/ingmarverheij.com\/?p=3593"},"modified":"2012-04-11T09:59:37","modified_gmt":"2012-04-11T07:59:37","slug":"opsmgr-choose-source-table-based-on-date-range","status":"publish","type":"post","link":"https:\/\/ingmarverheij.com\/en\/opsmgr-choose-source-table-based-on-date-range\/","title":{"rendered":"OpsMgr: Choose source table based on date range"},"content":{"rendered":"\n<p>When creating a report in System Center Operations Manager (SCOM) showing performance data you\u2019ll need to make a decision about the data you\u2019re going to show. Will you use raw data (Perf.vPerfRaw), hourly aggregated data (Perf.vPerfHourly) or daily aggregated data (Perf.vPerfDaily).<\/p>\n<p><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Query.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 0px 0px 5px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" align=\"right\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Query_thumb.png\" width=\"254\" height=\"137\" \/><\/a>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\u2019ll probably better use the data stored in Perf.vPerfDaily.<\/p>\n<p>&#160;<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p><!--more--><\/p>\n<h4>Stored procedure<\/h4>\n<p>Let\u2019s create a stored procedure in the OperationsManagerDW database. The stored procedure queries performance data for the rule with object name \u2018Processor\u2019, instance name \u2018_Total\u2019 and counter name \u2018% Processor Time\u2019.&#160; <\/p>\n<p>There are only two parameters, the start and end time.<\/p>\n<pre lang=\"sql\">IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spPerfDemo') \nBEGIN \nEXECUTE ('CREATE PROCEDURE dbo.[spPerfDemo] AS RETURN 1') \nEND \nGO\n\nALTER PROCEDURE [dbo].[spPerfDemo] \n    @StartDateTime VARCHAR(255), \n    @EndDateTime VARCHAR(255)\nAS \nBEGIN\n\n\t-- SET NOCOUNT ON added to prevent extra result sets from\n\t-- interfering with SELECT statements.\n\tSET NOCOUNT ON;\n\n\n    -- Declare variables\n    DECLARE @tableSource VARCHAR(25)\n    DECLARE @fieldsSource VARCHAR(100)\n    DECLARE @SQLCommand  VARCHAR(MAX)\n    \n\t\n\t--- Determine source table based on time range\n   IF DATEDIFF(day, @StartDateTime, @EndDateTime) &lt;= 1 \n      BEGIN \n         SET @tableSource = 'Perf.vPerfRaw'  \n         SET @fieldsSource = 'vPerf.SampleValue As AverageValue, vPerf.SampleValue As MinValue, vPerf.SampleValue As MaxValue, 0 as StandardDeviation'\n       END\n   ELSE IF DATEDIFF(day, @StartDateTime, @EndDateTime) &lt; 7 \n      BEGIN \n         SET @tableSource = 'Perf.vPerfHourly'  \n         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'\n      END\n   ELSE\n      BEGIN \n         SET @tableSource = 'Perf.vPerfDaily' \n         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'\n      END\n    \n \n   --- Create SQL command\n   SET @SQLCommand = 'SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, '+@fieldsSource+', vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName\n                      FROM '+@tableSource+' As vPerf\n                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId\n                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId\n                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRuleInstance\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE RuleRowId in (SELECT RuleRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRule\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE ObjectName = ''Processor'' AND CounterName = ''% Processor Time''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  )\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t InstanceName = ''_Total''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   )\n\t                        AND\n\t                        (vPerf.DateTime &gt;= '''+@StartDateTime+''') \n\t                        AND \n\t                        (vPerf.DateTime &lt;= '''+@EndDateTime+''')'\n   \n   --- Execute SQL command\n   PRINT @SQLCommand\n   EXEC (@sqlCommand)    \nEND\nGO\n\nGRANT EXEC on [dbo].[spPerfDemo] TO OpsMgrReader\nGO<\/pre>\n<p>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\u2019ve changed the source fields so it always returns the same columns (which is required if you want to create a universal report.<\/p>\n<pre lang=\"sql\">EXEC\t[dbo].[spPerfDemo]\n\t\t@StartDateTime = N'2012-04-01 0:00:00',\n\t\t@EndDateTime = N'2012-04-01 23:00:00'\nGO<\/pre>\n<p>If you look at the Message tab (where the SQL query is printed) you\u2019ll see that the source table is Perf.vPerfRaw.<\/p>\n<pre lang=\"sql\">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\n                      FROM Perf.vPerfRaw As vPerf\n                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId\n                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId\n                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRuleInstance\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE RuleRowId in (SELECT RuleRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRule\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE ObjectName = 'Processor' AND CounterName = '% Processor Time'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  )\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t InstanceName = '_Total'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   )\n\t                        AND\n\t                        (vPerf.DateTime &gt;= '2012-04-01 0:00:00') \n\t                        AND \n\t                        (vPerf.DateTime &lt;= '2012-04-01 23:00:00')<\/pre>\n<p>If we change the date range to more than a day (5 days)&#8230;<\/p>\n<pre lang=\"sql\">EXEC\t[dbo].[spPerfDemo]\n\t\t@StartDateTime = N'2012-04-01 0:00:00',\n\t\t@EndDateTime = N'2012-04-05 0:00:00'\nGO<\/pre>\n<p>\u2026the data is retrieved from Perf.vPerfHourly .<\/p>\n<pre lang=\"SQL\">SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation, vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName\n                      FROM Perf.vPerfHourly As vPerf\n                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId\n                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId\n                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRuleInstance\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE RuleRowId in (SELECT RuleRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRule\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE ObjectName = 'Processor' AND CounterName = '% Processor Time'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  )\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t InstanceName = '_Total'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   )\n\t                        AND\n\t                        (vPerf.DateTime &gt;= '2012-04-01 0:00:00') \n\t                        AND \n\t                        (vPerf.DateTime &lt;= '2012-04-05 0:00:00')<\/pre>\n<h4>Dataset<\/h4>\n<p>&#160;<\/p>\n<p>Now add a dataset to the \u2018Report Project\u2019 report in the Business Intelligence Development Studio (BIDS) and select the stored procedure you\u2019ve created earlier. Don\u2019t forget to add the parameters for the begin and end date.<\/p>\n<p><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/datasetPerformanceData-Query.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/datasetPerformanceData-Query_thumb.png\" width=\"204\" height=\"211\" \/><\/a><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/datasetPerformanceData-Parameters.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/datasetPerformanceData-Parameters_thumb.png\" width=\"204\" height=\"211\" \/><\/a> <\/p>\n<p>  <br clear=\"all\" \/>And create the report as you normally would.<\/p>\n<p>&#160;<\/p>\n<h4>Authoring the management pack<\/h4>\n<p>The next step is to add the report and the stored procedure to the management. This is done by creating a Data Warehouse Script.<\/p>\n<p><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-1.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-1_thumb.png\" width=\"79\" height=\"79\" \/><\/a><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-2.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-2_thumb.png\" width=\"79\" height=\"79\" \/><\/a><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-3.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-3_thumb.png\" width=\"79\" height=\"79\" \/><\/a><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-4.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-4_thumb.png\" width=\"79\" height=\"79\" \/><\/a><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-5.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Data-Warehouse-Script-5_thumb.png\" width=\"79\" height=\"79\" \/><\/a><\/p>\n<h6>Install script<\/h6>\n<pre lang=\"sql\">IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spPerfDemo') \nBEGIN \nEXECUTE ('CREATE PROCEDURE dbo.[spPerfDemo] AS RETURN 1') \nEND \nGO\n\nALTER PROCEDURE [dbo].[spPerfDemo] \n    @StartDateTime VARCHAR(255), \n    @EndDateTime VARCHAR(255)\nAS \nBEGIN\n\n\t-- SET NOCOUNT ON added to prevent extra result sets from\n\t-- interfering with SELECT statements.\n\tSET NOCOUNT ON;\n\n\n    -- Declare variables\n    DECLARE @tableSource VARCHAR(25)\n    DECLARE @fieldsSource VARCHAR(100)\n    DECLARE @SQLCommand  VARCHAR(MAX)\n    \n\t\n\t--- Determine source table based on time range\n   IF DATEDIFF(day, @StartDateTime, @EndDateTime) &lt;= 1 \n      BEGIN \n         SET @tableSource = 'Perf.vPerfRaw'  \n         SET @fieldsSource = 'vPerf.SampleValue As AverageValue, vPerf.SampleValue As MinValue, vPerf.SampleValue As MaxValue, 0 as StandardDeviation'\n       END\n   ELSE IF DATEDIFF(day, @StartDateTime, @EndDateTime) &lt; 7 \n      BEGIN \n         SET @tableSource = 'Perf.vPerfHourly'  \n         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'\n      END\n   ELSE\n      BEGIN \n         SET @tableSource = 'Perf.vPerfDaily' \n         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'\n      END\n    \n \n   --- Create SQL command\n   SET @SQLCommand = 'SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, '+@fieldsSource+', vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName\n                      FROM '+@tableSource+' As vPerf\n                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId\n                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId\n                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRuleInstance\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE RuleRowId in (SELECT RuleRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRule\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE ObjectName = ''Processor'' AND CounterName = ''% Processor Time''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  )\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t InstanceName = ''_Total''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   )\n\t                        AND\n\t                        (vPerf.DateTime &gt;= '''+@StartDateTime+''') \n\t                        AND \n\t                        (vPerf.DateTime &lt;= '''+@EndDateTime+''')'\n   \n   --- Execute SQL command\n   PRINT @SQLCommand\n   EXEC (@sqlCommand)    \nEND\nGO\n\nGRANT EXEC on [dbo].[spPerfDemo] TO OpsMgrReader\nGO<\/pre>\n<h6>Uninstall script<\/h6>\n<pre lang=\"sql\">IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPerfDemo]') AND type in (N'P', N'PC'))\nDROP PROCEDURE [dbo].spPerfDemo]\nGO<\/pre>\n<h6>Upgrade script<\/h6>\n<pre lang=\"sql\">ALTER PROCEDURE [dbo].[spPerfDemo] \n    @StartDateTime VARCHAR(255), \n    @EndDateTime VARCHAR(255)\nAS \nBEGIN\n\n\t-- SET NOCOUNT ON added to prevent extra result sets from\n\t-- interfering with SELECT statements.\n\tSET NOCOUNT ON;\n\n\n    -- Declare variables\n    DECLARE @tableSource VARCHAR(25)\n    DECLARE @fieldsSource VARCHAR(100)\n    DECLARE @SQLCommand  VARCHAR(MAX)\n    \n\t\n\t--- Determine source table based on time range\n   IF DATEDIFF(day, @StartDateTime, @EndDateTime) &lt;= 1 \n      BEGIN \n         SET @tableSource = 'Perf.vPerfRaw'  \n         SET @fieldsSource = 'vPerf.SampleValue As AverageValue, vPerf.SampleValue As MinValue, vPerf.SampleValue As MaxValue, 0 as StandardDeviation'\n       END\n   ELSE IF DATEDIFF(day, @StartDateTime, @EndDateTime) &lt; 7 \n      BEGIN \n         SET @tableSource = 'Perf.vPerfHourly'  \n         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'\n      END\n   ELSE\n      BEGIN \n         SET @tableSource = 'Perf.vPerfDaily' \n         SET @fieldsSource = 'vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.StandardDeviation'\n      END\n    \n \n   --- Create SQL command\n   SET @SQLCommand = 'SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, '+@fieldsSource+', vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName\n                      FROM '+@tableSource+' As vPerf\n                      INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId\n                      INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId\n                      WHERE vPerf.PerformanceRuleInstanceRowId IN (SELECT PerformanceRuleInstanceRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRuleInstance\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE RuleRowId in (SELECT RuleRowId\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   FROM vPerformanceRule\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   WHERE ObjectName = ''Processor'' AND CounterName = ''% Processor Time''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  )\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t InstanceName = ''_Total''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   )\n\t                        AND\n\t                        (vPerf.DateTime &gt;= '''+@StartDateTime+''') \n\t                        AND \n\t                        (vPerf.DateTime &lt;= '''+@EndDateTime+''')'\n   \n   --- Execute SQL command\n   PRINT @SQLCommand\n   EXEC (@sqlCommand)    \nEND\nGO\n\nGRANT EXEC on [dbo].[spPerfDemo] TO OpsMgrReader\nGO<\/pre>\n<p>\n  <br clear=\"all\" \/>Finally you need to reference the data warehouse script from the report that\u2019s using the stored procedure.<\/p>\n<p><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Report.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Report_thumb.png\" width=\"204\" height=\"204\" \/><\/a><\/p>\n<h4>&#160;<\/h4>\n<h4>Pitfalls<\/h4>\n<p>Here are some pitfalls that you might encounter when creating your own.<\/p>\n<h6>&#160;<\/h6>\n<h6>Upgrade script not set<\/h6>\n<p>If you don\u2019t set the upgrade script (the fourth tab) the management pack won\u2019t import and an Event is logged from source OpsMgr SDK Service with event ID 26319.<\/p>\n<pre>An exception was thrown while processing ExportManagementPack for session id uuid:0cd28904-1168-4c6a-b077-e2820605d1ee;id=591.\n Exception Message: The creator of this fault did not specify a Reason.\n 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\nXSD verification failed for management pack. [Line: 759, Position: 15]The element 'DataWarehouseScript' has incomplete content. List of possible elements expected: 'Upgrade, UpgradeUnsupported'.).<\/pre>\n<p><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Event-26319.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/Event-26319_thumb.png\" width=\"204\" height=\"143\" \/><\/a> <\/p>\n<p>  <br clear=\"all\" \/><\/p>\n<h6>&#160;<\/h6>\n<h6>Permissions not granted<\/h6>\n<p>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.<\/p>\n<pre>An error occurred during report processing.\nQuery execution failed for dataset 'datasetPerformanceData'\nThe EXECUTE permission was denied on the object 'spPerfDemo', database 'OperationsManagerDW', schema 'dbo'.<\/pre>\n<p><a href=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/The-EXECUTE-permission-was-denied.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; margin: 0px 5px 0px 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"\" border=\"0\" alt=\"\" src=\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2012\/04\/The-EXECUTE-permission-was-denied_thumb.png\" width=\"504\" height=\"39\" \/><\/a><\/p>\n<p>&#160;<\/p>\n<h6>No data in chart shown with raw data<\/h6>\n<p>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:<\/p>\n<ul>\n<li>The data from Perf.vPerfRaw <\/li>\n<li>Displayed in a (line) chart <\/li>\n<li>Category Groups is [DateTime] <\/li>\n<li>There is a series group with more than one instance. <\/li>\n<\/ul>\n<p>If you export the report to a comma separated file (CSV) you\u2019ll notice that for each instance multiple records are stored but only one has a value, the number of records match the number of instances.<\/p>\n<p>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\u2019t match exactly (probably off by a few milliseconds). <\/p>\n<p>The is \u201csolved\u201d by removing the milliseconds from the datetime with the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms187928.aspx\" target=\"_blank\">CONVERT<\/a> statement and using style 120 (ODBC canonical).<\/p>\n<pre lang=\"sql\">SELECT CONVERT(VARCHAR, vPerf.DateTime, 120) As DateTime, <\/pre>\n","protected":false},"excerpt":{"rendered":"<p>When creating a report in System Center Operations Manager (SCOM) showing performance data you\u2019ll need to make a decision about the data you\u2019re 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 [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[311],"tags":[431,440,286,235,441],"class_list":["post-3593","post","type-post","status-publish","format-standard","hentry","category-system-center-operations-manager-scom","tag-opsmgr","tag-query","tag-report","tag-scom","tag-vperf"],"_links":{"self":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/3593","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/comments?post=3593"}],"version-history":[{"count":2,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/3593\/revisions"}],"predecessor-version":[{"id":3595,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/posts\/3593\/revisions\/3595"}],"wp:attachment":[{"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/media?parent=3593"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/categories?post=3593"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ingmarverheij.com\/en\/wp-json\/wp\/v2\/tags?post=3593"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}