4 Replies Latest reply on Aug 16, 2019 8:44 AM by tmcurry

    EOC, SQL, SWQL for reports

    tmcurry

      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

        • Re: EOC, SQL, SWQL for reports
          mesverrum

          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.

            • Re: EOC, SQL, SWQL for reports
              tmcurry

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

                • Re: EOC, SQL, SWQL for reports
                  mesverrum

                  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

              • Re: EOC, SQL, SWQL for reports
                tmcurry

                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