use [OperationsManagerDW]
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--=================
-- Set time period
--=================
declare @DateFrom datetime = EOMONTH(GETDATE(), -2) 
declare @DateTo datetime = EOMONTH(GETDATE(), -1)
--=====================
-- Daily disk info
--=====================
; WITH CTE_DISK
AS
(
    SELECT
          vph.DateTime
        , vPerformanceRuleInstance.InstanceName
        , vManagedEntity.Path
        , vPerformanceRule.ObjectName
        , vPerformanceRule.CounterName 
        , vph.MinValue
        , vph.MaxValue
        , vph.AverageValue
        , EventType = 
            CASE  
                WHEN ObjectName = 'LogicalDisk' AND  CounterName = '% Free Space' AND AverageValue < 5 then 'Critical' 
                WHEN ObjectName = 'LogicalDisk' AND  CounterName = '% Free Space' AND AverageValue < 10 then 'Warning' 
                ELSE NULL 
            END 
        , Event = 
            CASE 
                WHEN ObjectName = 'LogicalDisk' AND  CounterName = '% Free Space' AND AverageValue < 10 
                THEN '% Free Space < 10'  
                ELSE NULL 
            END
    FROM Perf.vPerfDaily AS vph 
    INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vph.PerformanceRuleInstanceRowId 
    INNER JOIN vManagedEntity ON vph.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 
    INNER JOIN vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
    WHERE 
        vPerformanceRule.ObjectName = 'LogicalDisk' AND
        vPerformanceRule.CounterName = '% Free Space' AND
        vph.DateTime > @DateFrom AND vph.DateTime <= @DateTo
)
--=====================
-- Hourly CPU info
--=====================
,CTE_CPU AS 
(
    SELECT
          vph.DateTime
        , vPerformanceRuleInstance.InstanceName
        , vManagedEntity.Path
        , vPerformanceRule.ObjectName
        , vPerformanceRule.CounterName 
        , vph.MinValue
        , vph.MaxValue
        , vph.AverageValue
        , EventType= 
            case  
                when ObjectName = 'Processor Information' and  CounterName = '% Processor Time' and AverageValue > 90 then 'Critical' 
                when ObjectName = 'Processor Information' and  CounterName = '% Processor Time' and AverageValue > 85 then 'Warning' 
                else null 
            end 
        , Event = 
            CASE 
                WHEN
                    ObjectName = 'Processor Information' 
                AND CounterName = '% Processor Time' 
                AND AverageValue > 90 THEN '% Processor Time > 90' 
                ELSE NULL
            END
    FROM  Perf.vPerfHourly AS vph 
    INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vph.PerformanceRuleInstanceRowId 
    INNER JOIN vManagedEntity ON vph.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId 
    INNER JOIN vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
    WHERE 
        vPerformanceRule.ObjectName = 'Processor Information' AND
        vPerformanceRule.CounterName = '% Processor Time' AND
        vph.DateTime > @DateFrom AND vph.DateTime <= @DateTo
)
SELECT * FROM CTE_DISK WHERE Event IS NOT NULL
UNION ALL
SELECT * FROM CTE_CPU WHERE Event IS NOT NULL
GO