ziembor


DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @StartDateMAX datetime
-- comment bellow if You will use as parameter
DECLARE @FullName nvarchar(255)

DECLARE @CounterIDs TABLE(ID INT, ObjectName NVARCHAR(60), CounterName NVARCHAR(60))

SELECT      @StartDateMAX =  max(vPerf.DateTime)  FROM OperationsManagerDW.Perf.vPerfRaw AS vPerf
SET @StartDate = ISNULL(@StartDate, cast(cast(cast(DATEADD(DAY,-1,@StartDateMAX) AS int) AS float) AS datetime));
SET @EndDate = ISNULL(@EndDate, cast(cast(cast(DATEADD(DAY,0,GetDate()) AS int) AS float) AS datetime));

SET @FullName = ISNULL(@FullName,'Microsoft.SystemCenter.AllComputersGroup');

INSERT INTO @CounterIDs
SELECT RuleRowId, ObjectName, CounterName
--  FROM OperationsManagerDW.dbo.vPerformanceRule vPR  where objectName in ('LogicalDisk', 'Network Interface', 'System','Processor','Memory') and
FROM OperationsManagerDW.dbo.vPerformanceRule vPR  WHERE objectName IN ('LogicalDisk') AND
((counterName = '% Free Space' ) OR (counterName = 'Free Megabytes'))

DECLARE @ManagedEntityRowId INT
SELECT @ManagedEntityRowId = ManagedEntityRowId
FROM OperationsManagerDW.dbo.vManagedEntity
WHERE FullName = @FullName

DECLARE @MERIOBJ nvarchar(255)
SET @MERIOBJ='<Data><Objects><Object Use="Containment">' + CAST(@ManagedEntityRowId AS VARCHAR(5)) + '</Object></Objects></Data>'

DECLARE @ManagedEntity TABLE(ManagedEntityRowId int)

DECLARE @FROM DATETIME
DECLARE @TO DATETIME
SET @TO = GETDATE();
SET @FROM = DATEADD(MONTH, -1, @TO)

INSERT INTO @ManagedEntity
EXEC [OperationsManagerDW].[dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
@FROM,
@TO,
@MERIOBJ


DECLARE @CounterResults TABLE([DateTime] datetime,AverageValue int,Path nvarchar(255),InstanceName nvarchar(255),ObjectName nvarchar(255),CounterName nvarchar(255))

INSERT INTO @CounterResults
SELECT
max(vPerf.DateTime) AS DateTime,
max(vPerf.SampleValue) AS AverageValue,
vME.Path,
vPRI.InstanceName,
C.ObjectName,
C.CounterName

FROM OperationsManagerDW.Perf.vPerfRaw AS vPerf
INNER JOIN
OperationsManagerDW.dbo.vPerformanceRuleInstance vPRI ON vPRI.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN
OperationsManagerDW.dbo.vManagedEntity vME ON vPerf.ManagedEntityRowId = vME.ManagedEntityRowId
INNER JOIN
@CounterIDs C ON vPRI.RuleRowId = C.ID
WHERE
vPerf.DateTime > @StartDate
AND
vPerf.DateTime < @EndDate
AND
vME.Path IN (SELECT DISTINCT
vManagedEntity.Path
FROM OperationsManagerDW.Perf.vPerfRaw AS vPerf
INNER JOIN
OperationsManagerDW.dbo.vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN
OperationsManagerDW.dbo.vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN
@ManagedEntity ME ON vManagedEntity.ManagedEntityRowId = ME.ManagedEntityRowId
)
GROUP BY vME.Path,
vPRI.InstanceName,
C.ObjectName,
C.CounterName

ORDER BY DateTime

DECLARE @ProcFreeSpaceResults TABLE([DateTime] datetime,ProcFreeSpace int,Path nvarchar(255),InstanceName nvarchar(255),CounterName nvarchar(255))
INSERT INTO @ProcFreeSpaceResults
SELECT DateTime,AverageValue AS ProcFreeSpace,Path,InstanceName,CounterName FROM @CounterResults WHERE CounterName = '% Free Space'


DECLARE @FreeMegabytesResults TABLE([DateTime] datetime,FreeMegabytes int,Path nvarchar(255),InstanceName nvarchar(255),CounterName nvarchar(255))
INSERT INTO @FreeMegabytesResults
SELECT DateTime,AverageValue AS FreeMegabytes,Path,InstanceName,CounterName FROM @CounterResults WHERE CounterName = 'Free Megabytes'

SELECT m.Path AS Computer,m.InstanceName AS LogicalDisk,FreeMegabytes,ProcFreeSpace,m.DateTime FROM @ProcFreeSpaceResults F ,@FreeMegabytesResults m WHERE
m.Path=F.Path AND
m.InstanceName=f.InstanceName
ORDER BY computer,LogicalDisk