Good afternoon Thwack Community,
I am having trouble with a KPI widget that I am trying to repurpose in another KPI widget. The KPI will show the desired value but for some reason I can't get it to link to the Table Widget like the original KPI does. This is the query I am trying to work with
SELECT N.TheCount
, CASE WHEN D.Link is null then NULL
ELSE CONCAT(D.Link,'?filters=', d.InstanceSiteId, '_Orion.Nodes_Status:eq:2')
END AS Link
FROM
(SELECT COUNT(1) as TheCount
FROM Orion.Nodes
WHERE Category=1
AND Status = 2
AND Community IN ('blahblah', 'blahblah', 'blahblah')
) N
LEFT JOIN
(
SELECT TOP 1 InstanceSiteId, '/apps/platform/dashboard/' + TOSTRING(DashboardID) as Link
FROM Orion.Dashboards.Instances
WHERE DisplayName='Networks Summary - Node Status'
ORDER BY DashboardID
) D
ON 1=1;
The original KPI functions as expected it shows the widget with a number and when you click in the KPI it will take you to the Table Widget with more detailed information.
What I tried to do was create a new KPI Widget add one more filter the the WHERE clause (Partial match on Caption) so I could narrow the focus further and still have the KPI link to the same table. But after creating the new KPI Widget all that happens is the Widget shows the correct value but there is no link. The changed query is below:
SELECT N.TheCount
, CASE WHEN D.Link is null then NULL
ELSE CONCAT(D.Link,'?filters=', d.InstanceSiteId, '_Orion.Nodes_Status:eq:2')
END AS Link
FROM
(SELECT COUNT(1) as TheCount
FROM Orion.Nodes
WHERE Category=1
AND Status = 2
AND Caption LIKE 'fod%'
AND Community IN ('blahblah', 'blahblah', 'blahblah')
) N
LEFT JOIN
(
SELECT TOP 1 InstanceSiteId, '/apps/platform/dashboard/' + TOSTRING(DashboardID) as Link
FROM Orion.Dashboards.Instances
WHERE DisplayName='Networks Summary - Node Status'
ORDER BY DashboardID
) D
ON 1=1;
I have tried creating whole new Table Dashboards and all new KPI's but I am still unable to get the link to work. Does anyone have a suggestion or hint on what I can do to resolve this?
