Good Evening,
I'd like to customize a dashboard having different owners along with a catchall group. I am using a right join for the StatusInfo table. That way, when there is no value for a specific status, I will have the following StatusNames: Up, Down, Warning & Unmanaged.
We would like to filter any nodes from the count, if it was acknowledged. Using a left join for the AlertStatusview table, we accomplish this goal. The issue is when there is a mix of nodes having both acknowledged & not acknowledged, we are seeing duplicates for "Down" nodes. The reason is due to the grouping.
When grouping, it brings back both the acknowledged Nodes & Nodes having an acknowledged is NULL. If we filter the query to remove any acknowledge is NULL then we lose the ability to provide a count of Nodes not in the AlertStatusview table. For example, this affects the "Up" nodes.
Code to ignore down alerts that have been acknowledged. TLDR- How can we modify this query to ignore from the count of down nodes that are acknowledged.
SELECT
case
WHEN s.StatusName = 'Up' THEN COUNT(n.STATUS)
WHEN s.StatusName = 'Down' AND asv.Acknowledged = 0 THEN COUNT(n.STATUS)
WHEN s.StatusName = 'Warning' AND asv.Acknowledged = 0 THEN COUNT(n.STATUS)
WHEN s.StatusName = 'Unmanaged' THEN COUNT(n.STATUS)
ELSE 0
END AS Cnt
, s.StatusName
FROM Nodes n
RIGHT JOIN StatusInfo s ON(n.STATUS = s.StatusID and (N._systems_owner not in ('Teams to ignore') OR N._systems_owner IS NULL))
LEFT JOIN AlertStatusview asv on n.NodeID = asv.ActiveObject AND AlertDefID = 'bb3c9cff-f994-472f-879a-cf6171093891'
--LEFT JOIN AlertStatus asv on n.NodeID = asv.ActiveObject AND AlertDefID = 'bb3c9cff-f994-472f-879a-cf6171093891'
WHERE s.StatusID IN(1, 2, 3, 9)
GROUP BY n.STATUS,
s.StatusName,
s.StatusId
, asv.Acknowledged
ORDER BY s.statusID ASC;
We did get this working with a CTE but still dont have a solution since you cant use this in swsql.
--Pulls Nodes with status & ack value
with cte_NodeAckd(NodeID, StatusID, Acknowledged)
as
(
SELECT
n.NodeID
, n.STATUS AS StatusID
, isnull(asv.Acknowledged,0) AS Acknowledged
FROM Nodes n
LEFT JOIN AlertStatusview asv on n.NodeID = asv.ActiveObject AND AlertDefID = 'bb3c9cff-f994-472f-879a-cf6171093891'
where N._systems_owner = 'XXSOME TEAMXX '
), cte_AlertStatusCount(StatusID, Cnt, StatusName)
as
(
--Seed the data set with defaults for Up, Down, Warning & Unmanaged
SELECT 1 AS StatusID, 0 AS [Cnt], 'Up' AS StatusName UNION
SELECT 2 AS StatusID, 0 AS [Cnt], 'Down' AS StatusName UNION
SELECT 3 AS StatusID, 0 AS [Cnt], 'Warning' AS StatusName UNION
SELECT 9 AS StatusID, 0 AS [Cnt], 'Unmanaged' AS StatusName UNION
SELECT
s.StatusID
,CASE --If there's a Node having a NULL value, this is seeded from above & Count = 0
WHEN n.NodeID IS NOT NULL
THEN COUNT(1) OVER(Partition BY s.StatusID, n.Acknowledged ORDER BY s.StatusID)
ELSE 0
end AS [Cnt]
, s.StatusName
FROM cte_NodeAckd n
RIGHT OUTER JOIN StatusInfo s ON n.StatusID = s.StatusID
WHERE s.StatusID IN(1, 2, 3, 9)
AND ISNULL(n.Acknowledged,0) = 0
), CTE_ResultSet(Cnt, StatusName, StatusID, [Duplicate])
AS
(
select
Cnt
, StatusName
, StatusID
, ROW_NUMBER() OVER(PARTITION BY StatusID ORDER BY Cnt DESC) AS [Duplicate]
FROM cte_AlertStatusCount)SELECT
Cnt
, StatusName
FROM CTE_ResultSet
WHERE [Duplicate] = 1
GROUP BY Cnt
, StatusName
, StatusID
ORDER BY StatusID ASC;
