Schedule

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?

2) What are the alerts and how many are there?

3) How is planned downtime during scheduled work times handled?

4) Do you want to organize by customers or equipment or both?

5) What is the equipment and do you or the customer provide it?

Thanks in advance,
Steve

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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.

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.

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.

Thanks in advance,
Steve

Thanks for response.
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

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.

Thanks in advance,
Steve

Thanks for response.
Steve

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Jun 20, 2017 at 11:12 AM, Steve Clark <steve.clark@netwolves.com>
wrote:

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I would design it this way. See Attached.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 06/20/2017 08:12 AM, Steve Clark wrote:

On 06/20/2017 10:38 AM, Adrian Klaver wrote:

On 06/20/2017 07:00 AM, Steve Clark wrote:

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.

Yes this could be become complicated if for no other reason then it is
being driven from the customer end and there will need to be a process
to verify and incorporate their changes. I am going to wave my hand on
that part and proceed to the actual scheduling part. I have delved into
some of this for an yet unfinished timeclock/payroll project I peck away
at on occasion. Some thoughts from that project:

1) Settle on what a week is. ISO Monday(1) to Sunday(7) or non-ISO
Sunday(0) to Saturday(6)

2) Figure out what a day is. In other words are different timezones
involved and if so what do you 'anchor' a day to?

3) Figure out how to deal with schedules that cross Midnight. This might
come into play with holidays.

3) Create a parent table of default schedules. You could int4range as
day ranges. Assuming ISO week:

Weekend '[6, 7]'::int4range
Weekday '[1, 5]'::int4range

So in your monitoring determine day of week(dow) and look up schedule
for which dow falls in range.

4) Create child table to above that blocks out hours in schedule to
allow for lunch period, different start, stop hours.

5) Create holiday schedule that has date. When you determine dow in step
3) you will be using the date so you could look up to see it is a
holiday. This might tie into schedule table as I am not sure if there
are holiday schedules.

Thanks,
Steve

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 20/06/17, Steve Clark (steve.clark@netwolves.com) wrote:

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.

Assuming you have the ability to disable alerts for a
client/location/group/machine for a period of time, you could use
pg_cron schedules https://github.com/citusdata/pg_cron to enable and
disable this ability on a scheduled basis.

With some sort of front-end it would be trivial for the customer to
make their own schedules.

Rory

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 20 Jun 2017, at 18:46, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 06/20/2017 08:12 AM, Steve Clark wrote:

On 06/20/2017 10:38 AM, Adrian Klaver wrote:

On 06/20/2017 07:00 AM, Steve Clark wrote:

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.

Yes this could be become complicated if for no other reason then it is being driven from the customer end and there will need to be a process to verify and incorporate their changes.

There you're saying something rather important: "If it is being driven from the customer end".

2) Figure out what a day is. In other words are different timezones involved and if so what do you 'anchor' a day to?

For an example of how that might fail: At our company, they work in shifts (I don't) of 3*8 hours, that run from 23:00 to 23:00. Depending on who looks at the data, either that's a day or a normal day (00:00-00:00) is. It's a matter of perspective.

IMHO, the only safe approach is to have the customer end decide whether it's a regular outage or an irregular one. There is just no way to reliably guess that from the data. If a customer decides to turn off the system when he's going home, you can't guess when he's going to do that and you will be raising false positives when you depend on a schedule of when he might be going home.

From a software implementation point of view that means that your customer-side application needs to be able to signal planned shutdowns and startups. If you detect any outages without such a signal, then you can flag it as a problem.

There are still opportunities for getting those wrong of course, such as lack of connectivity between you and your customer, but those should be easy to explain once detected.
And I'm sure there are plenty of other corner-cases you need to take into account. I bet it has a lot of problems in common with replication actually (how do we reliably get information from system A to system B), so it probably pays to look at what particular problems occur there and how they're solved.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 06/20/2017 01:23 PM, Alban Hertroys wrote:

On 20 Jun 2017, at 18:46, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

Yes this could be become complicated if for no other reason then it is being driven from the customer end and there will need to be a process to verify and incorporate their changes.

There you're saying something rather important: "If it is being driven from the customer end".

Yeah, it is the interaction between technical issues and people issues.
One is easier to solve then the other:)

2) Figure out what a day is. In other words are different timezones involved and if so what do you 'anchor' a day to?

For an example of how that might fail: At our company, they work in shifts (I don't) of 3*8 hours, that run from 23:00 to 23:00. Depending on who looks at the data, either that's a day or a normal day (00:00-00:00) is. It's a matter of perspective.

I see that as part of how to 'anchor' a day. Right now Steve is looking
at one customer as I understand it. I would expect that might change so
I can envision a system that would need to account for different
definitions of a day. Still you have to start somewhere.

IMHO, the only safe approach is to have the customer end decide whether it's a regular outage or an irregular one. There is just no way to reliably guess that from the data. If a customer decides to turn off the system when he's going home, you can't guess when he's going to do that and you will be raising false positives when you depend on a schedule of when he might be going home.

From a software implementation point of view that means that your customer-side application needs to be able to signal planned shutdowns and startups. If you detect any outages without such a signal, then you can flag it as a problem.

I agree. I personally see false alerts as a form of 'Crying wolf' and I
think that down the road they lead to complacency. Hence my earlier
suggestion to have a method to indicate manual intervention on the
customer end.

There are still opportunities for getting those wrong of course, such as lack of connectivity between you and your customer, but those should be easy to explain once detected.
And I'm sure there are plenty of other corner-cases you need to take into account. I bet it has a lot of problems in common with replication actually (how do we reliably get information from system A to system B), so it probably pays to look at what particular problems occur there and how they're solved.

I would say a good deal of the above is going to be driven by legal
considerations. Who is responsible for what and what guarantees are in
effect.

Alban Hertroys

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general