CREATE VIEW NPOProbeScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcName
    ,r.userId
    ,utilization_sum=100-case WHEN SUM(utilization_sum) > 100 THEN 100 ELSE SUM(utilization_sum) END
    ,broadcastFrames=100-case WHEN SUM(broadcastFrames) > 100 THEN 100 ELSE SUM(broadcastFrames) END
    ,multicastFrames=100-case WHEN SUM(multicastFrames) > 100 THEN 100 ELSE SUM(multicastFrames) END
    ,frameLostProbe=100-case WHEN SUM(frameLostProbe) > 100 THEN 100 ELSE SUM(frameLostProbe) END
    ,frameLostBuf=100-case WHEN SUM(frameLostbuf) > 100 THEN 100 ELSE SUM(frameLostbuf) END
    ,frameLostHard=100-case WHEN SUM(frameLosthard) > 100 THEN 100 ELSE SUM(frameLosthard) END
    ,availability=100-case WHEN SUM(unavail) > 100 THEN 100 ELSE SUM(unavail) END
FROM NPOProbeAlertView r
GROUP BY
    r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcName
    ,r.userId
GO
GRANT SELECT ON  [dbo].[NPOProbeScoreBoard] TO [uniadmin]
GO