CREATE VIEW CiscoScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcName
    ,r.userId
    ,avgBusy1=100-case WHEN SUM(avgBusy1) > 100 THEN 100 ELSE SUM(avgBusy1) END
    ,freeMem=100-case WHEN SUM(freeMem) > 100 THEN 100 ELSE SUM(freeMem) END
    ,bufferElFree=100-case WHEN SUM(bufferElFree) > 100 THEN 100 ELSE SUM(bufferElFree) END
    ,bufferElMiss=100-case WHEN SUM(bufferElMiss) > 100 THEN 100 ELSE SUM(bufferElMiss) END
    ,bufferFail=100-case WHEN SUM(bufferFail) > 100 THEN 100 ELSE SUM(bufferFail) END
    ,bufferNoMem=100-case WHEN SUM(bufferNoMem) > 100 THEN 100 ELSE SUM(bufferNoMem) END
    ,availability=100-case WHEN SUM(unavail) > 100 THEN 100 ELSE SUM(unavail) END
FROM CiscoAlertView r
GROUP BY
    r.server_addr
    ,r.probe_addr
    ,r.rsrcName
    ,r.userId
GO
GRANT SELECT ON  [dbo].[CiscoScoreBoard] TO [uniadmin]
GO