SCOM 2007 Report 관련 쿼리 (보고서)

' 성능 카운터 리스트 쿼리
SELECT ObjectName, CounterName, MultiInstanceInd FROM vPerformanceRule ORDER BY ObjectName

' Windows 컴퓨터 리스트 쿼리
SELECT Name FROM dbo.vManagedEntity WHERE ManagedEntityTypeRowId = '22' ORDER BY Name

' 성능 정보 쿼리 (데이터웨어하우스)
EXEC SP_PerfViewDaily '2009-02-01', '2009-02-20', 'Processor', '% Processor Time', 'Server1'

----------------------------------------------------------------------------------------------------------

CREATE PROCEDURE SP_PerfViewDaily
@StartDate DATETIME,
@EndDate DATETIME,
@ObjectName NCHAR(30),
@CounterName NCHAR(30),
@ServerName NCHAR(30)
AS
SELECT    
Dateadd (HH, 9, vPerf.DateTime) AS DateTime ,
vPerf.SampleCount,
Round (vPerf.AverageValue, 2) AS Average,
Round (vPerf.MinValue, 2) AS Minimum,
Round (vPerf.MaxValue, 2) AS Maximum,
Round (vPerf.StandardDeviation, 2) AS StandardDeviation,
vPerformanceRuleInstance.InstanceName,
vManagedEntity.Path AS ServerName,
vPerformanceRule.ObjectName,
vPerformanceRule.CounterName
FROM Perf.vPerfdaily AS vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE vPerf.DateTime >= @StartDate
AND vPerf.DateTime < @EndDate
AND (vPerformanceRule.ObjectName IN (@ObjectName))
AND (vPerformanceRule.CounterName IN (@CounterName))
/* AND (vPerformanceRuleInstance.InstanceName IN ('')) */
AND (vManagedEntity.Path IN (@ServerName))
ORDER BY  DateTime
GO