CREATE VIEW BGP4ScoreBoard AS
SELECT r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
    ,bgpPeerInUpdates=100-case WHEN SUM(bgpPeerInUpdates) > 100 THEN 100 ELSE SUM(bgpPeerInUpdates) END
    ,bgpPeerOutUpdates=100-case WHEN SUM(bgpPeerOutUpdates) > 100 THEN 100 ELSE SUM(bgpPeerOutUpdates) END
    ,bgpPeerInTotalMessages=100-case WHEN SUM(bgpPeerInTotalMessages) > 100 THEN 100 ELSE SUM(bgpPeerInTotalMessages) END
    ,bgpPeerOutTotalMessages=100-case WHEN SUM(bgpPeerOutTotalMessages) > 100 THEN 100 ELSE SUM(bgpPeerOutTotalMessages) END
    ,bgpPeerFsmEstablishedTrans=100-case WHEN SUM(bgpPeerFsmEstablishedTrans) > 100 THEN 100 ELSE SUM(bgpPeerFsmEstablishedTrans) END
    ,bgpPeerInUpdateElapsedTime=100-case WHEN SUM(bgpPeerInUpdateElapsedTime) > 100 THEN 100 ELSE SUM(bgpPeerInUpdateElapsedTime) END
FROM BGP4AlertView r
GROUP BY
    r.server_addr
    ,r.probe_addr
    ,r.rsrcIndex
    ,r.rsrcOtherIndex
    ,r.rsrcName
    ,r.userId
GO
GRANT SELECT ON  [dbo].[BGP4ScoreBoard] TO [uniadmin]
GO