I have this custom swql query that will populate any sites that are down so that anyone can acknowledge and let managers and coworkers will know if anyone is actively working on it. It also give custom note capability so you can make a note like area outages, site waiting for replacement hardware etc. I would like to add 2 more columns to display city and state and need help joining it from another table.
-Custom SWQL query:
SELECT OA.AlertConfigurations.Name AS [Name]
,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + TOSTRING(OA.AlertObjectID) AS [Name_URL]
,OA.AlertConfigurations.Severity AS [Name_Severity]
,OA.EntityCaption AS [Node]
,OA.Node.Status AS [Object_Status]
,OA.AlertNote as [Note]
,OA.EntityDetailsUrl AS [Object_URL]
,CASE
WHEN OA.RelatedNodeCaption = EntityCaption
THEN '[NODE NAME]'
ELSE OA.RelatedNodeCaption
END AS [Related Node]
,OA.RelatedNodeDetailsURL AS [Related Node_URL]
,OA.Node.Status AS [Related Node_Status]
,TOSTRING(TOLOCAL(OA.AlertActive.TriggeredDateTime)) AS [Time]
,TOLOCAL(OA.AlertActive.TriggeredDateTime) AS [Time_SORT]
,CASE
WHEN MINUTEDIFF(OA.AlertActive.TriggeredDateTime, GETUTCDATE()) > 1440 -- More than 1 Day (1440 minutes)
THEN (TOSTRING(ROUND(MINUTEDIFF(OA.AlertActive.TriggeredDateTime, GETUTCDATE()) / 1440.0, 1)) + ' Days')
WHEN MINUTEDIFF(OA.AlertActive.TriggeredDateTime, GETUTCDATE()) > 60 -- More than 1 Hour (60 minutes)
THEN (TOSTRING(ROUND(MINUTEDIFF(OA.AlertActive.TriggeredDateTime, GETUTCDATE()) / 60.0, 1)) + ' Hours')
ELSE (TOSTRING(MINUTEDIFF(OA.AlertActive.TriggeredDateTime, GETUTCDATE())) + ' Minutes')
END AS [Active]
, CASE
WHEN OA.AlertActive.Acknowledged IS NULL
THEN 'Unacknowledged'
ELSE CONCAT(OA.AlertActive.AcknowledgedBy, ' (', TOLOCAL(OA.AlertActive.AcknowledgedDateTime), ')')
END AS [Acknowledge]
, CASE
WHEN OA.AlertActive.Acknowledged IS NULL
THEN CONCAT('/Orion/NetPerfMon/AckAlert.aspx?AlertDefID=', OA.AlertObjectID)
ELSE CONCAT('/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:', OA.AlertObjectID)
END AS [Acknowledge_URL]
FROM Orion.AlertObjects AS OA
WHERE OA.AlertActive.AlertActiveID > 0
AND OA.AlertConfigurations.Name Like 'US Store Node is down AZ'
AND OA.Node.CustomProperties.Department = 'StoreNetworks'
GROUP BY OA.AlertActive.AlertActiveID
-Two columns I would like to add this table:
SELECT cp.City, cp.State FROM Orion.NodesCustomProperties as cp
Thank you in advance