NPM Node Reliability Report ( Availability - Maintenance Window Time frame) by month and by year

Version 1

    This NPM  Document modifies the Out of Box Report Writer Availability Reports for month & for year.

     

    --******************************************************************************************************************

     

    -- Node Reliabilty (customized out of box Availability last YR from report writer)

     

     

    SELECT TOP 100000

     

     

    Nodes.NodeID AS NodeID,

    Nodes.VendorIcon AS Vendor_Icon,

    Nodes.Caption AS NodeName,

    Nodes.IP_Address AS IP_Address,

    AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,

    CONVERT(DateTime,

    LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

    101) AS SummaryMonth

     

     

    FROM

    Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

     

     

     

     

    WHERE

    -- between today-1-yr-ago and today (march 25th 2017)

    --( DateTime BETWEEN (42792-365) AND 42792 )

    DateTime > DATEADD(year,-1,GETDATE())

     

     

    AND 

    -- restrict day time windows

    (

      (

       -- sat before 11pm utc (- 4 hr from 3 ETZ)

       ( DATEPART(weekday, DateTime) = 7 AND DatePart(Hour,DateTime) < 23 )

       AND

       -- sun before 2am utc (- 4 from 6 ETZ)

       ( DATEPART(weekday, DateTime) = 1 AND DatePart(Hour,DateTime) > 2 )

      )

      or

      (

       -- betweenn monday and friday

       DATEPART(weekday, DateTime) between 2 and 6

      )

    )

     

     

    GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

    Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

     

    ORDER BY 3 ASC ,SummaryMonth ASC

    --******************************************************************************************************************

     

    --******************************************************************************************************************

     

    -- Node Reliabilty (customized out of box Availability last month)

     

     

    SELECT  TOP 10000

     

     

    CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth,

    Nodes.NodeID AS NodeID,

    Nodes.VendorIcon AS Vendor_Icon,

    Nodes.Caption AS NodeName,

    Nodes.IP_Address AS IP_Address,

    AVG(ResponseTime.Availability) AS AVERAGE_of_Availability

     

     

    FROM

    Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

     

     

    WHERE

    -- last month (march 25th 2017)

    -- ( DateTime BETWEEN 42765 AND 42792.9999884259 )

    datetime >= DATEADD(month,-1,GETDATE())

     

     

    AND 

    -- restrict day time windows

    (

      (

       -- sat before 11pm utc (- 4 hr from 3 ETZ)

       ( DATEPART(weekday, DateTime) = 7 AND DatePart(Hour,DateTime) < 23 )

       AND

       -- sun before 2am utc (- 4 from 6 ETZ)

       ( DATEPART(weekday, DateTime) = 1 AND DatePart(Hour,DateTime) > 2 )

      )

      or

      (

       -- betweenn monday and friday

       DATEPART(weekday, DateTime) between 2 and 6

      )

    )

     

     

    GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

    Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address

     

     

     

     

    ORDER BY SummaryMonth ASC, 4 ASC

     

    --******************************************************************************************************************