<DataWarehouseScript ID="Microsoft.Windows.Server.Reports.PerformanceBySystem.Script" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Report_Performace_By_System')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_Report_Performace_By_System] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_Report_Performace_By_System]
@StartDate_BaseValue DATETIME,
@EndDate_BaseValue DATETIME,
@AggregationType INT,
@ObjectList XML,
@PerfObjectCounterListXml XML,
@MPListXml XML
AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecError INT
DECLARE @Error INT
DECLARE @xmlDoc INT
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'
)
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
;WITH PerfTable AS
(
SELECT
CASE
WHEN @AggregationType = 1 THEN CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101))
WHEN @AggregationType = 2 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DW,PerfData.[DateTime]),PerfData.[DateTime]), 101))
WHEN @AggregationType = 3 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAY,PerfData.[DateTime]),PerfData.[DateTime]), 101))
WHEN @AggregationType = 4 THEN DATEADD(MONTH, (DATEPART(Q, PerfData.[DateTime])- 1)*3, CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,PerfData.[DateTime]),PerfData.[DateTime]), 101)))
WHEN @AggregationType = 5 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,PerfData.[DateTime]),PerfData.[DateTime]), 101))
END [Date],
PerfData.AverageValue AverageValue,
PerfData.MinValue MinValue,
PerfData.MaxValue MaxValue,
PerfData.SampleCount SampleCount,
PerfData.PerformanceRuleInstanceRowId,
PerfData.ManagedEntityRowId
FROM
[Perf].vPerfDaily PerfData
WHERE
CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101))
BETWEEN
CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate_BaseValue, 101))
AND
CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate_BaseValue, 101))
),
CalcPerfTable AS
(
SELECT
PerfTable.[Date] [Date],
MgEntity.[Path] ComputerName,
SUM(PerfTable.SampleCount * PerfTable.AverageValue) AverageDivident,
SUM(SUM(PerfTable.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName, PerfTable.[Date]) AverageDiviser,
SUM(SUM(PerfTable.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId,PerfRule.ObjectName,PerfRule.CounterName, PerfTable.[Date]) SampleCount,
MAX(MAX(PerfTable.MaxValue)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName) TotalMaxValue,
MIN(MIN(PerfTable.MinValue)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName) TotalMinValue,
SUM(SUM(PerfTable.SampleCount * PerfTable.AverageValue )) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName) TotalAverageDivident,
SUM(SUM(PerfTable.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName) TotalAverageDiviser,
PerfRule.ObjectName ObjectName,
PerfRule.CounterName CounterName,
PerfRuleInst.InstanceName InstanceName
FROM
PerfTable
INNER JOIN
[dbo].vManagedEntity MgEntity ON PerfTable.ManagedEntityRowId = MgEntity.ManagedEntityRowId
INNER JOIN
[dbo].vPerformanceRuleInstance PerfRuleInst ON PerfRuleInst.PerformanceRuleInstanceRowId = PerfTable.PerformanceRuleInstanceRowId
INNER JOIN
[dbo].vPerformanceRule PerfRule ON PerfRuleInst.RuleRowId = PerfRule.RuleRowId
INNER JOIN
#ManagedEntity ON MgEntity.ManagedEntityRowId = #ManagedEntity.ManagedEntityRowId
INNER JOIN
#PerfObjectCounters ON PerfRule.ObjectName = PerfObject AND PerfRule.CounterName = PerfCounter
INNER JOIN
[dbo].vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId =
MgEntityType.ManagedEntityTypeRowId
INNER JOIN
#ManagementPacks ON MgEntityType.ManagementPackRowId = #ManagementPacks.ManagementPackId
WHERE
(MgEntity.[Path] IS NOT NULL)
AND NOT(#PerfObjectCounters.PerInstance = 1 AND PerfRuleInst.InstanceName = '_Total')
GROUP BY
MgEntity.ManagedEntityRowId,
MgEntity.[Path],
PerfRule.ObjectName,
PerfRule.CounterName,
PerfRuleInst.InstanceName,
PerfTable.[Date]
),
ResultTable AS
(
SELECT
CalcPerfTable.[Date],
CalcPerfTable.ComputerName,
CASE
WHEN SUM(CalcPerfTable.AverageDiviser) = 0 THEN 0
ELSE SUM(CalcPerfTable.AverageDivident) / SUM(CalcPerfTable.AverageDiviser) END AverageValue,
SUM(CalcPerfTable.SampleCount) SampleCount,
MIN(MIN(CalcPerfTable.TotalMinValue)) OVER(PARTITION BY CalcPerfTable.ComputerName, CalcPerfTable.ObjectName, CalcPerfTable.CounterName, CalcPerfTable.InstanceName) TotalMinValue,
MAX(MAX(CalcPerfTable.TotalMaxValue)) OVER(PARTITION BY CalcPerfTable.ComputerName, CalcPerfTable.ObjectName, CalcPerfTable.CounterName, CalcPerfTable.InstanceName) TotalMaxValue,
CASE
WHEN SUM(SUM(CalcPerfTable.TotalAverageDiviser)) OVER(PARTITION BY CalcPerfTable.ComputerName, CalcPerfTable.ObjectName, CalcPerfTable.CounterName, CalcPerfTable.InstanceName) = 0 THEN 0
ELSE SUM(SUM(CalcPerfTable.TotalAverageDivident)) OVER(PARTITION BY CalcPerfTable.ComputerName, CalcPerfTable.ObjectName, CalcPerfTable.CounterName, CalcPerfTable.InstanceName) / SUM(SUM(CalcPerfTable.TotalAverageDiviser)) OVER(PARTITION BY CalcPerfTable.ComputerName, CalcPerfTable.ObjectName, CalcPerfTable.CounterName, CalcPerfTable.InstanceName) END TotalAverageValue,
CalcPerfTable.ObjectName,
CalcPerfTable.CounterName,
CalcPerfTable.InstanceName
FROM
CalcPerfTable
GROUP BY
CalcPerfTable.[Date],
CalcPerfTable.ComputerName,
CalcPerfTable.ObjectName,
CalcPerfTable.CounterName,
CalcPerfTable.InstanceName
),
TimeTable AS
(
SELECT @StartDate_BaseValue fDate
UNION ALL
SELECT
fDate +1
FROM
TimeTable
WHERE
fDate + 1 <= @EndDate_BaseValue
),
AggregatedTimeTable AS
(
SELECT
r.fDate
FROM
(SELECT
CASE
WHEN @AggregationType = 1 THEN CONVERT(DATETIME, CONVERT(VARCHAR, fDate, 101))
WHEN @AggregationType = 2 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DW,fDate),fDate), 101))
WHEN @AggregationType = 3 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAY,fDate),fDate), 101))
WHEN @AggregationType = 4 THEN DATEADD(MONTH, (DATEPART(Q, fDate)- 1)*3, CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,fDate),fDate), 101)))
WHEN @AggregationType = 5 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,fDate),fDate), 101))
END fDate
FROM
TimeTable) r
GROUP BY
fDate
),
Computers AS
(
SELECT
DISTINCT ComputerName,
#PerfObjectCounters.PerfObject,
#PerfObjectCounters.PerfCounter
FROM
ResultTable
CROSS JOIN
#PerfObjectCounters
),
FullTimeTable AS
(
SELECT
fDate,
Computers.PerfCounter,
Computers.PerfObject,
Computers.ComputerName,
ResultTable.InstanceName
FROM
AggregatedTimeTable
CROSS JOIN
Computers
INNER JOIN
ResultTable ON ResultTable.ComputerName = Computers.ComputerName
AND ResultTable.ObjectName = Computers.PerfObject
AND ResultTable.CounterName = Computers.PerfCounter
GROUP BY
fDate,
Computers.PerfCounter,
Computers.PerfObject,
Computers.ComputerName,
ResultTable.InstanceName
)
SELECT
FullTimeTable.fDate [Date],
FullTimeTable.ComputerName ComputerName,
COALESCE(ResultTable.AverageValue, 0) AverageValue,
COALESCE(ResultTable.SampleCount, 0) SampleCount,
COALESCE(ResultTable.TotalMinValue, 0) TotalminValue,
COALESCE(ResultTable.TotalMaxValue, 0)TotalMaxValue,
COALESCE(ResultTable.TotalAverageValue, 0) TotalAverageValue,
FullTimeTable.PerfObject ObjectName,
FullTimeTable.PerfCounter CounterName,
FullTimeTable.InstanceName InstanceName
FROM
FullTimeTable
LEFT JOIN
ResultTable ON ResultTable.[Date] = FullTimeTable.fDate
AND ResultTable.ComputerName = FullTimeTable.ComputerName
AND ResultTable.ObjectName = FullTimeTable.PerfObject
AND ResultTable.CounterName = FullTimeTable.PerfCounter
AND ResultTable.InstanceName = FullTimeTable.InstanceName
ORDER BY
FullTimeTable.ComputerName,
FullTimeTable.PerfObject,
FullTimeTable.PerfCounter,
FullTimeTable.fDate
OPTION (MAXRECURSION 0)
QuitError:
DROP TABLE #PerfObjectCounters
DROP TABLE #ManagedEntity
DROP TABLE #ManagementPacks
IF (@xmlDoc IS NOT NULL)
EXEC sp_xml_removedocument @xmlDoc
SET @xmlDoc = NULL
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_Report_Performace_By_System] TO OpsMgrReader
GO </Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Report_Performace_By_System')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_Report_Performace_By_System]
END
GO
</Uninstall> <Upgrade>
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_Report_Performace_By_System]
@StartDate_BaseValue DATETIME,
@EndDate_BaseValue DATETIME,
@AggregationType INT,
@ObjectList XML,
@PerfObjectCounterListXml XML,
@MPListXml XML
AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecError INT
DECLARE @Error INT
DECLARE @xmlDoc INT
Home
ENU