cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

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

0 Kudos
4 Replies
Level 9

The statement provided mesverrum​ definitely worked. However I am not doing a good explaining what we are try to convert.

So with a few mods this report is what we are trying to run out of the EOC, since now we have several orion primary with DB at various locations that feed the EOC.  We use to run this in SQL Reporting Server, but that option will not be available much longer.

;with BaseQuery as (

select distinct

n.Caption Node_Name,

n.Description,

n.Location,

n.MachineType,

n.Status,

n.City,

n.AssetTag,

n.Device_Function,

n.SerialNumber,

n.Network_Zone,

n.Number_of_Physical_Devices,

TRU = 1,

n.RU,

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

Inactive_Port_Count =

(select COUNT(i.status) from Interfaces i where i.NodeID = n.NodeID and (i.Status <> 1 and 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')),

Total_port_Count =

(select COUNT(i.status) from Interfaces i where i.NodeID = n.NodeID

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[SolarWindsOrion].[dbo].[Nodes] n

inner join [SolarWindsOrion].[dbo].[Interfaces] i on i.NodeID = n.NodeID

where n.Security_Boundary = 'XXX'

and RU <> 'EMCO')

select RU,

       SUM(TRU) as 'TRU-tot',

       SUM(TRU) as 'NPD',

       SUM(Active_Port_Count) as 'Active Port Count',

       SUM(Total_port_Count) as 'Total Port Counts'

from BaseQuery

Group by RU

order by RU

0 Kudos

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.

- Marc Netterfield, Github
0 Kudos

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

0 Kudos

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

- Marc Netterfield, Github
0 Kudos