CREATE VIEW InterfaceView AS
SELECT
    server_addr=p.server_addr
    ,probe_addr=p.probe_addr
    ,rsrcIndex=f.dbIndex
    ,rsrcOtherIndex=0
    ,rsrcType=-2
    ,collect_flags=p.collect_flags
    ,rsrcName = p.sysName + ' / '+convert(varchar(5),f.ifIndex)+' : '+ISNULL(i.itfcDescr,f.ifDescr)+ ' (bw= ' + CASE
    WHEN ISNULL(itfcSpeed,ifSpeed)=4294967295 THEN 'Unknown'
    WHEN ISNULL(itfcSpeed,ifSpeed)=1 THEN 'Unknown'
    WHEN ISNULL(itfcSpeed,ifSpeed) BETWEEN 1000.0 AND 1000000 THEN convert(varchar(5),ISNULL(itfcSpeed,ifSpeed)/1000) + ' Kbps'
    WHEN ISNULL(itfcSpeed,ifSpeed)>1000000 THEN convert(varchar(12),convert(int,1.0*ISNULL(itfcSpeed,ifSpeed)/1000000))+' Mbps'
    ELSE  convert(varchar(6),ISNULL(itfcSpeed,ifSpeed))+' bps'
    END+')'
    ,rsrcDescr=sysDescr
    ,IP_addr=p.IP_addr
    ,p.port
    ,community=community
    ,ifSpeed=isnull(i.itfcSpeed,f.ifSpeed)
    ,ifType=f.ifType
    ,agentName=convert(varchar(5),f.ifIndex)+' : '+ISNULL(i.itfcDescr,f.ifDescr)+ ' (bw= ' + CASE
    WHEN ISNULL(itfcSpeed,ifSpeed)=4294967295 THEN 'Unknown'
    WHEN ISNULL(itfcSpeed,ifSpeed)=1 THEN 'Unknown'
    WHEN ISNULL(itfcSpeed,ifSpeed) BETWEEN 1000.0 AND 1000000 THEN convert(varchar(5),ISNULL(itfcSpeed,ifSpeed)/1000) + ' Kbps'
    WHEN ISNULL(itfcSpeed,ifSpeed)>1000000 THEN convert(varchar(12),convert(int,1.0*ISNULL(itfcSpeed,ifSpeed)/1000000))+' Mbps'
    ELSE  convert(varchar(6),ISNULL(itfcSpeed,ifSpeed))+' bps'
    END+')'
    ,ifName=f.ifDescr
    ,ifIndex=f.ifIndex
    ,mibIndex=convert(varchar(10), f.ifIndex)
    ,mibId=0
    ,monitored=(CASE
    WHEN ((collect_flags & 67108864)=67108864 AND i.collReq IS NULL) THEN 0
    WHEN i.collReq & 0x1 = 0x1 THEN 0
    WHEN i.collReq & 32 = 32 THEN 0
    WHEN p.collect_flags < 0 THEN 0
    WHEN p.collect_flags >= 1073741824 THEN 0
    WHEN i.collReq & 0x2 = 0x2 THEN 2
    ELSE 1
    END) - (CASE WHEN i.itfcType & 0x1 = 0x1 THEN 3 ELSE 0 END)
    ,bypassAvail=
    CASE
    WHEN (p.collect_flags & 0x80000)>0 THEN 1
    WHEN i.collReq & 16 > 0 THEN 1
    ELSE 0 END
    ,viewType=2
    ,rsrcId=convert(varchar(10),-2)+':'+convert(varchar(12),p.probe_addr)+':'+convert(varchar(10),f.dbIndex)+':'+convert(varchar(10),0)
    ,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
GO
GRANT SELECT ON  [dbo].[InterfaceView] TO [uniadmin]
GO