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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.