1 Reply Latest reply on Aug 6, 2009 1:25 PM by c1sc0m

    Availability and Traffic Report

      Hi,

       

      I need to run a report that shows the Network Average Traffic of the Month and Including the Availability

      for that Month too.  I know nothing about SQL, and I think it's the only way I can generate that kind of report.

       

      Any guidance is welcome.

        • Re: Availability and Traffic Report

          This is the code I managed to create by looking at the builtin templates:

           

          SELECT TOP 10000 CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth,
          Nodes.Department AS Department,
          Nodes.City AS City,
          Nodes.NodeID AS NodeID,
          Interfaces.InterfaceID AS InterfaceID,
          Nodes.Caption AS NodeName,
          Nodes.IP_Address AS IP_Address,
          Interfaces.InterfaceSpeed AS Interface_Speed,
          MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
          MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
          AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability




          FROM
          (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID) INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)


          WHERE
          (
            ( DateTime BETWEEN 37954 AND 37983 ) AND
            (Interfaces.InterfaceType <> 77) AND
            (Interfaces.InterfaceType <> 63) AND
            (Nodes.Department = 'Branches')
          )


          GROUP BY Nodes.Department, Nodes.City, Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.IP_Address, Interfaces.InterfaceSpeed

          ORDER BY 1 ASC, 2 ASC, 5 ASC

           

          Now I'm getting this error "Ambiguous column name 'DateTime'.

          I want create a column with the corresponding average availabity % (Last Month) at the end of this report.  

          As I said before I have no experience with DataBases.  I'm just trying to figure out the problem by trial and error.