Re: Schedule

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Tue, Jun 20, 2017 at 11:12 AM, Steve Clark <steve.clark@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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,
	location_desc varchar(50) NOT NULL,
	location_city varchar(40) NOT NULL,
	CONSTRAINT locations_pk PRIMARY KEY (cust_id, location_id)
	);
	
	equipment
	(
	cust_id bigint NOT NULL,
	equipment_id varchar(20) NOT NULL,
	equipment_desc varchar(50),
	CONSTRAINT equipment_pk PRIMARY KEY (cust_id, equipment_id),
	CONSTRAINT fk_equip_cust FOREIGN KEY (cust_id)
		REFERENCES customers(cust_id),
	CONSTRAINT fk_equip_loc FOREIGN KEY (location_id)
		REFERENCES locations(location_id)
	);
	
	alerts
	(alert_id serial NOT NULL,
	 cust_id bigint NOT NULL,
	 location_id bigint NOT NULL,
	 equipment_id varchar(20) NOT NULL,
	 alert_type_cd varchar(5) NOT NULL,
	 alert_time timestamp NOT NULL,
	 CONSTRAINT alerts_pk PRIMARY KEY (alert_id),
	 CONSTRAINT fk_alerts_cust FOREIGN KEY (cust_id)
		REFERENCES customers(cust_id),
	 CONSTRAINT fk_equip_cust FOREIGN KEY (cust_id)
		REFERENCES customers(cust_id),
	 CONSTRAINT fk_equip_loc FOREIGN KEY (location_id)
		REFERENCES locations(location_id),
	 CONSTRAINT fk_equip FOREIGN KEY (equipment_id)
		REFERENCES equipment(equipment_id)
	);
	
	schedule
	(
	cust_id bigint NOT NULL,
	equipment_id varchar(20) NOT NULL,
	activedays varchar(5)  NOT NULL,  -- AD = all days, WD = mon-fri, Mn,Tu,Wd,Th,Fr
	active_start timestamp NOT NULL,
	active_end timestamp NOT NULL,
	CONSTRAINT fk_sched_cust FOREIGN KEY (cust_id)
		REFERENCES customers(cust_id),
	CONSTRAINT fk_equip FOREIGN KEY (equipment_id)
		REFERENCES equipment(equipment_id)
	);
	
	holidays
	(
	cust_id bigint NOT NULL,
	location_id bigint NOT NULL,
	holiday date NOT NULL,
	CONSTRAINT holiday_pk PRIMARY KEY (cust_id, location_id, holiday)
	);

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