4 Replies Latest reply on Aug 16, 2016 3:24 PM by mmarchand

    How to change an SQL query into SWQL query

    mmarchand

      I have this SQL query for CDP neighors that works in report or directly into the database manager query

       

      Select CustomPollerStatusTable.Value AS [Local Interface], NodeCiscoCdpEntries.IPAddress AS [IP Address], NodeCiscoCdpEntries.DeviceId AS Device, DevicePort AS [Remote Interface] From  NodeCiscoCdpEntries inner join CustomPollerStatusTable on NodeCiscoCdpEntries.NodeID = CustomPollerStatusTable.NodeID where (NodeCiscoCdpEntries.IfIndex like CustomPollerStatusTable.RowID and NodeCiscoCdpEntries.NodeID = '91') the 91 here will be change for a variable in the Custom query

       

      Result in Database Manager

       

      Now I want to put this as a Table in Node Details but SQL query do not work in Custom Query

       

      How can I translate this in SWQL?

        • Re: How to change an SQL query into SWQL query
          sean.martinez

          Use the Legacy Report Writer on the Solarwinds Server and use the SQL statement you have. (2012: Report Writer  2008: Start Menu> Solarwinds Orion> Reports> Report Writer)

           

           

          • Create an Advanced SQL Report and use the SQL Statement (I removed the NodeID so that this can be used on every Node Details Page):

               Select CustomPollerStatusTable.Value AS [Local Interface], NodeCiscoCdpEntries.IPAddress AS [IP Address], NodeCiscoCdpEntries.DeviceId AS Device, DevicePort AS [Remote Interface] From  NodeCiscoCdpEntries inner join CustomPollerStatusTable on NodeCiscoCdpEntries.NodeID = CustomPollerStatusTable.NodeID where (NodeCiscoCdpEntries.IfIndex like CustomPollerStatusTable.RowID)

           

          • Update the Title name (used later) and save the report in the Reports folder.
          • On the Web Console, At the top right select Customize Page,
          • Select + (add the Resource)
          • Search for Report, look for "Report from Orion Report Writer"
          • save the view
          • Edit the Resource
          • select the Report (the Report will be found under the Report Title)
          • Select Submit
          1 of 1 people found this helpful
          • Re: How to change an SQL query into SWQL query
            blsanner

            No promises because I don't have these custom pollers, but try this SWQL in a Custom Query resource on your Node Details page:

             

            SELECT cps.Status AS [Local Interface],

            cdp.IPAddress AS [IP Address],

            cdp.DeviceId AS Device,

            cdp.DevicePort AS [Remote Interface]

            FROM  Orion.NodeCdpEntry cdp

            INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular cps

            ON cdp.NodeID = cps.NodeID

            WHERE cdp.IfIndex LIKE cps.RowID

            AND cdp.NodeID = ${NodeID}