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
Hi Neal,
Thank you so much for the SQL. I will surely try it after modifying it to my needs and let you know. To be specific, all the devices for which I want to get percentage availability have CDM probe with uptime option enabled so I am going to use CDM probe with uptime interval < 3600 seconds for the same.
The only thing I would have wanted is that uptime value gets recorded as another QoS object. However I will work on that as well.
Regards,
Amit Saxena
Hi Neal,
I used your SQL and modified it by using QOS_COMPUTER_UPTIME as QoS and appropriate RN_QOS_DATA_???? table to suit my environment.
I have 3 robots "A9C0B9VM1", "A9C0B9VM2", "A9DCSR1SBC0B9" and 1 hub-robot "A9C0B9VM0" in my development environment. Every host has Nimsoft robot installed and CDM probe is installed with "uptime" option checked on all of them.
All non-hub robots are not rebooted so far. Only hub-robot is rebooted multiple times.
My queries are mentioned below.
1) Please let me know the significance of the check "RN_QOS_DATA_0005.samplevalue > 2000" in the SQL query. As far as I know, any uptime value of less than 3600 should reflect system as rebooted. When "2000" was used in the SQL query, the availability was reflected as 0% for all non-hub robots and 1.40% for all hub robots as confirmed from the screenshot below.
However when I changed the check to "RN_QOS_DATA_0005.samplevalue < 3600", I got correct values of percentage availability as confirmed from the screenshot below.
2) How to get the output report in bar format ? For me, I am only able to get the output in text format.
The modified version of SQL is pasted below.
SELECT
S_QOS_DATA.target,
CAST
(
ROUND
(
AVG
(
(
CASE
WHEN
RN_QOS_DATA_0005.samplevalue IS NULL
OR
RN_QOS_DATA_0005.samplevalue < 3600
THEN
CAST
(
0 AS Decimal(15, 2)
)
ELSE
CAST
(
1 AS Decimal(15, 2)
)
END
)
) * 100, 3, 0
) AS Decimal(15, 2)
) AS Availability
FROM
S_QOS_DATA
INNER JOIN
RN_QOS_DATA_0005 ON S_QOS_DATA.table_id = RN_QOS_DATA_0005.table_id
WHERE
(
S_QOS_DATA.qos = 'QOS_COMPUTER_UPTIME'
)
AND
(
MONTH ( RN_QOS_DATA_0005.sampletime ) = MONTH ( { fn NOW() } )
)
AND
(
YEAR ( RN_QOS_DATA_0005.sampletime ) = YEAR( { fn NOW() } )
)
GROUP BY
S_QOS_DATA.target
ORDER BY
Availability
Regards,
Amit Saxena
This last query wiil result in a report for the last 30 days past? The parameter below?
MONTH ( RN_QOS_DATA_0003.sampletime ) = MONTH ( { fn NOW() } )
)
AND
(
YEAR ( RN_QOS_DATA_0003.sampletime ) = YEAR( { fn NOW() } )
)
If I generate the report today means the uptime for the last 30 days (from 09-05-2012 to 10-05-2010?
If you are using UR you will need to use the chart and then create a group I believe so your chart will be in the group not the summary area. I built a UR on the query I gave you in a previous post that list hostname/ip and then a barchart with Red and Green that show the value. If you need the xml from it let me know and I can attach.
@leonardoalmeida
I'm no SQL expert, but I believe that will only query QOS values from the current month and year. Personally, I prefer to just use something like: "[...] where QOS.sampletime >= dateadd(day, -30, GETDATE())"
I also figured I would share my solution for this, for anyone that is interested or doesn't use UR. It uses net_connect QOS values to calculate network availability and sends out the report as an HTML email.
It's tailored for my needs; we treat each Origin as a distinct site, or customer, so it may need to be modified to suit.
Basically, this script runs from the NAS on a monthly schedule and queries each unique Origin sequentially, that has net_connect data within the past 30 days. It also does a rough calculation of the approximate downtime, basically: # NULL values * probe polling interval.
Of note: I use SQL views for my QOS data, as I find it makes reading/writing my SQL much easier than having to reference the RN tables. I've included the script to create the views; it was taken from this forum and just slightly modified so it ran through the SLM. It also includes a libMath library for LUA.
My code is pretty sloppy, but should be relatively easy to follow/modify 
Sample email output also attached.
-Cam
Hey Cahm,
The report looks good. I am trying to modify this script for my environment. Before that i would like to know how the netconnect availability calculation is done . Please advise.
Thanks,
Nikhil
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