CREATE VIEW ResourceNameFRView AS
SELECT
    server_addr=p.server_addr
    ,probe_addr=p.probe_addr
    ,rsrcIndex=f.dbIndex
    ,rsrcOtherIndex=fr.frCircuitDlci
    ,rsrcType=5
    ,collect_flags=p.collect_flags
    ,rsrcName = p.sysName + ' / '+convert(varchar(5),f.ifIndex) +' : '+ISNULL(i.itfcDescr,f.ifDescr)+ ' Circuit Id: ' + convert(varchar(5), fr.frCircuitDlci)+ ' (bw= ' +
    CASE
    WHEN fr.frCommittedInformationRate IS NULL AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NULL THEN convert(varchar(12), f.ifSpeed) + ' Mbps'
    WHEN fr.frCommittedInformationRate = 0 AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NULL THEN convert(varchar(12),f.ifSpeed/1000000)+' Mbps'
    WHEN fr.frCommittedInformationRate = 0 AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NOT NULL THEN convert(varchar(12),i.itfcSpeed/1000000)+' Mbps'
    WHEN fr.frCommittedInformationRate IS NULL AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NOT NULL THEN convert(varchar(12), i.itfcSpeed/1000000)+ 'Mbps'
    WHEN fr.frCommittedInformationRate = 0 AND fr.frCircuitCommittedBurst > 0 THEN convert(varchar(12),fr.frCircuitCommittedBurst*1.0/1000000)+' Mbps'
    ELSE convert(varchar(12),fr.frCommittedInformationRate*1.0/1000000)+' Mbps'
    END+')'
    ,rsrcDescr=sysDescr
    ,IP_addr=p.IP_addr
    ,p.port
    ,community=community
    ,ifSpeed=CASE
    WHEN fr.frCommittedInformationRate IS NOT NULL AND fr.frCommittedInformationRate > 0 THEN fr.frCommittedInformationRate
    WHEN fr.frCircuitCommittedBurst IS NOT NULL AND fr.frCircuitCommittedBurst > 0 THEN fr.frCircuitCommittedBurst
    WHEN i.itfcSpeed IS NOT NULL AND i.itfcSpeed > 0 THEN i.itfcSpeed
    ELSE f.ifSpeed
    END
    ,ifType=f.ifType
    ,agentName=convert(varchar(5),f.ifIndex) +' : '+ISNULL(i.itfcDescr,f.ifDescr)+ ' Circuit Id: ' + convert(varchar(5), fr.frCircuitDlci)+ ' (bw= ' +
    CASE
    WHEN fr.frCommittedInformationRate IS NULL AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NULL THEN convert(varchar(12), f.ifSpeed) + ' Mbps'
    WHEN fr.frCommittedInformationRate = 0 AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NULL THEN convert(varchar(12),f.ifSpeed/1000000)+' Mbps'
    WHEN fr.frCommittedInformationRate = 0 AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NOT NULL THEN convert(varchar(12),i.itfcSpeed/1000000)+' Mbps'
    WHEN fr.frCommittedInformationRate IS NULL AND fr.frCircuitCommittedBurst = 0 AND i.itfcSpeed IS NOT NULL THEN convert(varchar(12), i.itfcSpeed/1000000)+ 'Mbps'
    WHEN fr.frCommittedInformationRate = 0 AND fr.frCircuitCommittedBurst > 0 THEN convert(varchar(12),fr.frCircuitCommittedBurst*1.0/1000000)+' Mbps'
    ELSE convert(varchar(12),fr.frCommittedInformationRate*1.0/1000000)+' Mbps'
    END+')'
    ,ifName=f.ifDescr
    ,ifIndex=f.ifIndex
    ,mibIndex=convert(varchar(10), f.ifIndex)+'.'+convert(varchar(10),fr.frCircuitDlci)
    ,mibId=0
    ,monitored=(CASE WHEN ((collect_flags & 67108864)=67108864 AND i.collReq IS NULL) THEN 0
    WHEN i.collReq & 0x1 = 0x1 THEN 0
    WHEN fr.collReq & 0x1 = 0x1 THEN 0
    WHEN p.collect_flags < 0 THEN 0
    WHEN p.collect_flags >= 1073741824 THEN 0
    ELSE 1
    END) - (CASE WHEN i.itfcType & 0x1 = 1 THEN 3 ELSE 0 END)
    ,bypassAvail=
    CASE
    WHEN (p.collect_flags & 0x80000)>0 THEN 1
    WHEN fr.collReq & 16 > 0 THEN 1
    ELSE 0 END
    ,viewType=32
    ,rsrcId=convert(varchar(10),5)+':'+convert(varchar(12),p.probe_addr)+':'+convert(varchar(10),f.dbIndex)+':'+convert(varchar(10),fr.frCircuitDlci)
    ,userId=p.userId
FROM (UserProbeView p join M2if f
ON p.server_addr=f.server_addr
AND p.probe_addr=f.probe_addr)
left join ItfcConfig i
ON f.server_addr=i.server_addr
AND f.probe_addr=i.probe_addr
AND f.dbIndex=i.itfcIndex
join FRCircuitId fr
    ON p.server_addr=fr.server_addr
    AND p.probe_addr=fr.probe_addr
    AND f.dbIndex=fr.frCircuitIfIndex
GO
GRANT SELECT ON  [dbo].[ResourceNameFRView] TO [uniadmin]
GO