SWQL query works in SWQL studio but not in UI

maan_syd

I am trying to get data based on a custom node poller. Below is my SWQL query. This works fine in swql studio. But when I add it to a modern dashboard in a table widget, it returns RunQuery failed - cannot resolve property rowid_a. Please help

SELECT a.rowid AS rowid_a, b.rowid AS rowid_b,
a.description As description_in_node_a,
b.description AS description_in_node_b,
a.id AS id_a, b.id AS id_b,
a.caption AS caption_a, b.caption AS caption_b,
a.operstatus AS operstatus_a, b.operstatus AS operstatus_b,
a.uptime AS uptime_a, b.uptime AS uptime_b
FROM (
SELECT
cp.CustomPollerStatus.RowID as rowid,
cp.Node.Caption as caption,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcDescr'
THEN cp.CustomPollerStatus.Status
END) AS description,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcOperStatus'
THEN cp.CustomPollerStatus.Status
END) AS operstatus,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcID'
THEN cp.CustomPollerStatus.Status
END) as id,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcUpTime'
THEN cp.CustomPollerStatus.Status
END) as uptime
FROM Orion.NPM.CustomPollerAssignmentOnNode as cp
WHERE cp.AssignmentName LIKE 'cpwVc%'
GROUP BY
cp.CustomPollerStatus.RowID, cp.Node.Caption
) a
JOIN (
SELECT
cp.CustomPollerStatus.RowID as rowid,
cp.Node.Caption as caption,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcDescr'
THEN cp.CustomPollerStatus.Status
END) AS description,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcOperStatus'
THEN cp.CustomPollerStatus.Status
END) AS operstatus,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcID'
THEN cp.CustomPollerStatus.Status
END) as id,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcUpTime'
THEN cp.CustomPollerStatus.Status
END) as uptime
FROM Orion.NPM.CustomPollerAssignmentOnNode as cp
WHERE cp.AssignmentName LIKE 'cpwVc%'
GROUP BY
cp.CustomPollerStatus.RowID, cp.Node.Caption
) b
ON
a.id = b.id

cscoengineer

I simplified the query and it's working for me.  Your dataset might not be matching up

One more thing - SWQL studio will work without an order.  When you move the code to resource, you generally need an order statement at the end.