Availability and Traffic Report

c1sc0m

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.

c1sc0m

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.