7 Replies Latest reply on Nov 8, 2017 3:45 PM by mjperkins

    Report Writer Cannot Handle FROM Clause

    mjperkins

      I posted about a similar query and problem back in August. I appreciate everyone who tried to help (zackm, ctlswadmin, dnerdahl), though I never did find a solution.

      After another request for a report, I'm trying again, though I've narrowed the issue down a bit.

       

      The current query, which executes flawlessly in SWQL Studio, is:

       

      SELECT

        N.NodeID AS Node_UniqueID, N.CoreNodeID AS Node_ID, N.AgentIP AS IP_Address, N.NodeCaption AS "Node_Name",

        CA.ConfigType AS Config_Type, CA.DownloadTime AS Download_Time, CA.AttemptedDownloadTIme AS Last_Successful_Download_Time,

        SI.StatusName as "Status"

      FROM

        NCM.Nodes N INNER JOIN Cirrus.ConfigArchive CA ON N.NodeID = CA.NodeID

        INNER JOIN Orion.StatusInfo SI ON N.Status = SI.StatusID

      WHERE

        (CA.ConfigID = (SELECT TOP 1 CAA.ConfigID

           FROM Cirrus.ConfigArchive CAA

           WHERE CAA.NodeID = N.NodeID

           ORDER BY CAA.DownloadTime DESC)

         AND CA.AttemptedDownloadTime < ADDDAY(-1, GETDATE())

      ORDER BY CA.AttemptedDownloadTime

       

      Essentially, I am looking for NCM nodes where a nightly config backup has failed by pulling the date of the most recently downloaded configuration and showing only those which have not been downloaded in the past 24 hours, along with a node status that may show why (e.g., node is down or unmanaged), so my customer can investigate why NCM is unable to connect and pull configs from nodes of interest.

       

      As with my August issue, that query works as-is in SWQL Studio, but clicking the "Preview Results" button in Report Writer only gets a "Query is not valid" error.

       

      The issue appears to be with the nested SELECT clause. If I remove it so the query's WHERE clause becomes

       

      WHERE

         CA.AttemptedDownloadTime < ADDDAY(-1, GETDATE())

       

      then Report Writer previews the not-so-useful results.

       

      I am stumped as to whether this will work at all in ReportWriter. Any assistance in getting Report Writer to accept the supposedly valid SWQL query would be appreciated. If you have some other way to pull the date and time of only the most recently downloaded config besides the Report Writer-offending subquery, I'm all ears.

        • Re: Report Writer Cannot Handle FROM Clause
          ctlswadmin

          This should work if ran from Report Writer. Also, I replied to your previous post mentioned at the beginning of this post.

           

           

          SELECT

            N.NodeID AS Node_UniqueID, N.CoreNodeID AS Node_ID, N.AgentIP AS IP_Address, N.NodeCaption AS "Node_Name",

            CA.ConfigType AS Config_Type, CA.DownloadTime AS Download_Time, CA.AttemptedDownloadTIme AS Last_Successful_Download_Time,

            StatusName as "Status"

          FROM

            NCM_Nodes N INNER JOIN NCM_ConfigArchive CA ON N.NodeID = CA.NodeID

            INNER JOIN StatusInfo SI ON N.Status = StatusID

          WHERE

            (CA.ConfigID = (SELECT TOP 1 CAA.ConfigID

               FROM NCM_ConfigArchive CAA

               WHERE CAA.NodeID = N.NodeID

               ORDER BY CAA.DownloadTime DESC)

             AND CA.AttemptedDownloadTime < DATEADD(DAY, -1, GETDATE()))

          ORDER BY CA.AttemptedDownloadTime

            • Re: Report Writer Cannot Handle FROM Clause
              mjperkins

              This form of the query fails in both Report Writer ("Query is not valid," like other queries I've tried) and in SWQL Studio.

               

              Upon executing this query, SWQL Studio returns errors in the form, "Source entity [NCM_Nodes | NCM_ConfigArchive | StatusInfo]  not found in catalog." After correcting the source entity (to [NCM.Nodes | Cirrus.ConfigArchive | Orion.StatusInfo]), and running again, the error occurs again with the first uncorrected source entity encountered.

               

              I am at a loss as to why our schemas seem to be so different.

                • Re: Report Writer Cannot Handle FROM Clause
                  ctlswadmin

                  I provided sql syntax, won't work in SWQL studio. It has to be ran on either db manager or sql.

                   

                  Try changing this ADDDAY(-1, GETDATE()) to this: DATEADD(DAY, -1, GETDATE())). If that doesn't work remove a ) from GETDATE())

                    • Re: Report Writer Cannot Handle FROM Clause
                      mesverrum

                      The easiest way to tell if someone is giving you SWQL or SQL is that the table names in SWQL almost always have "orion." something in them but you would never see that in SQL.

                       

                      It is a pretty commonly seen issue for the people who do a lot of custom SWQL that the Custom Table/Report validator for SWQL has some bugs with how it deals with certain more complex functions.  Sometimes you can figure a way around it by changing the form of your query and getting rid of things like subselections and certain types of table joins.  On the other hand it has no such problems with real SQL and, so far as I have been able to tell, anything that would execute in SMSS seems to run fine as a Custom Table/Report, even some stuff I have done that seems really risky haha.

                       

                      You can use the custom query resource, which is much less picky and more closely matches what you see in SWQL Studio (minus the bug where some queries won't show up on the web interface if you don't give them an ORDER BY).  On the plus side, custom query allows you to embed searches directly into the report and is more comfortable with complexity, the downside is that you can't export to excel if your report contains any resources that aren't the custom table resource.  Always trade offs in finding the best solution I suppose.

                        • Re: Report Writer Cannot Handle FROM Clause
                          mjperkins

                          mesverrum You were correct that the query ran in a Custom Query resource with no modification, just as it did in SWQL Studio. Unfortunately, a resource is not the way to deliver this to the customer asking for it. The customer works with nearly every SolarWinds report he uses by exporting the data to Excel and viewing/processing it further there.

                           

                          ctlswadmin Before I posted the first thread in August, I fir tried writing he query in SQL. While I am no DBA, I have experience in SQL and even a bit of PL/SQL, so preferred it. The SQL query I wrote, however, would not run in Database Manager. That is why I first turned to SWQL: the query (rewritten to SWQL) ran.

                           

                          In any case, I did try both the ADDDAY and DATEADD versions of the line. Both ways ran fine in SWQL Studio but not in Report Writer.

                           

                          With what we have checked here, with the Custom Query running the query and Report Writer not, plus trying multiple other tweaks to the query, it is pretty clear that

                             (a) Report Writer and its 'peculiarities' are causing the issue, and

                             (b) The subquery is the part with which Report Writer disagrees.

                           

                          I am still trying to figure out how to get this to run as a report, since the request on my plate is for a report.

                           

                          Now to think up some other ideas ...

                            • Re: Report Writer Cannot Handle FROM Clause
                              mesverrum

                              Since I have some time to spare today I stripped out the parts from my query that prevented you from being able to use it in excel.  Populate your datasource like so:

                               

                               

                              So the key thing is that you generally can't do any type of data aggregation in the query itself when using custom tables, you have to feed the full data set into the datasource then do the aggregation using the GUI.  This tends not to work for certain types of complex queries but if all you need is a single min/max/average/count then you can get it to work easily enough and still retain the ability to export to excel.

                                • Re: Report Writer Cannot Handle FROM Clause
                                  mjperkins

                                  Nice idea. I played with it for a while, but have yet to get it working.

                                  Even setting the "Data aggregation" field to "Max", the report still displays all records in the datasource -- in my case, all configs in Cirrus.ConfigArchive for nodes currently managed by NCM downloaded more than 24 hours ago.

                                  I still need to return only the most recent config for each node; that's what the subquery that so offended Report Writer was doing.

                                   

                                  I have even started looking for a different strategy/query design: querying NCM Jobs (Events?) for everything:

                                     A. within the last 24 hours,

                                     B. job type is config backup, and

                                     C. job failed

                                   

                                  but I have not been able to query either the "NCM_NCMJobs" (SQL) or "Cirrus.NCM_NCMJobs" (SWQL) tables. I'm going to open a support case to get help figuring out why.