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
