EOC, SQL, SWQL for reports

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

Parents
  • I'm a bit confused, am I reading correctly that active port count just returns the total of ports that are up or down and match the naming filters?  This query is really extremely complicated compared to what it should be.

  • it is stripped down for the sake of the forum.  There are other items included that i did not show.

  • Well for what you are showing I would write it like so

    select

    n.caption as Node_Name

    , n.Description

    , n.customproperties.Network_Zone

    , 1 as [TotalResourceUnit]

    count(i.status) as [Active_Port_Count]

    from orion.nodes n

    join orion.npm.interfaces i on i.nodeid=n.nodeid

    where

    ((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')

    group by

    n.caption

    , n.Description

    , n.customproperties.Network_Zone

Reply
  • Well for what you are showing I would write it like so

    select

    n.caption as Node_Name

    , n.Description

    , n.customproperties.Network_Zone

    , 1 as [TotalResourceUnit]

    count(i.status) as [Active_Port_Count]

    from orion.nodes n

    join orion.npm.interfaces i on i.nodeid=n.nodeid

    where

    ((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')

    group by

    n.caption

    , n.Description

    , n.customproperties.Network_Zone

Children
No Data