2 Replies Latest reply on May 23, 2017 8:25 AM by i3scottw

    Difficulty creating a custom chart from Advanced Database Query

    i3scottw

      I have been attempting to create a custom chart that will show the total number of wireless clients connected to a Cisco WLC over time.   I have used ouberlord's very helpful post https://thwack.solarwinds.com/docs/DOC-171606 as a starting point, but can't quite get get the chart or report to display correctly.

       

      My problem is that I have not been able to get a simple 2 column set of data to correctly display as a custom chart.   I am using an advanced DataBase query to select the appropriate information (and the preview data shows it is selecting the appropriate data):

      SELECT

          Detail.[DateTime] AS Timestamp,

          SUM(Detail.[RawStatus]) AS 'Client Count'

      FROM

          [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail]

              AS Detail

          INNER JOIN [SolarWindsOrion].[dbo].[CustomPollerStatus]

              AS Status

              ON Left(Detail.[RowID], Len(Detail.[RowID]) - 2) = Status.[RowID]

      WHERE

          Detail.[CustomPollerAssignmentID] = '85F98CBF-FEF0-4A96-9E2B-A21C105B20DE'

      GROUP BY

          Detail.[DateTime]

       

      This query results in a nice table that includes one column with the timestamp and another column showing the total number of clients connected on the WLC at that time, for example:

      TimestampClient Count
      2017-05-22 16:12:06.220620
      2017-05-22 16:17:06.270642
      2017-05-22 16:22:09.003666
      2017-05-22 16:27:06.280674

       

      I have added "Client Count" as the data series for the Y Axis with Timestamp selected as the "Time Column".   At this point it requires "group chart data by" and "legend shows" values.    I have tried using Timestamp and Client Count in those fields, but it does not work correctly.   I feel like I am making this a lot harder than it needs to be.   I simply want to take a 2 column set of data and produce a line chart that can be shown on a dashboard.

       

      If anyone can provide some pointers for converting this SQL query into a line chart on a dashboard I would very much appreciate it!

       

      Scott

        • Re: Difficulty creating a custom chart from Advanced Database Query
          mesverrum

          They made a change in version 12 where those group by and legend shows fields became required instead of optional.  If you want to be lazy you can just hard code something to use for them both, like this:

           

          SELECT
              'WLC' as [Legend],
              Detail.[DateTime] AS Timestamp,
              SUM(Detail.[RawStatus]) AS 'Client Count'
          FROM
              [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail]
                  AS Detail
              INNER JOIN [SolarWindsOrion].[dbo].[CustomPollerStatus]
                  AS Status
                  ON Left(Detail.[RowID], Len(Detail.[RowID]) - 2) = Status.[RowID]
          WHERE
              Detail.[CustomPollerAssignmentID] = '85F98CBF-FEF0-4A96-9E2B-A21C105B20DE'
          GROUP BY
              Detail.[DateTime]
          

          Or to make it a bit more fancy you could join the assignment table to get the name of the actual poller and server, like so (may have syntax errors, my lab doesnt have a similar style of poller to test against right now):

             

            SELECT
              cpa.assignmentname as [Legend],
              Detail.[DateTime] AS Timestamp,
              SUM(Detail.[RawStatus]) AS 'Client Count'
          FROM
              [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail]
                  AS Detail
              INNER JOIN [SolarWindsOrion].[dbo].[CustomPollerStatus]
                  AS Status
                  ON Left(Detail.[RowID], Len(Detail.[RowID]) - 2) = Status.[RowID]
             JOIN [SolarWindsOrion].[dbo].[CustomPollerAssignment] cpa on cpa.custompollerassignmentid=Detail.custompollerassignmentid
          WHERE
              Detail.[CustomPollerAssignmentID] = '85F98CBF-FEF0-4A96-9E2B-A21C105B20DE'
          GROUP BY
              Detail.[DateTime], cpa.assignmentname
          
          1 of 1 people found this helpful