17 Replies Latest reply on Apr 22, 2016 2:06 AM by lynchnigel

    Custom Table SQL




      I struggle to use macros in SQL in a custom table. Is this not possible??


      Here is my example:


      SELECT CS.RowID as RowID, IsNull(CS.Status, 0) as Value

      INTO #Sensor_Names

      FROM CustomPollerStatus CS

      INNER JOIN CustomPollerAssignment CA ON CA.CustomPollerAssignmentID = CS.CustomPollerAssignmentID

      WHERE (CA.CustomPollerID='ABB18B3C-BCB2-473D-AC66-24BED1C8C799') AND (CA.NodeID=114)


      I want to replace teh last part of the WHERE clause with "(CA.NodeID=${nodeid}) but that doesnt work...


      Any ideas?

        • Re: Custom Table SQL

          What kind of view are you on when you are seeing that error?  Macros like ${Nodeid} don't work on summary views because they aren't associated with particular nodes.  If you try to build a custom table on a node details view it has a check box to use the current object as the data source, which is functionally the same as matching the ${nodeid} macro


          I find that if you get familiar with the SWQL syntax you can generally build reports more easily as a Custom Query resource than using SQL and SWQL to define a data source in the Custom Table resources.  It's also nice because then you can use the SWQL Studio app to test them and that is MUCH faster than going back and forth on the web console.



          -Marc Netterfield

              Loop1 Systems: SolarWinds Training and Professional Services

            • Re: Custom Table SQL



              The view type is Node Details


              If I click on use curent node as datasource I loose my SQL...

              If I deselect it, I get my SQL back (Advanced database Query with SQL selected) and

              if I replace my hardcoded node ID with  ${Nodeid} in the WHERE clause I get "Query not valid"...


              I probably would be better off learning SWQL. I prefer SQL for now because I know it wery well, and I can find everthing I want very easy in the DB.

              With WSQL I feel like i am very limited as there is limited online help, and the table/view names and relations between them is very obscure...


              But SWQL studio sounds interresting, where can I find that?

              • Re: Custom Table SQL

                I installed SWQL Studio, and now it is as easy to make SWQL queries as it is to make SQL queries :-)


                As for Custom resources: SWQL queries seems nice for plain tables, however I do not see that I can get

                icons and interpret html with them as I can with Custom Tables.


                My main question remains though; No matter what I try I dont manage to limit the scope for my

                SWQL or SQL queries to the current node.

              • Re: Custom Table SQL

                I'd open a ticket on this then.  Please post results!

                • Re: Custom Table SQL

                  Custom Table does not support any of those fun things unfortunately.  I think I saw a bug on the forum a while back where the Solarwinds devs were aware of the issue that ${nodeid} should be failing their syntax check on the custom table resource but wasn't.

                    • Re: Custom Table SQL

                      Well, that was extremely unfortunate...

                      Then as far as I can see there is no way I can make a nice table with icons and html text to present a summary of UnDP pollers....

                        • Re: Custom Table SQL

                          You can do customized icons and links, but if I recall correctly from past experiments it won't parse html.

                          Here is a custom query example for a summary page that has the syntax for links, icons, and a case scenario for using different icons to represent a condition.



                          n.caption as Node
                          , '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.nodeid) AS [_LinkFor_Node]
                          , '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
                          , tolocal(UnManageFrom) as [Unmanage From]
                          , tolocal(UnManageUntil) as [Unmanage To]
                          ,getdate() as Today
                          , daydiff(UnManagefrom ,getutcdate())as [Days since Unmanage]
                          WHEN daydiff(UnManagefrom ,getutcdate()) < 90 THEN 'No'
                          WHEN daydiff(UnManagefrom ,getutcdate()) > 89 THEN 'Yes'
                          END AS [Over 90 days]
                          , CASE
                          WHEN daydiff(UnManagefrom ,getutcdate()) < 90 THEN '/Orion/images/ActiveAlerts/Serious.png'
                          WHEN daydiff(UnManagefrom ,getutcdate()) > 89 THEN '/Orion/images/ActiveAlerts/Check.png'
                          END AS [_IconFor_Over 90 days]
                          , daydiff(getutcdate(), UnManageuntil)as [Days until Remanage]
                          FROM Orion.Nodes n
                          where status = 9
                          order by unmanagefrom


                          2 of 2 people found this helpful
                      • Re: Custom Table SQL

                        Is there any other _ (underscore) features other than _IconFor an _LinkFor ?