Thoughts on Azure, OMS & SCOM: Step by step guide for the error ‘…too many arguments specified…’ for the report ‘Performance By Utilization’

Important to know:
This posting is based on this article Kevin Holman posted. Based on the information provided in that article I wrote this step by step guide.

Issue
When one has imported the latest version of the Server OS MP (version 6.0.6958.0) with the additional Reports MP (Microsoft.Windows.Server.Reports.mp) one can bump into a nagging error when running the Report Performance By Utilization.

I ran the Report and all i got was: ‘An error has occurred during report processing. Query execution failed for dataset 'PerfDS'.’:

How to solve it
And this puzzled me since the related SQL Server is based on SQL Server 2008 R2 SP1. So no issues there. So it was time for some Bing actions (no more Google for me since Google has decided to wreck the search results with Google+ information…). And soon I found this posting written by Kevin Holman.

Item 4 in the Known Issues section of his posting told all I needed to know:

But first I needed to enable Remote Errors on the related SSRS instance in order to make sure I experience the issue described in the posting. How? Easy and also to be found here: http://technet.microsoft.com/en-us/library/aa337165.aspx.

Start a RDP session with the SSRS server;
Log on and start SQL Server Management Studio and logon to the SSRS instance (NOT the DB engine!);
Right click on the SSRS instance, select Properties and go to Advanced and set EnableRemoteErrors item to True. No restart needed. Just click OK and close SQL Server Management Studio.
When you run the problematic report again, you’ll get more information about the CAUSE of the error and BINGO! Exact the same error as described: Procedure or function Microsoft_SystemCenter_Report_Performance_By_Utilization has too many arguments specified.

OK, so now I know this issue matches exactly the one Kevin has described. Now it’s time for some action in order to restore the functionality of this Report. In this case I have decided to run the procedure high lighted in purple since that one is straight forward and easy to do:

Let’s start:

Download the MP for the server OS and unzip it by either installing it on your workstation or running the QWERTY.msi tool;
Make sure the MP Authoring Console is installed on the same workstation, start this tool and open the file Microsoft.Windows.Server.Reports.mp;
Go to Reporting > Data Warehouse Scripts;
Select the Data Warehouse script Microsoft.Windows.Server.Reports.PerformancebyUtilization.Script and select in the left pane the option Properties;
Go to the Install tab and select the WHOLE SQL script and copy it:
Backup the Data Warehouse database before you run this SQL script!!!
Right click the Data Warehouse database (OperationsManagerDW) and select the option New Query;
Paste the query which you copied in Step 5:
Run the query. It will run fast:
Close SQL Server Management Studio. Go to the SCOM R2 Console and run the Report again. BINGO!

So within a few minutes a nagging problem was solved! Nice one!

Fast Track
Here is the required SQL script which you must run against the Data Warehouse database when you have above mentioned issues. If not, contact CSS!

Now you can start from Step 6 :).

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

ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_Report_Performace_By_Utilization]
    @DataAggregation INT,
    @StartDate DATETIME,
    @EndDate DATETIME,
    @GroupManagedEntityID INT,
    @Top INT,
    @OrderType BIT,
    @ManagementGroupsCsv NVARCHAR(MAX),
    @PerfObjectCounterListXml XML,
    @MPListXml XML
AS
BEGIN

SET NOCOUNT ON

    DECLARE @ExecError INT
    DECLARE @Error INT
    DECLARE @xmlDoc INT
    -- build a list of all relationship
-- types derived from "System.Containment"
-- get row id of the "containment" relationship type
DECLARE @ContainmentRelationshipTypeRowId int

SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId
FROM vRelationshipType
WHERE (RelationshipTypeSystemName = 'System.Containment')

-- create table to hold all relationship types found
IF (OBJECT_ID('tempdb..#RelationshipType') IS NOT NULL)
DROP TABLE #RelationshipType

CREATE TABLE #RelationshipType (
RelationshipTypeRowId int
)

-- use table-valued function to build relationship list
INSERT #RelationshipType (RelationshipTypeRowId)
SELECT RelationshipTypeRowId
FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)

-- *********************************************
-- * STEP I: Get member computers
--
--     In this step we're going to figure out
--     which objects are currently members
--     of the group specified for report
-- *********************************************

-- create table to hold all contained objects
IF (OBJECT_ID('tempdb..#ContainedManagedEntity') IS NOT NULL)
DROP TABLE #ContainedManagedEntity
CREATE TABLE #ContainedManagedEntity (
ManagedEntityRowId int
,[Level] int
)

-- prepare recursion: put group into contained objects
INSERT #ContainedManagedEntity(ManagedEntityRowId, [Level])
VALUES (@GroupManagedEntityID, 0)
DECLARE @CurrentLevel int
SET @CurrentLevel = 1

DECLARE @RowCount int
SET @RowCount = 1

-- recursively walk down containment hierarchy
WHILE (@RowCount > 0)
BEGIN
INSERT #ContainedManagedEntity(ManagedEntityRowId, [Level])
SELECT r.TargetManagedEntityRowId, @CurrentLevel
FROM vRelationship r
          JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
          JOIN #ContainedManagedEntity c ON (r.SourceManagedEntityRowId = c.ManagedEntityRowId) AND (c.[Level] = @CurrentLevel - 1)
WHERE EXISTS (SELECT *
                FROM vRelationshipManagementGroup rmg
                WHERE (rmg.RelationshipRowId = r.RelationshipRowId)
                  AND (GETUTCDATE() BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, '99991231'))
               ) -- membership relationship exists as of NOW
SELECT @RowCount = @@ROWCOUNT
SET @CurrentLevel = @CurrentLevel + 1
END

    CREATE TABLE #PerfObjectCounters (
        PerfObject VARCHAR(MAX),
        PerfCounter VARCHAR(MAX),
        PerInstance BIT
        )

SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError

    CREATE TABLE #ManagementGroups(ManagementGroupGuid UNIQUEIDENTIFIER)
    SET @Error = @@ERROR
    IF @Error <> 0 GOTO QuitError

    EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @PerfObjectCounterListXml
    SET @Error = @@ERROR
    IF @ExecError <> 0 OR @Error <> 0 GOTO QuitError

    INSERT INTO #PerfObjectCounters (PerfObject, PerfCounter, PerInstance)
    SELECT PerfObject, PerfCounter, PerInstance
    FROM OPENXML(@xmlDoc,'/Data/Objects/Object',2) WITH
        (PerfObject VARCHAR(MAX) '@Name',
         PerfCounter VARCHAR(MAX) '@Counter',
         PerInstance BIT '@PerInstance'
         )

    DECLARE @Pos        INT,
            @NextPos    INT,
            @ValueLen   INT,
            @MGList        NVARCHAR(MAX)

SET @Pos = 0
SET @NextPos = 1

    WHILE @NextPos > 0
        BEGIN
            SET @NextPos = CHARINDEX(',', @ManagementGroupsCsv, @Pos + 1)
            SET @ValueLen = (CASE WHEN @NextPos > 0
                                  THEN @NextPos
                                  ELSE LEN(@ManagementGroupsCsv) + 1
                             END) - @Pos - 1
            INSERT #ManagementGroups (ManagementGroupGuid)
             VALUES (CONVERT(UNIQUEIDENTIFIER, SUBSTRING(REPLACE(@ManagementGroupsCsv,' ',''), @Pos + 1, @ValueLen)))
            SET @Pos = @NextPos
        END

    IF @ExecError <> 0 GOTO QuitError
    CREATE TABLE #ManagementPacks (
    ManagementPackSysName VARCHAR(MAX),
    ManagementPackId INT
    )
    EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @MPListXml
        SET @Error = @@ERROR
        IF @ExecError <> 0 OR @Error <> 0 GOTO QuitError

    INSERT INTO #ManagementPacks(ManagementPackSysName, ManagementPackId)
        SELECT ManagementPackSysName, MP.ManagementPackRowId
        FROM OPENXML(@xmlDoc,'/ManagementPacks/ManagementPack',2) WITH
            (ManagementPackSysName VARCHAR(MAX) '@Name')
        INNER JOIN
            ManagementPack MP ON MP.ManagementPackSystemName = ManagementPackSysName

IF @DataAggregation = 1

    WITH ComputerId AS
    (
        SELECT
            MgEntity.ManagedEntityRowId,
            MgEntity.Name
        FROM
            vManagedEntity MgEntity
        INNER JOIN
            vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = MgEntityType.ManagedEntityTypeRowId
            JOIN #ContainedManagedEntity cme ON MgEntity.ManagedEntityRowId = cme.ManagedEntityRowId
        WHERE
            MgEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'
    ),
    PerfTable AS
    (
    SELECT
        MgEntity.[Path] ComputerName,
        SUM(PerfData.SampleCount * PerfData.AverageValue)/(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) AverageValue,
        PerfRule.ObjectName ObjectName,
        PerfRule.CounterName CounterName,
        PerfRuleInst.InstanceName InstanceName,
        MAX(CAST(#PerfObjectCounters.PerInstance AS INT)) PerInstance,
        ComputerId.ManagedEntityRowId ManagedEntityRowId,
        MAX(PerfRule.RuleRowId) RuleId,
        (SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) RuleSampleCount
    FROM
        [Perf].vPerfDaily PerfData
        INNER JOIN
            [dbo].vManagedEntity MgEntity ON PerfData.ManagedEntityRowId =
            MgEntity.ManagedEntityRowId
        INNER JOIN
            [dbo].vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId =
            MgEntityType.ManagedEntityTypeRowId
        INNER JOIN
            [dbo].vPerformanceRuleInstance PerfRuleInst ON
            PerfRuleInst.PerformanceRuleInstanceRowId =
            PerfData.PerformanceRuleInstanceRowId
        INNER JOIN
            [dbo].vPerformanceRule PerfRule ON PerfRuleInst.RuleRowId =
            PerfRule.RuleRowId
        INNER JOIN
            [dbo].vManagementGroup MG ON MgEntity.ManagementGroupRowId = MG.ManagementGroupRowId
        INNER JOIN
            #ManagementGroups ON MG.ManagementGroupGuid = #ManagementGroups.ManagementGroupGuid
        INNER JOIN
            #PerfObjectCounters ON PerfRule.ObjectName = PerfObject AND PerfRule.CounterName = PerfCounter
        INNER JOIN
            #ManagementPacks ON MgEntityType.ManagementPackRowId = #ManagementPacks.ManagementPackId
        RIGHT JOIN
            ComputerId ON ComputerId.Name = MgEntity.Path
    WHERE
          (
            CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101))
            BETWEEN
                CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101))
            AND
                CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101))
          )
         AND
          (
            MgEntity.[Path] IS NOT NULL
          )
    GROUP BY
        MgEntity.ManagedEntityRowId, MgEntity.[Path], PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName, MgEntityType.ManagedEntityTypeSystemName, ComputerId.ManagedEntityRowId
    ),
    InstanceFilteredTable AS
    (
    SELECT
        ComputerName,
        SUM(AverageValue*RuleSampleCount)/ SUM(SUM(RuleSampleCount)) OVER(PARTITION BY ComputerName,ObjectName,CounterName,InstanceName,ManagedEntityRowId) AverageValue,
        ObjectName,
        CounterName,
        InstanceName,
        PerInstance,
        CASE WHEN (@OrderType = 1) THEN
            (CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName <> '% Free Space'))
                    OR (PerInstance = 0)
                    OR ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))
                THEN 1 ELSE 0 END)
        ELSE
            (CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName <> '% Free Space'))
                    OR (PerInstance = 0)
                    OR ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))
                THEN 1 ELSE 0 END)
        END MaxFlag,
        ManagedEntityRowId
    FROM
        PerfTable
    GROUP BY
        ComputerName,
        ObjectName,
        CounterName,
        InstanceName,
        PerInstance,
        ManagedEntityRowId
    ),
    InstanceRankedTable AS
    (
        SELECT
            ComputerName,
            AverageValue,
            ObjectName,
            CounterName,
            InstanceName,
            PerInstance,
            ManagedEntityRowId,
            ROW_NUMBER() OVER(PARTITION BY ComputerName,ObjectName,CounterName ORDER BY MaxFlag DESC) MaxFlag
        FROM
            InstanceFilteredTable
    ),
    OrderedPerfTable AS
    (
    SELECT
        CASE WHEN (@OrderType = 1)THEN
            CASE WHEN (CounterName <> '% Free Space')
            THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue DESC))
            ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC)) END
        ELSE
            CASE WHEN (CounterName <> '% Free Space')
            THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC))
            ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue DESC))END
        END OrderNum,
        ComputerName,
        AverageValue,
        ObjectName,
        CounterName,
        InstanceName,
        ManagedEntityRowId
    FROM
        InstanceRankedTable
    WHERE
        MaxFlag = 1
    )
    SELECT
        OrderNum,
        ComputerName,
        AverageValue,
        ObjectName,
        CounterName,
        InstanceName,
        ManagedEntityRowId
    FROM
        OrderedPerfTable
    WHERE
        OrderNum <= @Top

ELSE
    WITH ComputerId AS
    (
        SELECT
            MgEntity.ManagedEntityRowId,
            MgEntity.Name
        FROM
            vManagedEntity MgEntity
        INNER JOIN
            vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = MgEntityType.ManagedEntityTypeRowId
            JOIN #ContainedManagedEntity cme ON MgEntity.ManagedEntityRowId = cme.ManagedEntityRowId
        WHERE
            MgEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'
    ),
    PerfTable AS
    (
    SELECT
        MgEntity.[Path] ComputerName,
        SUM(PerfData.SampleCount * PerfData.AverageValue)/(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) AverageValue,
        PerfRule.ObjectName ObjectName,
        PerfRule.CounterName CounterName,
        PerfRuleInst.InstanceName InstanceName,
        MAX(CAST(#PerfObjectCounters.PerInstance AS INT)) PerInstance,
        ComputerId.ManagedEntityRowId ManagedEntityRowId,
        MAX(PerfRule.RuleRowId) RuleId,
        (SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) RuleSampleCount
    FROM
        [Perf].vPerfHourly PerfData
        INNER JOIN
            [dbo].vManagedEntity MgEntity ON PerfData.ManagedEntityRowId =
            MgEntity.ManagedEntityRowId
        INNER JOIN
            [dbo].vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId =
            MgEntityType.ManagedEntityTypeRowId
        INNER JOIN
            [dbo].vPerformanceRuleInstance PerfRuleInst ON
            PerfRuleInst.PerformanceRuleInstanceRowId =
            PerfData.PerformanceRuleInstanceRowId
        INNER JOIN
            [dbo].vPerformanceRule PerfRule ON PerfRuleInst.RuleRowId =
            PerfRule.RuleRowId
        INNER JOIN
            [dbo].vManagementGroup MG ON MgEntity.ManagementGroupRowId = MG.ManagementGroupRowId
        INNER JOIN
            #ManagementGroups ON MG.ManagementGroupGuid = #ManagementGroups.ManagementGroupGuid
        INNER JOIN
            #PerfObjectCounters ON PerfRule.ObjectName = PerfObject AND PerfRule.CounterName = PerfCounter
        INNER JOIN
            #ManagementPacks ON MgEntityType.ManagementPackRowId = #ManagementPacks.ManagementPackId
        RIGHT JOIN
            ComputerId ON ComputerId.Name = MgEntity.Path
    WHERE
          (
            CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101))
            BETWEEN
                CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101))
            AND
                CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101))
          )
         AND
          (
            MgEntity.[Path] IS NOT NULL
          )
    GROUP BY
        MgEntity.ManagedEntityRowId, MgEntity.[Path], PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName, MgEntityType.ManagedEntityTypeSystemName, ComputerId.ManagedEntityRowId
    ),
    InstanceFilteredTable AS
    (
    SELECT
        ComputerName,
        SUM(AverageValue*RuleSampleCount)/ SUM(SUM(RuleSampleCount)) OVER(PARTITION BY ComputerName,ObjectName,CounterName,InstanceName,ManagedEntityRowId) AverageValue,
        ObjectName,
        CounterName,
        InstanceName,
        PerInstance,
        CASE WHEN (@OrderType = 1) THEN
            (CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName <> '% Free Space'))
                    OR (PerInstance = 0)
                    OR ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))
                THEN 1 ELSE 0 END)
        ELSE
            (CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName <> '% Free Space'))
                    OR (PerInstance = 0)
                    OR ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))
                THEN 1 ELSE 0 END)
        END MaxFlag,
        ManagedEntityRowId
    FROM
        PerfTable
    GROUP BY
        ComputerName,
        ObjectName,
        CounterName,
        InstanceName,
        PerInstance,
        ManagedEntityRowId
    ),
    InstanceRankedTable AS
    (
        SELECT
            ComputerName,
            AverageValue,
            ObjectName,
            CounterName,
            InstanceName,
            PerInstance,
            ManagedEntityRowId,
            ROW_NUMBER() OVER(PARTITION BY ComputerName,ObjectName,CounterName ORDER BY MaxFlag DESC) MaxFlag
        FROM
            InstanceFilteredTable
    ),
    OrderedPerfTable AS
    (
    SELECT
        CASE WHEN (@OrderType = 1)THEN
            CASE WHEN (CounterName <> '% Free Space')
            THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue DESC))
            ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC)) END
        ELSE
            CASE WHEN (CounterName <> '% Free Space')
            THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC))
            ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue DESC))END
        END OrderNum,
        ComputerName,
        AverageValue,
        ObjectName,
        CounterName,
        InstanceName,
        ManagedEntityRowId
    FROM
        InstanceRankedTable
    WHERE
        MaxFlag = 1
    )
    SELECT
        OrderNum,
        ComputerName,
        AverageValue,
        ObjectName,
        CounterName,
        InstanceName,
        ManagedEntityRowId
    FROM
        OrderedPerfTable
    WHERE
        OrderNum <= @Top

    QuitError:
    DROP TABLE #PerfObjectCounters
    DROP TABLE #ManagementGroups
    DROP TABLE #ManagementPacks
    IF (@xmlDoc IS NOT NULL)
    EXEC sp_xml_removedocument @xmlDoc
    SET @xmlDoc = NULL

RETURN @Error

END

GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_Report_Performace_By_Utilization] TO OpsMgrReader
GO

Thoughts on Azure, OMS & SCOM

Tuesday, January 31, 2012

Step by step guide for the error ‘…too many arguments specified…’ for the report ‘Performance By Utilization’

No comments: