Hello,
I have little to no experience with SQL and SWQL and have been messing around in SWQL to create custom queries for our Dashboards. The current one I am working on is this Active Alerts one I got here on thwack awhile ago. After doing some edits to the original I just am trying to try to get the Alert Object column to function like the one on the All Active Alerts Page. ie "Object"<URL> on "Node"<URL> I have only been able to get one URL to work. ie "Object on Node"<URL> Here is the query below:
SELECT
o.AlertConfigurations.Name AS [ALERT NAME],
'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME],
CASE
WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'
WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'
WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'
END AS [_iconfor_ALERT NAME],
CASE
WHEN o.RelatedNodeCaption = EntityCaption THEN o.EntityCaption
WHEN o.RelatedNodeCaption != EntityCaption THEN CONCAT(o.EntityCaption, ' on ', o.RelatedNodeCaption)
END AS [ALERT OBJECT],
o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT],
'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_ALERT OBJECT],
TOLOCAL(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME],
CASE
WHEN MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE()) > 1440
THEN (TOSTRING(DAYDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + 'd '
+ TOSTRING(HOURDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%24) + 'h '
+ TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%60) + 'm')
WHEN MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE()) > 60
THEN (TOSTRING(HOURDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%24) + 'h '
+ TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%60) + 'm')
ELSE (TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + 'm')
END AS [Time Active],
CASE
WHEN aa.Acknowledged = TRUE
THEN aa.AcknowledgedBy
Else 'Click to Acknowledge'
END AS [Acknowledged By],
'/Orion/Netperfmon/AckAlert.aspx?AlertDefID=' + ToString(aa.AlertObjectID) AS [_Linkfor_Acknowledged By],
ah.Message as [Note]
FROM Orion.AlertActive AS aa
JOIN Orion.AlertObjects AS o ON aa.alertobjectid = o.alertobjectid
LEFT JOIN Orion.Nodes AS p ON p.nodeid = o.relatednodeid
LEFT JOIN Orion.alerthistory AS ah ON ah.AlertActiveID = aa.AlertActiveID AND ah.EventType IN (2,3)
LEFT JOIN Orion.AlertConfigurationsCustomProperties AS acp ON o.AlertID = acp.AlertID
WHERE acp.ResponsibleTeam = 'NOC'
ORDER by o.AlertActive.TriggeredDateTime DESC
