6 Replies Latest reply on Jun 16, 2014 11:12 AM by RichardLetts

    SWQL query across Cirrus and Orion objects

    RichardLetts

      I am trying to execute the following SWQL query:

       

      SELECT n1.ip_address, i4.interfaceindex, i4.interfacename,RemoteSystemName

      FROM Orion.NodeLldpEntry NLE inner join Orion.nodes N1

        on NLE.nodeid=n1.nodeid

      inner join Orion.nodes N2

        on NLE.remotesystemname=n2.sysname

      inner join Cirrus.Nodes n3

        on n3.corenodeid=n1.nodeid

      inner join Cirrus.Interfaces i3

        on i3.interfaceindex = nle.localportnumber and i3.nodeid=n3.nodeid

      inner join Cirrus.Interfaces i4

        on i3.macaddress = i4.macaddress and i4.PhysicalInterface='Y'

      left outer join Orion.NPM.Interfaces i

        on i.interfaceindex = nle.localportnumber and i.nodeid=nle.nodeid

      left outer join Orion.NPM.Interfaces i5

        on i5.interfaceindex = i4.interfaceindex and i5.nodeid=nle.nodeid

      where i.interfaceid is null and i5.interfaceindex is null

      and n1.caption not like '%@%'

      and nle.localportnumber != 0

       

      [this query identifies unmonitored layer-2 interfaces between two monitored nodes.]

      The information service is misconverting this into the following SQL:

       

      SELECT [T1].[IP_Address] AS C1, [T2].[InterfaceIndex] AS C2, [T2].[InterfaceName] AS C3, [T3].[RemoteSystemName] AS C4

      FROM [dbo].[NodeLldpEntries] AS T3

      INNER JOIN [dbo].[Nodes] AS T1 ON ([T3].[NodeID] = [T1].[NodeID])

      INNER JOIN [dbo].[Nodes] AS T6 ON ([T3].[RemoteSystemName] = [T6].[SysName])

      INNER JOIN [Nodes] AS T7 ON ([T7].[CoreNodeID] = [T1].[NodeID])

      INNER JOIN [Interfaces] AS T8 ON (([T8].[InterfaceIndex] = [T3].[LocalPortNumber]) AND ([T8].[NodeID] = [T7].[NodeID]))

      INNER JOIN [Interfaces] AS T2 ON (([T8].[MACAddress] = [T2].[MACAddress]) AND ([T2].[PhysicalInterface] = N'Y'))

      LEFT JOIN [dbo].[Interfaces] AS T4 ON (([T4].[InterfaceIndex] = [T3].[LocalPortNumber]) AND ([T4].[NodeID] = [T3].[NodeID]))

      LEFT JOIN [dbo].[Interfaces] AS T5 ON (([T5].[InterfaceIndex] = [T2].[InterfaceIndex]) AND ([T5].[NodeID] = [T3].[NodeID]))

      WHERE (((([T4].[InterfaceID] IS NULL) AND ([T5].[InterfaceIndex] IS NULL)) AND ([T1].[Caption] NOT LIKE N'%@%')) AND ([T3].[LocalPortNumber] <> 0))

       

      notice the database selector for the Cirrus and Orion SWQL objects are missing.

       

      Suggestions?

        • Re: SWQL query across Cirrus and Orion objects
          tdanner

          What version of NCM?

            • Re: SWQL query across Cirrus and Orion objects
              RichardLetts

              [well, DUH on me for not including this]

              NCM: 7.2.1 / NPM: 10.6.1 / UDT 3.1.2: + a whole load of buddy drops

                • Re: SWQL query across Cirrus and Orion objects
                  madhavan

                  Hi,

                   

                  In this version of NCM, Cirrus tables and Orion tables are in two different databases. SWIS takes care of getting the data from these tables and joining in memory. That's the reason you are not seeing db names in the formed query.

                   

                  Did you get this query from log file or SQL profiler?

                   

                  Moreover, whether the SWQL query you use returns incorrect results? or you are concerned about the SQL query formed?

                    • Re: SWQL query across Cirrus and Orion objects
                      RichardLetts

                      I have a report that has the SQL that works in it that runs correctly under the reportWriter.

                      I converted that into SWQL and references the SWIS tables in the query (because I want to use the returned values in a program to monitor interfaces)

                       

                      the two queries I pasted in above were copied from the InformationService logfile, and show what it received, and what it converted it into.

                       

                      it is obvious that SWIS does not not take care of getting the data from the tables and joining them in memory -- that is a hard task and

                       

                      it botched the SWQL to SQL conversion by not fully qualifying the table's names; the following is the correct SWQL->SQL translation

                       

                      SELECT [T1].[IP_Address] AS C1, [T2].[InterfaceIndex] AS C2, [T2].[InterfaceName] AS C3, [T3].[RemoteSystemName] AS C4

                      FROM [SolarwindsOrion].[dbo].[NodeLldpEntries] AS T3

                      INNER JOIN [SolarwindsOrion].[dbo].[Nodes] AS T1 ON ([T3].[NodeID] = [T1].[NodeID])

                      INNER JOIN [SolarwindsOrion].[dbo].[Nodes] AS T6 ON ([T3].[RemoteSystemName] = [T6].[SysName])

                      INNER JOIN [ConfigMgmt].[dbo].[Nodes] AS T7 ON ([T7].[CoreNodeID] = [T1].[NodeID])

                      INNER JOIN [ConfigMgmt].[dbo].[Interfaces] AS T8 ON (([T8].[InterfaceIndex] = [T3].[LocalPortNumber]) AND ([T8].[NodeID] = [T7].[NodeID]))

                      INNER JOIN [ConfigMgmt].[dbo].[Interfaces] AS T2 ON (([T8].[MACAddress] = [T2].[MACAddress]) AND ([T2].[PhysicalInterface] = N'Y'))

                      LEFT JOIN [SolarwindsOrion].[dbo].[Interfaces] AS T4 ON (([T4].[InterfaceIndex] = [T3].[LocalPortNumber]) AND ([T4].[NodeID] = [T3].[NodeID]))

                      LEFT JOIN [SolarwindsOrion].[dbo].[Interfaces] AS T5 ON (([T5].[InterfaceIndex] = [T2].[InterfaceIndex]) AND ([T5].[NodeID] = [T3].[NodeID]))

                      WHERE (((([T4].[InterfaceID] IS NULL) AND ([T5].[InterfaceIndex] IS NULL)) AND ([T1].[Caption] NOT LIKE N'%@%')) AND ([T3].[LocalPortNumber] <> 0))

                        • Re: SWQL query across Cirrus and Orion objects
                          madhavan

                          Hi,

                           

                          In NCM 7.2 version, there are two different databases. One for Orion and other for Cirrus and this query uses tables from both the databases. SWIS has its own schema and knows how to handle these tables. In your case both the database are in the same server, but there are clients who have these databases in different servers and that's the reason SWIS is handing the query data in memory. There will be a performance impact to do in memory operation based on the query and data.

                           

                          In NCM 7.3, these two databases are merged into one and so you will not face this kind of in memory operation. I would suggest you to upgrade to NCM 7.3 which is available now.

                           

                          The query in the log file is the query formed by swis before in memory operation and that is not the query executed against the sql server.

                           

                          Also, does the sql query and the swql query behaves differently in terms of data returned? or do you experience any other exception like memory or timeout?

                          1 of 1 people found this helpful
                            • Re: SWQL query across Cirrus and Orion objects
                              RichardLetts

                              the SWQL doesn't return any data -- it times out.

                              And, now I look at that it's executing on the SQL server I can see why: it's decomposing the query into individual table select's; this is probably never going to work -- a simplistic in memory join of cirrus.interfaces with cirrus.interfaces on MAC address will generate 57 million rows instead of the ~18,000 rows if you only consider ones which are in the LLDP table.

                               

                              will wait until NCM7.3 is deployed and recheck this is sane.