Hi,
I'm trying to create a custom query for event log (which is based on custom alerts that queries trigger/reset actions from npm log)
So far the query is complete and is working, until I saw that the Volume event is missing / not being captured by the query. But I do see the Volume events appear in the NPM events/ Message Center.
I think that because is working only for event trigger condition "nodes", and the Volume alert/event's trigger condition is "Volume Capacity Forecasting", maybe that's why the query won't grab the volumes?
SELECT
TOLOCAL(ev.EventTime) AS [Event Time],
' ' AS [ ],
CASE
WHEN ev.Message LIKE '%is%up%' THEN '/Orion/Images/StatusIcons/Up.gif'
WHEN ev.Message LIKE '%NODE STABILIZED%' THEN '/Orion/Images/StatusIcons/Up.gif'
WHEN ev.Message LIKE '%NODE DOWN%' THEN '/Orion/Images/StatusIcons/Down1.gif'
WHEN ev.Message LIKE '%undefined%' THEN '/Orion/Images/StatusIcons/Dormant.gif'
WHEN ev.Message LIKE '%rebooted%' THEN '/Orion/Images/OrionServiceManager/Restart.png'
WHEN ev.Message LIKE '%became unavailable%' THEN '/Orion/Images/StatusIcons/Warning.gif'
WHEN ev.Message LIKE '%is unknown%' THEN '/Orion/Images/StatusIcons/Unknown.gif'
WHEN ev.Message LIKE '%unreachable%' THEN '/Orion/Images/StatusIcons/Unreachable.gif'
WHEN ev.Message LIKE '%index changed%' THEN '/Orion/Images/Info-Notification.gif'
WHEN ev.Message LIKE '%CPU%has%reached%' THEN '/Orion/Images/ForecastingIcons/trend.png'
WHEN ev.Message LIKE '%CPU%has%stabilized%' THEN '/Orion/Images/ForecastingIcons/trenddown.png'
WHEN ev.Message LIKE '%has been added%' THEN '/Orion/Images/Info-Notification.gif'
WHEN ev.Message LIKE '%Critical Volume Space%' THEN '/Orion/Images/StatusIcons/Critical.gif'
END AS [_IconFor_ ],
CASE
WHEN ev.Message LIKE '%is%up%' THEN (n.Caption + ' is up')
WHEN ev.Message LIKE '%NODE STABILIZED%' THEN (n.Caption + ' is up')
WHEN ev.Message LIKE '%NODE DOWN%' THEN (n.Caption + ' is down')
WHEN ev.Message LIKE '%Host Unreachable%' THEN ('Host ' + n.Caption + ' is ' + 'unreachable')
WHEN ev.Message LIKE '%Critical Volume Space%' THEN (' VOLUME ' )
WHEN vu.DisplayName = ev.Message THEN 'VOLUME'
ELSE ev.Message
END AS [Message]
FROM
Orion.Events ev
JOIN
Orion.Nodes n ON n.NodeID = ev.NetObjectID
INNER JOIN
Orion.NodesCustomProperties nc ON nc.NodeID = n.NodeID
-----I've created the following to test-----
LEFT JOIN
Orion.Volumes v ON v.VolumeID = ev.EventID
LEFT OUTER JOIN
Orion.VolumeUsageHistory vu ON vu.VolumeID = ev.EventID
LEFT OUTER JOIN
Orion.ForecastCapacity fc ON fc.InstanceId = v.VolumeID
-----SAME RESULT------
WHERE EventTime > ADDDAY(-7,GETUTCDATE())
AND ev.Message NOT LIKE '%Group%'
AND ev.Message NOT LIKE '%Agent%'
AND ev.Message NOT LIKE '%Hardware health monitoring%'
AND ev.Message NOT LIKE '%Request Timed Out%'
AND ev.Message NOT LIKE '%responding%'
ORDER BY ev.EventTime DESC
RESULT:
| 2019-02-13 13:56:04.69 | | /Orion/Images/ForecastingIcons/trenddown.png | has stabilized at 92 % |
| 2019-02-13 13:20:57.643 | | /Orion/Images/ForecastingIcons/trend.png | has reached 97 % |
| 2019-02-13 11:59:45.557 | | /Orion/Images/OrionServiceManager/Restart.png | rebooted at 2/13/2019 11:58:00 AM |
| 2019-02-13 11:32:54.05 | | /Orion/Images/OrionServiceManager/Restart.png | rebooted at 2/13/2019 11:32:00 AM |
| 2019-02-13 10:47:26.307 | | /Orion/Images/StatusIcons/Up.gif | is up |
| 2019-02-13 10:46:10.883 | | /Orion/Images/StatusIcons/Down1.gif | is down |
| 2019-02-13 10:45:10.303 | | /Orion/Images/StatusIcons/Up.gif | is up |
| 2019-02-13 10:44:38.177 | | /Orion/Images/OrionServiceManager/Restart.png | rebooted at 2/13/2019 10:43:00 AM |
| 2019-02-13 07:47:20.24 | | /Orion/Images/OrionServiceManager/Restart.png | rebooted at 13/2/2019 7:45:00 AM |
| 2019-02-13 03:38:44.78 | | /Orion/Images/OrionServiceManager/Restart.png | rebooted at 13/2/2019 3:37:00 AM |
| 2019-02-13 03:38:26.437 | | /Orion/Images/StatusIcons/Up.gif | is up |
| 2019-02-13 01:39:00.217 | | /Orion/Images/Info-Notification.gif | /vmfs/volumes/528549d9-5b1c0d9d-3c95-d48564c9910c- Volume index changed from 10 to 11 |
| 2019-02-13 01:37:09.387 | | /Orion/Images/Info-Notification.gif | /vmfs/volumes/528549d9-5b1c0d9d-3c95-d48564c9910c- Volume index changed from 11 to 10 |
| 2019-02-12 23:23:46.747 | | /Orion/Images/StatusIcons/Down1.gif | is down |
| 2019-02-12 23:06:49.997 | | /Orion/Images/ForecastingIcons/trenddown.png | has stabilized at 53 % |
| 2019-02-12 22:36:53.78 | | /Orion/Images/ForecastingIcons/trend.png | has reached 99 % |
