So I have a SQL query that was written long ago (and I am not a SQL person) and it is required for a report that goes to management. I have been playing with trying to port it to SWQL and have a lot of problems. Are there some resources out there to learn SWQL? The documentation is meh, and I am trying to do this in addition to my regular job. The SDK says that there is an error, but nothing about where the error is. There isn't a debug mode like in the SQL manager. Any help would be appreciated.
Here is the query in SQL.
SELECT TOP 95000 Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0) AS SummaryDateTime,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
AVG(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization
FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)
WHERE
( DateTime BETWEEN 44011 AND 44042 )
AND
(
(Interfaces.CPI = 1)
)
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0),
Nodes.Caption, Interfaces.Caption
ORDER BY SummaryDateTime ASC, 2 ASC, 3 ASC
Here is what I came up with SWQL, but it doesn't work and doesn't tell me where.
SELECT TOP 95000 Orion.NPM.Nodes.DisplayName as NodeName,
Orion.NPM.Interfaces.Caption AS Interface_Caption,
Orion.NPM.InterfacesCustomProperties.Backbone AS Backbone,
AVG(Case Orion.NPM.Interfaces.InBandwidth
WHEN 0 THEN 0
ELSE (Orion.NPM.InterfaceTraffic.InAveragebps/Orion.NPM.Interfaces.InBandwidth)
End) AS AVERAGE_of_Recv_Percent_Utilization,
AVG(CASE Orion.NPM.Interfaces.OutBandwidth
WHEN 0 then 0
ELSE (Orion.NPM.InterfaceTraffic.OutAveragebps/Orion.NPM.Interfaces.OutBandwidth)
End) AS AVERAGE_of_Xmit_Percent_Utilization
FROM
(Orion.NPM.Nodes INNER JOIN Orion.NPM.Interfaces ON (Orion.NPM.Nodes.ID)) INNER JOIN Orion.NPM.InterfaceTraffic ON
(Orion.NPM.Interfaces.InterfaceID = Orion.NPM.InterfaceTraffic.InterfaceID and Orion.NPM.InterfaceTraffic.NodeID = Orion.NPM.Nodes.ID)
WHERE
(DATETIME BETWEEN 44042 AND 44073)
AND
(
(Orion.NPM.Interfaces.CustomProperties.Backbone = 1)
)
GROUP BY
Orion.NPM.Nodes.DisplayName, Orion.NPM.Interfaces.Caption,
