DX Unified Infrastructure Management

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

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