Issue with Custom Chart with SWQL+UnDP

Hello,

I am fighting with SWQL/SQL query for few hours now and I feel like I reached a dead end. Maybe you could point me in the right direction?

I need to combine UnDP statistics with its labels. I was able to do that with using Custom Poller ID and Row ID. Timestamps are working fine.

The query is working in SQL Management Studio (SQL) as well as in SWQL Studio (SWQL). It is even working on Custom Table, but for some reason, Custom Chart is displaying "Error".

For Custom Chart, I tried to use daydiff as well as ${FromTime}, but both did not resolve the issue.

This is NPM 12.5 (upgraded from fresh installation of NPM 12.4).

I even tried to include a dummy column in "Group chart data by", as suggested in one of the technical article, but this does not help.

SQL:

---------------------------

SELECT

cpsd.DateTime

,cpsd.RawStatus as [vSConns]

--cpsd.Status,

--,n.Caption

,labels.Label

FROM CustomPollerStatistics cpsd

JOIN CustomPollerAssignment cpa ON cpsd.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID

JOIN CustomPollers cp ON cpa.CustomPollerID = cp.CustomPollerID

JOIN Nodes n ON cpa.NodeID = n.NodeID

JOIN CustomPollerLabels labels ON

(cpsd.RowID = labels.RowID AND cpsd.CustomPollerAssignmentID = labels.CustomPollerAssignmentID)

WHERE cp.UniqueName = 'vSConns' --AND daydiff(cpsd.DateTime,getdate())<1 --and cpsd.DateTime > ${FromTime}

--GROUP BY cpsd.DateTime,cpsd.RawStatus,n.caption , cpsd.Status, labels.Label

GROUP BY cpsd.DateTime, cpsd.RawStatus, labels.Label --, n.Caption

ORDER BY cpsd.RawStatus desc

SWQL:

---------------------------

SELECT

cpsd.DateTime

,cpsd.RawStatus as [vSConns]

--cpsd.Status,

--n.Caption,

,labels.Label

FROM Orion.NPM.CustomPollerStatistics cpsd

JOIN Orion.NPM.CustomPollerAssignment cpa ON cpsd.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID

JOIN Orion.NPM.CustomPollers cp ON cpa.CustomPollerID = cp.CustomPollerID

--INNER JOIN Orion.[Nodes] n ON cpa.NodeID = n.NodeID

JOIN Orion.NPM.CustomPollerLabels labels ON

(cpsd.RowID = labels.RowID AND cpsd.CustomPollerAssignmentID = labels.CustomPollerAssignmentID)

WHERE cp.UniqueName = 'vSConns' AND daydiff(cpsd.DateTime,getdate())<1 --datetime < ${FromTime}

--GROUP BY cpsd.DateTime,cpsd.RawStatus,n.caption , cpsd.Status, labels.Label

GROUP BY cpsd.DateTime, cpsd.RawStatus, labels.Label

--ORDER BY n.caption

Screenshot:

From SolarWinds website:

From SWQL Studio:

Thanks,

Marcin.

Find more posts tagged with

swql query

custom charts

UnDP

Accepted answers

mesverrum

There is a bug in the current release that breaks all custom swql/sql based charts, your query is fine they just haven't released a hotfix to address the issue yet.

All comments

xaturbatu

Just use this SWQL and set the grouping and time on the chart.

Select Rawstatus for Left Y-Axis and press the more button near it to select the time column, then just select the group by and label culomn as Label and voila

SELECT

ObservationTimestamp

,RawStatus

,labels.Label

FROM Orion.NPM.CustomPollerStatistics cpsd

JOIN Orion.NPM.CustomPollerAssignment cpa ON cpsd.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID

JOIN Orion.NPM.CustomPollers cp ON cpa.CustomPollerID = cp.CustomPollerID

JOIN Orion.NPM.CustomPollerLabels labels ON

(cpsd.RowID = labels.RowID AND cpsd.CustomPollerAssignmentID = labels.CustomPollerAssignmentID)

WHERE cp.UniqueName = 'vSConns'

mesverrum

There is a bug in the current release that breaks all custom swql/sql based charts, your query is fine they just haven't released a hotfix to address the issue yet.

kpmarcin

Hello xaturbatu

Thanks for your reply and new query to test. I appreciate it.

Unfortunately the NPM 12.5 is still showing a simple one word "Error" message.

For now, I will need to use limited out-of-the box Universal Chart, without labels.

This is a pity, as vSConns is a parameter for Load Balancer connections and labels are used for different application names, such as Exchange or Skype.

I can show labels per device, but such Universal Chart does not exist for Dashboard with multiple devices. There is a "classic chart", but it has too old school look.

Kind regards,

Marcin.

kpmarcin

Hi mesverrum

Thanks a lot for this info! Now it all makes sense. I remember that few months ago similar query was working fine for me.

I hope SolarWinds will fix it soon.

Kind regards,

Marcin.

Seashore

This bug can't still be around can it? (We have Orion 2020.2.1)

I'm trying to make a chart out of below query and just gets "There was an error while loading data for this resource. error"

SELECT
C.Name + ' on ' + N.Caption AS [ComponentName]
,D.AvgNumericData AS [Latency]
,D.TimeStamp AS [Dattime]
FROM Orion.APM.Component C
INNER JOIN Orion.APM.DynamicEvidenceChart D on C.ComponentID=D.ComponentID
INNER JOIN Orion.APM.Application A ON C.ApplicationID=A.ApplicationID
INNER JOIN Orion.Nodes N ON A.NodeID=N.NodeID
WHERE
A.Name LIKE 'Maximo 7.6 - %'
AND C.Name LIKE 'Appserver%'
AND D.TimeStamp>${FromTime}
ORDER BY D.TimeStamp