DX Infrastructure Manager

Expand all | Collapse all

Generating a report for percentage availabilty of all devices

  • 1.  Generating a report for percentage availabilty of all devices

    Posted 09-30-2011 08:38 PM

    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



  • 2.  Re: Generating a report for percentage availabilty of all devices

    Posted 10-03-2011 10:38 PM
      |   view attached

    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



  • 3.  Re: Generating a report for percentage availabilty of all devices

    Posted 10-04-2011 07:32 AM

    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

     



  • 4.  Re: Generating a report for percentage availabilty of all devices

    Posted 10-20-2011 03:52 PM

    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

     



  • 5.  Re: Generating a report for percentage availabilty of all devices

    Posted 10-06-2012 12:33 AM

    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? 



  • 6.  Re: Generating a report for percentage availabilty of all devices

    Posted 10-09-2012 03:54 PM

    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.



  • 7.  Re: Generating a report for percentage availabilty of all devices

    Posted 10-12-2012 04:10 AM

    @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())"



  • 8.  Re: Generating a report for percentage availabilty of all devices

    Posted 10-12-2012 05:08 AM

    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 :smileywink:

     

    Sample email output also attached.

     

    -Cam 

    Attachment(s)



  • 9.  Re: Generating a report for percentage availabilty of all devices

    Posted 12-24-2018 09:13 AM

    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