During a POC this week a customer wanted to know if we could provide device availability metrics, the only thing I could find was Jim C's probe that runs based on the SNMP uptime OID. The prospect was wondering if it was possible without enabling and configuring SNMP on their customer's devices so I tried to hack something up in SQL. This query uses the net_connect response QOS metrics and basically associates a NULL value to being unavailable, and anything NOT NULL as available. It doesn't currently have any formatting for the percentage, as with my limited SQL skills I couldn't make it work correctly. Maybe someone more skilled than I can correct the percentage formatting.
Tips: You will need to adjust the RN_QOS_DATA_XXXX table reference and it should be good to go. If you want to separate on other columns (origin, source, etc) just add those into the select statements.
select REPLACE([Target], '
ing', ''), [Available], [NotAvailable], [Available] * 100.0 / sum([Available]+[NotAvailable]) as [Percentage] from ( Select s.target as [Target], sum(case when rx.samplevalue IS NOT NULL then 1 else 0 end) as [Available], sum(case when rx.samplevalue IS NULL then 1 else 0 end) as [NotAvailable] from S_QOS_DATA s join (select r.* from RN_QOS_DATA_0001 r Join (Select r1.Table_id from RN_QOS_DATA_0001 r1 group by r1.Table_ID) rr on r.table_id = rr.table_id ) rx on s.table_id = rx.table_id where s.target like '%ping%' group by s.target ) b group by b.target,b.Available, b.NotAvailable