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:

Timestamp	Client Count
2017-05-22 16:12:06.220	620
2017-05-22 16:17:06.270	642
2017-05-22 16:22:09.003	666
2017-05-22 16:27:06.280	674

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

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.custompollerassignmentidWHERE    Detail.[CustomPollerAssignmentID] = '85F98CBF-FEF0-4A96-9E2B-A21C105B20DE'GROUP BY    Detail.[DateTime], cpa.assignmentname