This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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.

  • 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.