We have a report written in sql that use to reside on a MS Reporting server.
The frame work is as follows in SQL
;with BaseQuery as (
select distinct
n.Caption Node_Name,
n.Description,
p.Network_Zone,
TotalResourceUnit = 1, (in SWQL this line looks like '1' AS [TotalResourceUnit], (thank you dgsmith80)
Active_Port_Count =
(select COUNT(i.status) from Interfaces i where i.NodeID = n.NodeID and (i.Status = 1 or i.Status = 2)
and
((i.InterfaceName like '%eth%') or (i.InterfaceName like '%gi%') or (i.InterfaceName like '%mgmt%') or (i.InterfaceName like '%xe%') or (i.InterfaceName like '%ge%') or (i.InterfaceName like '%unit%'))
and
(i.InterfaceName not like '%.0')),
From
inner join
where
select RU,
SUM(Active_Port_Count) as 'Active Port Count',
from BaseQuery
Group by RU
order by RU
-----
A few things to note I had to strip this down for obvious reasons like keeping me out of trouble.
There are several queries similar in nature to Active Port Count. My logic get one get them all.
IN SQL I joined 2 tables
IN SWQL I have to join 3 tables since custom properties do not appear in the orion.nodes like the Nodes view, so in SWQL my joins look like this:
FROM Orion.Nodes n
INNER JOIN Orion.NodesCustomProperties p ON p.NodeID = n.NodeID
INNER JOIN Orion.NPM.Interfaces i ON i.NodeID = n.NodeID
Any help would be appreciative my first take at SWQL had to be a more complex one then what i saw in my research.
Thank you in advance
