<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,
@Timezone varchar(100),
@ObjectList XML,
@PerfObjectCounterListXml XML,
@MPListXml XML
AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecError INT
DECLARE @Error INT
DECLARE @xmlDoc INT
DECLARE @StartDate_DBBaseValue DATETIME
DECLARE @EndDate_DBBaseValue DATETIME
DECLARE @DBTimezone varchar(50)
--@Timezone used to convert date from UTC(DB) format to Report Datetime format.
--@DBTimezone used to convert date from Report Datetime format to UTC format.
--Ex1: if @Timezone is '(UTC-08:00) Pacific Time (US & Canada)' i.e. Non-UTC
--@Timezone is made to -08:00 and @DBTimezone is +08:00
--Ex2: if @Timezone is '(UTC) Pacific Time (US & Canada)' i.e. UTC
--@Timezone and @DBTimezone are made to +00:00
IF(CHARINDEX(UPPER('(UTC)') , @Timezone) = 0)
BEGIN
IF(SUBSTRING(@Timezone,5,1)='+')
SET @DBTimezone='-'+SUBSTRING(@Timezone,6,5)
ELSE
SET @DBTimezone='+'+SUBSTRING(@Timezone,6,5)
SET @Timezone=SUBSTRING(@Timezone,5,6)
END
ELSE
BEGIN
SET @Timezone='+00:00'
SET @DBTimezone='+00:00'
END
SET @StartDate_DBBaseValue=CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,@StartDate_BaseValue),@DBTimezone))
SET @EndDate_DBBaseValue=CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,@EndDate_BaseValue),@DBTimezone))
CREATE TABLE #ManagedEntity (ManagedEntityRowId 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
SELECT
CASE
WHEN @AggregationType = 1 THEN CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone)), 101),101)
WHEN @AggregationType = 2 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DW, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101)
WHEN @AggregationType = 3 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAY, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101)
WHEN @AggregationType = 4 THEN DATEADD(MONTH, (DATEPART(Q, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone)))- 1)*3, CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101))
WHEN @AggregationType = 5 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101)
END [Date],
PerfData.AverageValue AverageValue,
PerfData.MinValue MinValue,
PerfData.MaxValue MaxValue,
PerfData.SampleCount SampleCount,
PerfData.PerformanceRuleInstanceRowId,
PerfData.ManagedEntityRowId
INTO #PerfTable
FROM
[Perf].vPerfDaily PerfData
WHERE
CONVERT(DATETIME, CONVERT(VARCHAR,CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone)), 101),101)
BETWEEN
CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate_BaseValue, 101),101)
AND
CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate_BaseValue, 101),101)
CREATE CLUSTERED INDEX #IX_PerfTable ON #PerfTable(ManagedEntityRowId)
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
INTO #CalcPerfTable
FROM
#PerfTable 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]
CREATE CLUSTERED INDEX IX_CalcPerfTable ON #CalcPerfTable([Date])
;WITH 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 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
CONVERT(date, fDate+1) <=CONVERT(date, @EndDate_BaseValue)
),
AggregatedTimeTable AS
(
SELECT
r.fDate
FROM
(SELECT
CASE
WHEN @AggregationType = 1 THEN CONVERT(DATETIME, CONVERT(VARCHAR, fDate, 101),101)
WHEN @AggregationType = 2 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DW,fDate),fDate), 101),101)
WHEN @AggregationType = 3 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAY,fDate),fDate), 101),101)
WHEN @AggregationType = 4 THEN DATEADD(MONTH, (DATEPART(Q, fDate)- 1)*3, CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,fDate),fDate), 101),101))
WHEN @AggregationType = 5 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,fDate),fDate), 101),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
DROP TABLE #PerfTable
DROP TABLE #CalcPerfTable
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,
@Timezone varchar(100),
@ObjectList XML,
@PerfObjectCounterListXml XML,
@MPListXml XML
AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecError INT
DECLARE @Error INT
DECLARE @xmlDoc INT
DECLARE @StartDate_DBBaseValue DATETIME
DECLARE @EndDate_DBBaseValue DATETIME
DECLARE @DBTimezone varchar(50)
--@Timezone used to convert date from UTC(DB) format to Report Datetime format.
--@DBTimezone used to convert date from Report Datetime format to UTC format.
--Ex1: if @Timezone is '(UTC-08:00) Pacific Time (US & Canada)' i.e. Non-UTC
--@Timezone is made to -08:00 and @DBTimezone is +08:00
--Ex2: if @Timezone is '(UTC) Pacific Time (US & Canada)' i.e. UTC
--@Timezone and @DBTimezone are made to +00:00
IF(CHARINDEX(UPPER('(UTC)') , @Timezone) = 0)
BEGIN
IF(SUBSTRING(@Timezone,5,1)='+')
SET @DBTimezone='-'+SUBSTRING(@Timezone,6,5)
ELSE
SET @DBTimezone='+'+SUBSTRING(@Timezone,6,5)
SET @Timezone=SUBSTRING(@Timezone,5,6)
END
ELSE
BEGIN
SET @Timezone='+00:00'
SET @DBTimezone='+00:00'
END
SET @StartDate_DBBaseValue=CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,@StartDate_BaseValue),@DBTimezone))
SET @EndDate_DBBaseValue=CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,@EndDate_BaseValue),@DBTimezone))
CREATE TABLE #ManagedEntity (ManagedEntityRowId 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
SELECT
CASE
WHEN @AggregationType = 1 THEN CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone)), 101),101)
WHEN @AggregationType = 2 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DW, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101)
WHEN @AggregationType = 3 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAY, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101)
WHEN @AggregationType = 4 THEN DATEADD(MONTH, (DATEPART(Q, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone)))- 1)*3, CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101))
WHEN @AggregationType = 5 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR, CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone))), 101),101)
END [Date],
PerfData.AverageValue AverageValue,
PerfData.MinValue MinValue,
PerfData.MaxValue MaxValue,
PerfData.SampleCount SampleCount,
PerfData.PerformanceRuleInstanceRowId,
PerfData.ManagedEntityRowId
INTO #PerfTable
FROM
[Perf].vPerfDaily PerfData
WHERE
CONVERT(DATETIME, CONVERT(VARCHAR,CONVERT(DATETIME,SWITCHOFFSET(CONVERT(DATETIMEOFFSET,PerfData.[DateTime]),@Timezone)), 101),101)
BETWEEN
CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate_BaseValue, 101),101)
AND
CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate_BaseValue, 101),101)
CREATE CLUSTERED INDEX #IX_PerfTable ON #PerfTable(ManagedEntityRowId)
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
INTO #CalcPerfTable
FROM
#PerfTable 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]
CREATE CLUSTERED INDEX IX_CalcPerfTable ON #CalcPerfTable([Date])
;WITH 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 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
CONVERT(date, fDate+1) <=CONVERT(date, @EndDate_BaseValue)
),
AggregatedTimeTable AS
(
SELECT
r.fDate
FROM
(SELECT
CASE
WHEN @AggregationType = 1 THEN CONVERT(DATETIME, CONVERT(VARCHAR, fDate, 101),101)
WHEN @AggregationType = 2 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DW,fDate),fDate), 101),101)
WHEN @AggregationType = 3 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAY,fDate),fDate), 101),101)
WHEN @AggregationType = 4 THEN DATEADD(MONTH, (DATEPART(Q, fDate)- 1)*3, CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,fDate),fDate), 101),101))
WHEN @AggregationType = 5 THEN CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY,1 - DATEPART(DAYOFYEAR,fDate),fDate), 101),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
DROP TABLE #PerfTable
DROP TABLE #CalcPerfTable
IF (@xmlDoc IS NOT NULL)
EXEC sp_xml_removedocument @xmlDoc
SET @xmlDoc = NULL
Home
ENU