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

    Custom Table SQL

    lll8lll

      Hi,

       

      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
          mesverrum

          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
              lll8lll

              Hi,

               

              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
                  mesverrum

                  Release v2.0.50 · solarwinds/OrionSDK · GitHub

                   

                  It is part of the SDK, and if you are trying to figure out the tables and such without it then you are going to have a painful time.  The thing to keep in mind is that SWQL is sanitized to be read only so any commands that would impact the contents of the database probably aren't going to work.  Other than that there are slight changes in the datediffs and a few extra commands but if you are strong in SQL you can be working comfortably in SWQL almost right away once you install that. 

                   

                  -Marc Netterfield

                      Loop1 Systems: SolarWinds Training and Professional Services

                  • Re: Custom Table SQL
                    njoylif

                    try ${ID} and ${NodeId} (these will be legacy).

                    for SWQL, try ${N=SwisEntity;M=NodeID}

                    I've found SWQL and macros to be case sensative in some places and not in others.  I usually just bang my head against it until it either works or my head splits open.

                      • Re: Custom Table SQL
                        lll8lll

                        Thx, but that doesnt work either. I just get "Query not valid" no matter where in my SQL I put ${NodeId} or ${ID}.

                          • Re: Custom Table SQL
                            njoylif

                            try using ${N=SwisEntity;M=NodeID}.  I know you're not using a SWQL query, but the parser may be looking for this type of entry.

                            • Re: Custom Table SQL
                              lll8lll

                              That did not work either...

                               

                              I even tried all three macro alternatives i SWQL but none works...

                               

                              Actually SWQL ignores the ${NodeId} part of WHERE (...) AND (<snip>=${NodeId}) and returns all rows.

                               

                              If I use

                              WHERE (CA.CustomPollerID='ABB18B3C-BCB2-473D-AC66-24BED1C8C799') AND (CA.NodeID=${N=SwisEntity;M=NodeID})

                               

                              I get "Query not valid"

                               

                              I am beginning to think it is not possible to set a scope for custom SWQL/SQL resources.... But I cannot realy believe that...

                        • Re: Custom Table SQL
                          lll8lll

                          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
                          njoylif

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

                          • Re: Custom Table SQL
                            mesverrum

                            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
                                lll8lll

                                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
                                    mesverrum

                                    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.

                                     

                                     

                                    SELECT
                                    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]
                                    ,case
                                    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
                                  lll8lll

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