CREATE VIEW RMONAlertView AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
    ,etherStatsOctets=CASE WHEN a.fieldIndex=302 THEN 100*SUM(alertDuration*severity/5.0)/(24*3600) ELSE 0 END
    ,etherStatsBroadcastPkts=CASE a.fieldIndex WHEN 304 THEN 100*SUM(alertDuration*severity/5.0)/(24*3600) ELSE 0 END
    ,etherStatsMulticastPkts=CASE WHEN a.fieldIndex =305 THEN 100*SUM(alertDuration*severity/5.0)/(24*3600) ELSE 0 END
    ,etherStatsCRCAlignErrors=CASE WHEN a.fieldIndex=306 THEN 100*SUM(alertDuration*severity/5.0)/(24*3600) ELSE 0 END
    ,etherStatsCollisions=case WHEN a.fieldIndex =311 THEN 100*SUM(alertDuration*severity/5.0)/(24*3600) ELSE 0 END
    ,etherStatsDropEvents=CASE WHEN a.fieldIndex = 301 THEN 100*SUM(alertDuration*severity/5.0)/(24*3600) ELSE 0 END
    ,etherStatsFragments=CASE WHEN a.fieldIndex = 309 THEN 100*SUM(alertDuration*severity/5.0)/(24*3600) ELSE 0 END
    ,unavail=CASE WHEN a.fieldIndex =1602 THEN 100*SUM(a.alertDuration*1.0)/(24*3600) ELSE 0 END
FROM ResourceNameRMONView r, AlertView a
        WHERE r.server_addr=a.server_addr
        AND r.probe_addr=a.probe_addr
        AND r.rsrcOtherIndex=a.rsrcIndex
    AND a.severity > 0
    AND a.severity <=5
    AND r.monitored=1
    AND a.IntervalDateTime BETWEEN dateadd(second, -datepart(second, getdate()),
        dateadd(minute, -datepart(minute, getdate()),
        dateadd(hour, -datepart(hour, getdate()),
        dateadd(day, -1, getdate()))))
    AND dateadd(second, -datepart(second, getdate()),
        dateadd(minute, -datepart(minute, getdate()),
        dateadd(hour, -datepart(hour, getdate()),
        getdate())))
    AND monitored = 1
    AND a.fieldIndex/100=3
GROUP BY r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
    ,a.fieldIndex
GO
GRANT SELECT ON  [dbo].[RMONAlertView] TO [uniadmin]
GO