I could not find exactly same issue answered, but please inform me if this has been already resolved.
I have need to build alerts on Node property, as Node table has custom properties which can be used for Trigger Actions.
Currently we are having SLA as custom property.
SLA coding is still under investigation but first plan is to have hours and week days coded in following format:
SLA starting Hour and Minutes in 4 first digits, SLA ending hour and Minutes in digits 5 to 8 and weekdays from start to end day
0900.1700.15 = SLA would be active 09:00 to 17:00 from monday(1) to friday (5)
I'm interested to create "Node Down" alert which will alert when Node is down and current time matches to node's SLA hours.
Practically this means advanced SQL query which first picks Nodes.SLA value and modify that for comparing current time.
In case of 0900.1700.15 SLA: if node goes down at 22:20, that would not trigger alert, but if node goes down on 13:45, that would trigger alert.
I would more than pleased if someone can give example code, thanks!
After extensive googling I found this out myself :
Let's assume that SLA code '1' is 24 hours active. '2' would be from 09 - 17.
I case of Node Down alert I could make selective alert based on SLA code:
(Nodes.Status = '2')
(Nodes.SLA = '1')
(Nodes.SLA = '2') AND
(CONVERT(TIME(0),GETDATE()) between '09:00:00' and '17:00:00')