Re: [GENERAL] Schedule - Mailing list pgsql-general

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