Re: [GENERAL] Schedule

[Adrian Klaver]

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



On 20 Jun 2017, at 18:46, Adrian Klaver  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.kla...@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

Re: [GENERAL] Schedule

[Alban Hertroys]

> On 20 Jun 2017, at 18:46, Adrian Klaver  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

Re: [GENERAL] Schedule

[Rory Campbell-Lange]

On 20/06/17, Steve Clark (steve.cl...@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

Re: [GENERAL] Schedule

[Adrian Klaver]

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.kla...@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

Re: [GENERAL] Schedule

[Melvin Davidson]

On Tue, Jun 20, 2017 at 11: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:
> >> 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.

TABLES
alert_codes 
(
alert_type_cd varchar(5) NOT NULL,
alert_description varchar(50),
CONSTRAINT alerts_pk PRIMARY KEY (alert_type_cd)
);

customers
(
cust_id serial NOT NULL,
cust_name varchar(30),
CONSTRAINT customers_pk PRIMARY KEY(cust_id)
);

locations
(
cust_id bigint NOT NULL,
location_id serial NOT NULL,

Re: [GENERAL] Schedule

[Steve Clark]

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

Re: [GENERAL] Schedule

[Adrian Klaver]

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.kla...@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

Re: [GENERAL] Schedule

[Steve Clark]

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

Re: [GENERAL] Schedule

[Adrian Klaver]

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.kla...@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