This was a query I built some time ago but I'm posting it here now for others to use. I'm using the SDK forum since this would be for advanced users who understand SWQL and SWQL alerts.
The purpose of this was to create an alert that could be triggered on a specific schedule. This was mainly for setting nodes into a recurring maintenance mode. This is only the first part of triggering the alert. The second part, an alert action of executing an external program, to kick off a script that calls into the Orion/SolarWinds Platform API to unmanage/mute alert on a node won't be covered in this discussion.
While I've posted the full code below in this thread, any updates that I make to it over time will only be updated on my Github site.
solarwinds/SWQL/Alert_Trigger-Alert-On-Specific-Schedule.swql at master · everychad/solarwinds · GitHub
-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.
-- URL:
-- Description: The purpose of this was to create an alert that could be triggered
-- on a specific schedule. This was mainly for setting nodes into a recurring
-- maintenance mode. This is only the first part of triggering the alert. The second
-- part, an alert action of executing an external program, to kick off a script that
-- calls into the Orion/SolarWinds Platform API to unmanage/mute alert on a node
-- won't be covered in this discussion.
--
-- Directions:
-- 1. Create a a new alert.
-- 2. In the Alert Trigger conditions section, update the dropdown called 'I want
-- to alert on:' to 'Custom SWQL Alert (Advanced)'. p.s. it is at the bottom
-- of the list.
-- 3. In the dropdown called 'Set up your SWQL condition', select 'Node'.
-- 4. Past the query below into the open text box.
-- 5. Update the last few lines to match the nodes that you want to include in this alert.
-------------------------------------
-- THIS IS STILL A WORK IN PROGESS --
-------------------------------------
--SELECT
-- NodeID
-- ,Caption
-- ,DOWNSAMPLE(GETDATE(),'00:00:01') AS [DATE Current]
-- ,DAY(GETDATE()) AS [DATE Day]
-- ,WEEKDAY(GETDATE()) AS [DATE Weekday]
-- ,HOUR(GETDATE()) AS [DATE Hour]
-- ,MINUTE(GETDATE()) AS [DATE Minute]
--FROM Orion.Nodes nodes
-----------------------------------------------------------------------------------------------
-- Copy everything below this line into a custom an Advanced SWQL Alert, entity: Nodes --
-----------------------------------------------------------------------------------------------
WHERE 1=1
--Find the nth week of each month
--AND DAY(GETDATE()) IN (1,2,3,4,5,6,7) --Days that the 1st week of each month could be
--AND DAY(GETDATE()) IN (8,9,10,11,12,13,14) --Days that the 2nd week of each month could be
AND DAY(GETDATE()) IN (15,16,17,18,19,20,21) --Days that the 3rd week of each month could be
--AND DAY(GETDATE()) IN (22,23,24,25,26,27,28) --Days that the 4th week of each month could be
--Uncomment multiple lines above if it needs to be a specific pattern (e.g. 1st and 3rd week)
--Comment out ALL lines above if it needs to be every week
--Set the day of the WEEK
--0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday
AND WEEKDAY(GETDATE()) = 3
--AND WEEKDAY(GETDATE()) IN (2,4) --Twice weekly on Tuesdays and Thursdays
--Start at 10:00pm at night
AND HOUR(GETDATE()) = 22 --Start at 10pm (24hr format)
AND MINUTE(GETDATE()) = 0 --Start at 00 minutes into the hour
--With the example statements above, it would trigger:
----on the 3rd week of each month
----on Wednesday
----At 10:00pm (22:00)
-------------------------------------------------------------------------------------------------
-- You also need to set what specific nodes you want to include. Without setting this, it will --
-- trigger for ALL nodes. --
-------------------------------------------------------------------------------------------------
--Set info specific to the nodes you want to include
AND nodes.Caption LIKE 'XYZ%' --Starts with XYZ
--AND nodes.Caption LIKE '%XYZ%' --Contains XYZ
--AND nodes.IP_Address LIKE '10.0.0.%' --Node's IP Address starts with
--AND nodes.NodeID IN (1,2,3,4) --NodeID match the IDs in the array
--AND nodes.CustomProperties.City = 'name' --Replace .City with name of Custom Property and 'name' with value of Custom Property
For users that want to finish the second part, triggering an alert action, you would build a script that passes in the NodeID, maintenance start time, and maintenance end time into the Orion Nodes Unmanage verb. I know there are a handful of examples out there.
Happy customizing!
