Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

SQL Error

Hello, Can someone help me with this error.

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

If I run the above seperate, it works fine, but I need to add both in the report, speeds and availability. thanks

Find more posts tagged with

Accepted answers

dunky2k

@mrkuwait

Run the following, again adjusting the 'Select Top X' to suit your environment.

select top 5
T1.NodeID as NodeID,
T1.InterfaceID as InterfaceID,
T1.Caption as NodeName,
T1.Interface_Name as Interface_Name,
AVG(T1.inaver) as AVERAGE_of_Average_Receive_bps,
MAX(T1.inmax) as MAX_of_Peak_Receive_bps,
AVG(T1.outaver) as AVERAGE_of_Average_Transmit_bps,
MAX(T1.outmax) as MAX_of_Peak_Transmit_bps,
AVG(T2.avail) as AVERAGE_of_Availability
from
(SELECT TOP 10
nodes.NodeID,
interfaces.InterfaceID,
interfaces.CarrierName,
nodes.Caption,
Interfaces.Caption as Interface_Name,
AVG(InterfaceTraffic.In_averagebps) as inaver,
MAX(InterfaceTraffic.In_Maxbps) as inmax,
AVG(InterfaceTraffic.Out_averagebps) as outaver,
MAX(InterfaceTraffic.Out_maxbps) as outmax
from
Nodes
Inner Join Interfaces on (Interfaces.NodeID = Nodes.NodeID)
Inner join InterfaceTraffic on (Interfacetraffic.InterfaceID = interfaces.InterfaceID AND Interfacetraffic.NodeID = nodes.NodeID)
group by nodes.NodeID, interfaces.InterfaceID, Interfaces.CarrierName, nodes.Caption, interfaces.Caption) T1

left join

(select TOP 10
nodes.NodeID,
interfaces.InterfaceID,
nodes.Caption,
Interfaces.Caption as Interface_Name,
AVG(DailyNodeAvailability.Availability) as avail
from
Nodes
Inner Join Interfaces on (Interfaces.NodeID = Nodes.NodeID)
Inner join DailyNodeAvailability on (DailyNodeAvailability.NodeID = nodes.NodeID)
group by nodes.NodeID, interfaces.InterfaceID, nodes.Caption, interfaces.Caption) T2

on T1.NodeID = T2.NodeID

where T1.CarrierName = 'MW_LINK'

group by T1.NodeID, T1.InterfaceID, T1.Caption, T1.Interface_Name

mrkuwait

Hi, I tried that. It gives me only 3 nodes, whereas it's on select top 10 and availability not for all. So changed it to Select top 100 and I have 99 Nodes to be displayed but it only shows 34 nodes. then I ran the last query you sent me without the 30 days statement and its behaving the same way

dunky2k

@mrkuwait

What exactly are you trying to achieve here, are you trying to get the average node availability for the last 30 days and then match up the same average interfacetraffic on these nodes for the same 30 day period?

If your interfacetraffic table is anything like ours, then this table contains 365 days worth of data, so your first query is averaging the interface traffic over the entire year rather than the last 30 days.

If you are trying to get the avg node availability and avg interfacetraffic for the past 30 days then run the query below

select
T1.NodeID as NodeID,
T1.InterfaceID as InterfaceID,
T1.Caption as NodeName,
T1.Interface_Name as Interface_Name,
AVG(T1.inaver) as AVERAGE_of_Average_Receive_bps,
MAX(T1.inmax) as MAX_of_Peak_Receive_bps,
AVG(T1.outaver) as AVERAGE_of_Average_Transmit_bps,
MAX(T1.outmax) as MAX_of_Peak_Transmit_bps,
AVG(T2.avail) as AVERAGE_of_Availability
from
(SELECT
nodes.NodeID,
interfaces.InterfaceID,
interfaces.CarrierName,
nodes.Caption,
Interfaces.Caption as Interface_Name,
AVG(InterfaceTraffic.In_averagebps) as inaver,
MAX(InterfaceTraffic.In_Maxbps) as inmax,
AVG(InterfaceTraffic.Out_averagebps) as outaver,
MAX(InterfaceTraffic.Out_maxbps) as outmax
from
Nodes
Inner Join Interfaces on (Interfaces.NodeID = Nodes.NodeID)
Inner join InterfaceTraffic on (Interfacetraffic.InterfaceID = interfaces.InterfaceID AND Interfacetraffic.NodeID = nodes.NodeID)
WHERE (InterfaceTraffic.DateTime between dateadd(day, -30, getdate() ) and getdate ())
group by nodes.NodeID, interfaces.InterfaceID, Interfaces.CarrierName, nodes.Caption, interfaces.Caption) T1

left join

(select
nodes.NodeID,
interfaces.InterfaceID,
nodes.Caption,
Interfaces.Caption as Interface_Name,
AVG(DailyNodeAvailability.Availability) as avail
from
Nodes
Inner Join Interfaces on (Interfaces.NodeID = Nodes.NodeID)
Inner join DailyNodeAvailability on (DailyNodeAvailability.NodeID = nodes.NodeID)
WHERE (DailyNodeAvailability.DateTime between dateadd(day, -30, getdate() ) and getdate ())
group by nodes.NodeID, interfaces.InterfaceID, nodes.Caption, interfaces.Caption) T2

on T1.NodeID = T2.NodeID

where T1.CarrierName = 'MW_LINK'

group by T1.NodeID, T1.InterfaceID, T1.Caption, T1.Interface_Name

If you run the individual queries you may get more results showing, but by joining the queries together this will only show the final data where there are entries for both nodes and interfacetraffic that match on NodeId.

e.g. you may have a node that has no interface traffic recorded as the interface may not be selected to be monitored, hence the difference in results.

In my scenario, the nodes availability has 9370 rows, the interfacetraffic availability has 7857 so the combined join creates 7857 entries, hope that helps.

All comments

dunky2k

Try changing the From statement to the below.

FROM

Nodes

Inner Join Interfaces on (Interfaces.NodeID = Nodes.NodeID)

Inner Join InterfaceTraffic on (InterfaceTraffic.InterfaceID = Interfaces.interfaceID AND Interfacetraffic.NodeID = Nodes.NodeID)

Inner Join DailyNodeAvailability on (DailyNodeAvailability.Nodeid = Nodes.NodeID)

I ran this on my test system without any errors, however i had no where clauses defined.

mrkuwait

Hi, Same error

dunky2k

Out of curiosity, can you change the select to only output the top 10 rather than top 1000 and see if that runs.

mrkuwait

Hi, Same error. You see when I run them sepetare, they work fine, but dont run together.

shuth

Best guess is the query is timing out because of the way the data is being joined.

I did a quick test on my lab system using your query without the AVG/MAX calculations to see the raw data and it returned 95,004 rows of data. However, my lab only has a few devices and a couple of interfaces - InterfaceTraffic view only has 812 rows and the DailyNodeAvailability view has 1280 rows.

If I add in the DateTime columns from each view, the number of returned rows is the same (95,004) and I can see that is matching all of the interface data against each node availability timestamp.

If you have a lot of nodes and interfaces in that report, it is probably generating a massive table that is taking too long to finish executing hence times out.

Unfortunately I don't know SQL well enough to optimise the query/join or if it's even possible to do so optimally without splitting it into two separate queries.

You can run the below query without the MAX/AVG calculations (add your WHERE clauses) to see the raw data and get an idea of the number of rows being calculated on.

SELECT Nodes.NodeID AS NodeID
 , Interfaces.InterfaceID AS InterfaceID
 , Nodes.Caption AS [NodeName]
 , Interfaces.Caption AS [Interface_Name]
 , InterfaceTraffic.In_Averagebps
 , InterfaceTraffic.In_Maxbps
 , InterfaceTraffic.Out_Averagebps
 , InterfaceTraffic.Out_Maxbps
 , DailyNodeAvailability.Availability
 --, InterfaceTraffic.DateTime AS InterfaceTrafficDate
 --, DailyNodeAvailability.DateTime AS DailyNodeAvailDate
 FROM Nodes
  INNER JOIN Interfaces ON Interfaces.NodeID = Nodes.NodeID
  INNER JOIN InterfaceTraffic ON (InterfaceTraffic.InterfaceID = Interfaces.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
  INNER JOIN DailyNodeAvailability ON DailyNodeAvailability.NodeID = Nodes.NodeID