On 06/20/2017 10:38 AM, Adrian Klaver wrote: > On 06/20/2017 07:00 AM, Steve Clark wrote: >> On 06/20/2017 09:02 AM, Adrian Klaver wrote: >>> On 06/20/2017 05:35 AM, Steve Clark wrote: >>>> Hello, >>>> >>>> We have customers whose equipment we monitor. Some of the customers don't run a 24/7 operation >>>> and turn their equipment off when the go home. We need to create a schedule for them of when we >>>> can ignore alerts from their equipment. We use postgresql in our monitoring environment to maintain >>>> alerts and equipment to be monitored. Each piece of equipment has a unique unit serial number so >>>> the schedule would be tied to this unit serial no. >>>> >>>> I would be very interested in what might be the best was to organize a scheduling table(s) in postgresql. >>> Some questions: >>> >>> 1) What happens if someone ignores the schedule and the alert is real? >> That is up in the air for now, probably if our NOC wasn't informed by the customer they >> were working outside of the schedule the alert would be ignored, but then the customer >> would probably call us because something wasn't working. > It might be just me, but looks like a finger pointing exercise in the > making. The classic '(Person 1)I thought you had it. (Person 2)No, I > thought you had it'. The whole idea of ignoring an alert makes me > nervous anyway. It seems that it should be possible to have the > equipment produce an manual off state and the monitoring to acknowledge > that. That being said, see more below. > >>> 2) What are the alerts and how many are there? >> Device not pingable, as an example. The alerts continue to be sent to our >> monitoring system, typically at 2 minute intervals, the monitoring system would look at the schedule for that >> unit a decide whether or not to ignore the alert. >>> 3) How is planned downtime during scheduled work times handled? >> They would get a call from our NOC if the unit was down during scheduled uptimes. > Could they not schedule a downtime? Yes that would certainly be an option. > >>> 4) Do you want to organize by customers or equipment or both? >> We have one piece of equipment at each customer that monitors one to many devices at the customer. > So when you where talking about unique serial numbers where you talking > about the monitoring equipment only or does that include the monitored > equipment? > >>> 5) What is the equipment and do you or the customer provide it? >> We provide the monitoring equipment, we or the customer could provide the equipment being monitored. > My first draft of an idea(I'm guessing some of this exists already): > > 1) Location/customer table. Not sure if a customer can have more then > one location. > > 2) Table of alerts and what they mean. > > 3) Schedule table keyed to location. > To make life a good simpler I would use range types for the schedule: > https://www.postgresql.org/docs/9.6/static/rangetypes.html > > Then you could use the range type operators and functions: > > https://www.postgresql.org/docs/9.6/static/functions-range.html#RANGE-OPERATORS-TABLE > > to verify whether an alert occurs in or out of the schedule. > > What I have not taken into account is whether a location has multiple > schedules e.g. weekday vs weekend. Then there is the holidays issue. Is > this something that needs to be dealt with? > > 4) Equipment table keyed to location. We already have a monitoring system in place that has been in operation circa 2003. Just recently we have added a new class of customer whose operation is not 24/7. I envision the schedule could be fairly complicated including WE and holidays, plus the enduser might shut down for lunch etc. I am looking for more on how to organize the schedule, EG a standard weekly schedule then exceptions for holidays etc, or a separate individual schedule for each week, also need to consider how easy it is to maintain the schedule, etc. Thanks, Steve -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
