3 Replies Latest reply on Jul 12, 2016 1:07 AM by mesverrum

    Broken SQL report since upgrade

    Anthony Ussery

      I've encountered an issue since upgrading to NPM 12 where charts based on a SQL query have been broken.

      Here's the query (specifics redacted):

       

      SELECT a.[FACILITY]

             , SUM(a.[metric]) AS Actions

             , a.[endtime]

          FROM [database].[dbo].[table] a

          WHERE a.[endtime] BETWEEN DATEADD(dd,-30,GETDATE()) AND GETDATE()

           AND a.[FACILITY]  in ('FAC1','FAC2','FAC3')

      GROUP BY a.[FACILITY]

             , a.[endtime]

      ORDER BY a.[endtime]

             , a.[FACILITY];

       

      It used to show up fine as a stacked area chart, but now it just says "Data is not available".

      I have Actions as my data series, endtime as my time column, FACILITY on "Group chart data by:", and Actions on "Legend shows:"

      Sample interval is every hour.  I've tinkered with switching around different settings, but I can't seem to fix the issue.

      Thanks for any help.

        • Re: Broken SQL report since upgrade
          I LIKE EGGS

          lol not sure how much help ill be here i don't have SQL db to hand and iv not encountered this problem off to a good start but ill try my best to help. what id do in your case is rebuild the statement and see what it is getting caught but firstly can you run the query in your db? and can you query the table a with no other conditions where and selects

           

          Select *

          FROM table a 

           

          see if i can actually get records based on the select statement then simply add in the other factors also maybe remove the brackets []

          • Re: Broken SQL report since upgrade
            mesverrum

            I had to rework a few of my own sql based charts as well, you are correct that the problem is not that the query broke it's that they made some changes to how the custom chart resource works and the sticking point seems to be those two data grouping options that used to be optional and are now required.  After mucking with it for a while I ended up just hardcoding in a column to feed it for the "legend shows" value because the data set i was working with didnt need to be grouped up into multiple lines.  Once I had that in place it all started rendering correctly.  It would seem to me that for your chart you should be using Facility for both the group by and legend shows options, so it will group them by the facility and also show those FAC1/2/3 values as the caption at the bottom of the chart for each line.  Since your query itself is so heavily redacted I can't load it up on my server to play with it but hopefully that gives you something to work with.

             

            -Marc Netterfield

                Loop1 Systems: SolarWinds Training and Professional Services