Hi all,
I need to generate a report of percentage availability of all devices. Please suggest me how to proceed for this.
The report needs to be something like following.
Device Name Percentage Availability
device1 90%
device2 100%
device3 96%
Note : I am NOT looking for any SLA report.
Regards,
Amit Saxena
Try this sql query we use it to generate a unified report of current uptime for the month you will need to modify to fit your qos tables and time. It generates a report like attached. It also looks for response times under 2000 ms so you may need to modify that as well.
SELECT S_QOS_DATA.target, CAST(ROUND(AVG((CASE WHEN RN_QOS_DATA_0001.samplevalue IS NULL OR RN_QOS_DATA_0001.samplevalue > 2000 THEN CAST(0 AS Decimal(15, 2)) ELSE CAST(1 AS Decimal(15, 2)) END)) * 100, 3, 0) AS Decimal(15, 2)) AS AvailabilityFROM S_QOS_DATA INNER JOIN RN_QOS_DATA_0001 ON S_QOS_DATA.table_id = RN_QOS_DATA_0001.table_idWHERE (S_QOS_DATA.qos = 'QOS_NET_CONNECT') AND(MONTH(RN_QOS_DATA_0001.sampletime) = MONTH({ fn NOW() })) AND (YEAR(RN_QOS_DATA_0001.sampletime) = YEAR({ fn NOW() }))
GROUP BY S_QOS_DATA.targetORDER BY Availability