-
Notifications
You must be signed in to change notification settings - Fork 2
/
CustomizedActiveAlerts.CustomQueryWidget.swql
110 lines (110 loc) · 5.74 KB
/
CustomizedActiveAlerts.CustomQueryWidget.swql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
-- Tested on Orion Platform 2020.2.1 and 2019.4 HF5
-- With input from Marc Netterfield (Mesverrum) [https://github.com/Mesverrum] and Holger Mundt
--
--
--
--
SELECT DISTINCT [ActiveAlerts].TriggeredMessage AS [Alert]
,CASE [ActiveAlerts].AlertObjects.AlertConfigurations.Severity
WHEN 0
THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
WHEN 1
THEN '/Orion/images/ActiveAlerts/Warning.png'
WHEN 2
THEN '/Orion/images/ActiveAlerts/Critical.png'
WHEN 3
THEN '/Orion/images/ActiveAlerts/Serious.png'
WHEN 4
THEN '/Orion/images/ActiveAlerts/Notice.png'
ELSE '/Orion/images/StatusIcons/EmptyIcon.gif' -- we should never get here
END AS [_IconFor_Alert]
,CONCAT (
'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'
,[ActiveAlerts].AlertObjectID
) AS [_LinkFor_Alert]
-- We can connect to related tables by referencing them. Linked tables are show in SWQL Studio with a chain icon.
-- Here we are chaining base table (ActiveAlerts) to the AlertObjects and selecting additional fields there
,[ActiveAlerts].AlertObjects.TriggeredCount AS [Count]
,[ActiveAlerts].TriggeredDateTime AS [Date/Time]
,CASE
WHEN FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 86400) > 0
THEN TOSTRING(TOSTRING(FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 86400)) + 'd ' + TOSTRING(FLOOR(((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) - 86400 * (FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 86400))) + 0.0) / 3600)) + 'h ' + TOSTRING(FLOOR(((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) - 3600 * (FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
WHEN FLOOR(((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) - 86400 * (FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 86400))) + 0.0) / 3600) > 0
THEN TOSTRING(TOSTRING(FLOOR(((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) - 86400 * (FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 86400))) + 0.0) / 3600)) + 'h ' + TOSTRING(FLOOR(((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) - 3600 * (FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
WHEN FLOOR(((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) - 3600 * (FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 3600))) + 0.0) / 60) > 0
THEN TOSTRING(TOSTRING(FLOOR(((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) - 3600 * (FLOOR((SECONDDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) + 0.0) / 3600))) + 0.0) / 60)) + 'm ')
ELSE ''
END AS [Time Active]
,[ActiveAlerts].AlertObjects.AlertNote AS [Alert Note]
,CASE
WHEN [ActiveAlerts].Acknowledged IS NULL
THEN '[Acknowledge Now]'
ELSE CONCAT (
'By: '
,[ActiveAlerts].AcknowledgedBy
,' at '
,[ActiveAlerts].AcknowledgedDateTime
,' / Note: '
,[ActiveAlerts].AcknowledgedNote
)
END AS [Ack. Details]
,CASE
WHEN [ActiveAlerts].Acknowledged IS NULL
THEN '/Orion/images/StatusIcons/Small-EmptyIcon.gif'
ELSE '/Orion/images/ActiveAlerts/Acknowliedged_icon16x16v1.png'
END AS [_IconFor_Ack. Details]
,CASE
WHEN [ActiveAlerts].Acknowledged IS NULL
THEN CONCAT (
'/Orion/Netperfmon/AckAlert.aspx?AlertDefID='
,[ActiveAlerts].AlertObjectID
)
ELSE CONCAT (
'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'
,[ActiveAlerts].AlertObjectID
)
END AS [_LinkFor_Ack. Details]
,[ActiveAlerts].AlertObjects.EntityCaption AS [Object]
,[ActiveAlerts].AlertObjects.EntityDetailsUrl AS [_LinkFor_Object]
-- This part is some magical mystery as far as I'm concerned. I looked at the icons on the actual All Active Alerts page and looked at how the URL was formatted
-- The StatusIcon.ashx takes (for our purposes) four (4) parameters: Entity, EntityUri, Size (always 'small'), and Timestamp
-- Then I just joined them all together to get the icon.
-- The timestamp was the interesting thing because it's in epoch time, which is the number of seconds after 01/01/1970
,CONCAT (
'/Orion/StatusIcon.ashx?entity='
,[ActiveAlerts].AlertObjects.EntityType
,'&EntityUri='
,[ActiveAlerts].AlertObjects.EntityUri
,'&size=small×tamp='
,SecondDiff('01/01/1970', [ActiveAlerts].TriggeredDateTime)
) AS [_IconFor_Object]
-- Bring in the related node if it exists
,CASE
WHEN [ActiveAlerts].AlertObjects.RelatedNodeUri <> [ActiveAlerts].AlertObjects.EntityUri
THEN [ActiveAlerts].AlertObjects.RelatedNodeCaption
ELSE NULL
END AS [On Node]
,CASE
WHEN [ActiveAlerts].AlertObjects.RelatedNodeUri <> [ActiveAlerts].AlertObjects.EntityUri
THEN [ActiveAlerts].AlertObjects.RelatedNodeDetailsUrl
ELSE NULL
END AS [_LinkFor_On Node]
,CASE
WHEN [ActiveAlerts].AlertObjects.RelatedNodeUri <> [ActiveAlerts].AlertObjects.EntityUri
THEN CONCAT (
'/Orion/StatusIcon.ashx?entity='
,'Orion.Nodes'
,'&EntityUri='
,[ActiveAlerts].AlertObjects.RelatedNodeUri
,'&size=small×tamp='
,SecondDiff('01/01/1970', [ActiveAlerts].TriggeredDateTime)
)
ELSE '/Orion/images/StatusIcons/Small-EmptyIcon.gif'
END AS [_IconFor_On Node]
,(FLOOR(MINUTEDIFF([ActiveAlerts].TriggeredDateTime, GETUTCDATE()) / 60.0)) AS [Active Hours]
FROM Orion.AlertActive AS [ActiveAlerts]
-- We are also chaining here to get the AlertHistory event type /though/ the first chain of AlertObjects
-- The logic is this: "[ActiveAlerts].AlertObjects.AlertHistory.<Field Name>"
-- We are querying the Active Alerts, which is connected to AlertObjects, which is connected to AlertHistory
WHERE [ActiveAlerts].AlertObjects.AlertHistory.EventType IN (2, 3)
ORDER BY [ActiveAlerts].TriggeredDateTime DESC