On Tue, Jun 20, 2017 at 11:12 AM, Steve Clark <steve.cl...@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.
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general