def get_boxids_by_key(key, value):
"""Select boxes by key-type and key-value"""
db = _get_db()
netbox_ids = []
if key in ('room', 'location'):
if key == 'location':
sql = """SELECT netboxid
FROM netbox
INNER JOIN room USING (roomid)
WHERE locationid = %s"""
if key == 'room':
sql = """SELECT netboxid
FROM netbox
WHERE roomid = %s"""
db.execute(sql, (value,))
netbox_ids = [box_id for (box_id,) in db.fetchall()]
if key == 'service':
sql = "SELECT netboxid FROM service WHERE serviceid = %s"
db.execute(sql, (int(value),))
result = db.fetchone()
if result:
(box_id,) = result
netbox_ids.append(box_id)
if key == 'netbox':
netbox_ids.append(int(value))
return netbox_ids
def getusers(self, sent='N'):
"""
Get users which has messages with given sent status (normally unsent).
Returns a sorted list with the phone numbers for all users with
messages with given sent status.
"""
users = []
dbconn = self._connect()
db = dbconn.cursor()
data = dict(sent=sent)
sql = """SELECT DISTINCT phone
FROM smsq
WHERE sent = %(sent)s
ORDER BY phone"""
db.execute(sql, data)
result = db.fetchall()
# Rollback so we don't have old open transactions which foobars the
# usage of now() in setsentstatus()
dbconn.rollback()
# Create a simple list without the tuples
for row in result:
users.append(row[0])
return users
def getmsgs(self, sent='N'):
"""
Get all messages with given sent status (normally unsent).
Returns a list of dictionaries containing messages details of SMS in
queue with the specified status.
"""
dbconn = self._connect()
db = dbconn.cursor()
data = dict(sent=sent)
sql = """SELECT smsq.id as smsqid, name, msg, time
FROM smsq
JOIN account ON (account.id = smsq.accountid)
WHERE sent = %(sent)s ORDER BY time ASC"""
db.execute(sql, data)
result = []
for (smsqid, name, msg, time) in db.fetchall():
result.append(dict(id=smsqid, name=name, msg=msg,
time=time.strftime("%Y-%m-%d %H:%M")))
# Rollback so we don't have old open transactions which foobars the
# usage of now() in setsentstatus()
dbconn.rollback()
return result
def schedule():
"""Check if there are maintenance tasks to be schedule"""
db = _get_db()
sql = """UPDATE maint_task SET state = 'scheduled'
WHERE state IS NULL
OR state NOT IN ('scheduled', 'active', 'passed', 'canceled')"""
db.execute(sql)
_get_dbconn().commit()
def check_tasks_without_end():
"""Loop thru all maintenance tasks without a defined end time, and
end the task if all boxes included in the task have been up for
longer than a minimum time."""
db = _get_db()
tasks_and_boxes = get_tasks_and_boxes_without_end()
# Loop thru all maintenance tasks and check if the affected boxes
# have been up for minimum time.
for (maint_id, netbox_ids) in tasks_and_boxes.iteritems():
boxes_still_on_maint = []
for box_id in netbox_ids:
sql = """SELECT end_time FROM alerthist
WHERE netboxid = %s AND
eventtypeid = 'boxState' AND
end_time IS NOT NULL
ORDER BY end_time DESC LIMIT 1"""
db.execute(sql, (box_id,))
result = db.fetchone()
if result:
(end_time,) = result
# Box is up
end_time_to_check = INFINITY
if end_time < (INFINITY -
datetime.timedelta(minutes=MINIMUM_UPTIME_MINS)):
end_time_to_check = (end_time +
datetime.timedelta(minutes=MINIMUM_UPTIME_MINS))
if end_time_to_check > datetime.datetime.now():
# Box have not been up for minimum time yet.
boxes_still_on_maint.append(str(box_id))
else:
# Box is not up
boxes_still_on_maint.append(str(box_id))
if len(boxes_still_on_maint) > 0:
# Some boxes are still down...
_logger.debug(
"Maintenance task %d: Boxes still on maintenance: %r",
maint_id, boxes_still_on_maint)
else:
# All affected boxes for this maintenance task have been up
# again for the minimum time.
time_now = datetime.datetime.now()
_logger.warn("Maintenance task %d: Set end at %s",
maint_id, time_now)
sql = """UPDATE maint_task
SET maint_end = %s
WHERE maint_taskid = %s"""
db.execute(sql, (time_now, maint_id,))
_get_dbconn().commit()
def inserttestmsgs(self, uid, phone, msg):
"""
Insert test messages into the SMS queue for debugging purposes.
Returns a integer indicating how many rows have been inserted.
"""
dbconn = self._connect()
db = dbconn.cursor()
data = dict(uid=uid, phone=phone, msg=msg)
sql = """INSERT INTO smsq (accountid, time, phone, msg) VALUES (
%(uid)s, now(), %(phone)s, %(msg)s)"""
db.execute(sql, data)
dbconn.commit()
return db.rowcount
def cancel(self, minage='0'):
"""
Mark all unsent messages as ignored.
Input:
minage Minimum age required for canceling message, default '0'.
Format as PostgreSQL interval type, e.g. '1 day 12 hours'.
Returns number of messages canceled.
"""
dbconn = self._connect()
db = dbconn.cursor()
data = dict(minage=str(minage))
# Test minage
if minage != '0':
sql = "SELECT interval %(minage)s"
try:
db.execute(sql, data)
except nav.db.driver.ProgrammingError:
self.logger.warning("'autocancel' value (%s) is not valid. " +
"Check config for errors.",
minage)
return 0
except Exception:
self.logger.exception("Unknown exception caught in " +
"cancel(). Exiting.")
sys.exit(1)
# Ignore messages
sql = """UPDATE smsq SET sent = 'I'
WHERE sent = 'N' AND time < now() - interval %(minage)s"""
db.execute(sql, data)
dbconn.commit()
return db.rowcount
def getusermsgs(self, user, sent='N'):
"""
Get the user's messages which has given sent status (normally unsent).
Returns a list of messsages ordered with the most severe first. Each
message is a tuple with the ID, text, and severity of the message.
"""
dbconn = self._connect()
db = dbconn.cursor()
data = dict(phone=user, sent=sent)
sql = """SELECT id, msg, severity
FROM smsq
WHERE phone = %(phone)s AND sent = %(sent)s
ORDER BY severity DESC, time ASC"""
db.execute(sql, data)
result = db.fetchall()
# Rollback so we don't have old open transactions which foobars the
# usage of now() in setsentstatus()
dbconn.rollback()
return result
def get_tasks_and_boxes_without_end():
"""Collect all netboxes from maintenance tasks that do not have a defined
end time. Place them in a dictionary with maintenance identity as key
and a list of affected netboxes for each task."""
db = _get_db()
# Select all affected components for every task.
sql = """SELECT maint_taskid, key, value
FROM maint_task
INNER JOIN maint_component USING (maint_taskid)
WHERE state = 'active' AND
maint_end >= %s"""
db.execute(sql, (INFINITY,))
tasks_and_boxes = {}
for (maint_id, key, value) in db.fetchall():
# Collect affected boxes for each maintenance task.
netbox_ids = get_boxids_by_key(key, value)
# Use maintenance key as key for netboxes that are affected by
# the maintenance task.
if maint_id in tasks_and_boxes:
tasks_and_boxes[maint_id].extend(netbox_ids)
else:
tasks_and_boxes[maint_id] = netbox_ids
return tasks_and_boxes
def setsentstatus(self, identifier, sent, smsid=0):
"""
Set the sent status of a message given ID and status.
Returns number of messages changed.
"""
dbconn = self._connect()
db = dbconn.cursor()
if sent == 'Y' or sent == 'I':
sql = """UPDATE smsq
SET sent = %(sent)s, smsid = %(smsid)s, timesent = now()
WHERE id = %(id)s"""
else:
sql = """UPDATE smsq
SET sent = %(sent)s, smsid = %(smsid)s
WHERE id = %(id)s"""
data = dict(sent=sent, smsid=smsid, id=identifier)
db.execute(sql, data)
dbconn.commit()
return db.rowcount
def setsentstatus(self, identifier, sent, smsid=0):
"""
Set the sent status of a message given ID and status.
Returns number of messages changed.
"""
dbconn = self._connect()
db = dbconn.cursor()
if sent == 'Y' or sent == 'I':
sql = """UPDATE smsq
SET sent = %(sent)s, smsid = %(smsid)s, timesent = now()
WHERE id = %(id)s"""
else:
sql = """UPDATE smsq
SET sent = %(sent)s, smsid = %(smsid)s
WHERE id = %(id)s"""
data = dict(sent=sent, smsid=smsid, id=identifier)
db.execute(sql, data)
dbconn.commit()
return db.rowcount
def check_state(events, maxdate_boxes):
"""
Checks if there are some maintenance tasks to be set active or passed.
"""
time_now = datetime.datetime.now()
db = _get_db()
sql = """SELECT maint_taskid FROM maint_task
WHERE maint_start < %s AND state = 'scheduled'"""
db.execute(sql, (time_now,))
for (taskid,) in db.fetchall():
sched_event = {}
sched_event['type'] = 'active'
sched_event['taskid'] = taskid
events.append(sched_event)
sql = """SELECT maint_taskid, maint_end FROM maint_task
WHERE maint_end < %s AND state = 'active'"""
db.execute(sql, (time_now,))
for (taskid, maint_end) in db.fetchall():
active_event = {}
active_event['type'] = 'passed'
active_event['taskid'] = taskid
active_event['maint_end'] = maint_end
events.append(active_event)
# Get boxes that should still stay on maintenance
sql = """SELECT max(maint_end) AS maint_end, key, value
FROM maint_task INNER JOIN maint_component USING (maint_taskid)
WHERE state = 'active' AND maint_end > %s
GROUP BY key, value"""
db.execute(sql, (time_now,))
for (maint_end, key, value) in db.fetchall():
boxids = get_boxids_by_key(key, value)
for boxid in boxids:
if boxid in maxdate_boxes and maxdate_boxes[boxid] > maint_end:
continue
maxdate_boxes[boxid] = maint_end
def setTask(taskid, maint_start, maint_end, description, author, state):
"""
Insert or update a maintenance task
Input:
taskid Maintenance task ID if update, set to false if new task
maint_start Start time of task
maint_end End time of task
description Description of the task
author Username of author
state State of task, initally 'scheduled', used by other
subsystems
Returns:
msgid ID of updated or inserted task
"""
dbconn = nav.db.getConnection('webfront', 'manage')
db = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
if taskid:
sql = """UPDATE maint_task SET
maint_start = %(maint_start)s,
maint_end = %(maint_end)s,
description = %(description)s,
author = %(author)s,
state = %(state)s
WHERE
maint_taskid = %(maint_taskid)s"""
else:
sql = """INSERT INTO maint_task (
maint_start,
maint_end,
description,
author,
state
) VALUES (
%(maint_start)s,
%(maint_end)s,
%(description)s,
%(author)s,
%(state)s
)"""
if not isinstance(description, unicode):
description = description.decode('utf-8')
data = {
'maint_taskid': taskid,
'maint_start': time.strftime('%Y-%m-%d %H:%M:%S', maint_start),
'maint_end': time.strftime('%Y-%m-%d %H:%M:%S', maint_end),
'description': description,
'author': author,
'state': state
}
logger.debug("setTask() query: %s", sql % data)
db.execute(sql, data)
if not taskid:
db.execute("SELECT CURRVAL('maint_task_maint_taskid_seq')")
taskid = db.fetchone()['currval']
logger.debug("setTask() number of results: %d", db.rowcount)
return taskid
def remove_forgotten(boxes_off_maintenance):
"""
Remove 'forgotten' netboxes from their maintenance state.
Sometimes, like when netboxes have been deleted from a maintenance task
during its active maintenance window, we will no longer know that the box
has gone on maintenenance and should be taken off. This function takes all
'forgotten' netboxes off maintenance.
"""
db = _get_db()
# This SQL retrieves a list of boxes that are currently on
# maintenance, according to the alert history.
sql_actual = """SELECT ah.netboxid, ah.deviceid, n.sysname, subid
FROM alerthist ah LEFT JOIN netbox n USING (netboxid)
WHERE eventtypeid='maintenanceState' AND netboxid IS NOT NULL
AND end_time = 'infinity'"""
# This SQL retrieves a list of boxes that are supposed to be on
# maintenance, according to the schedule.
sql_sched = """SELECT n.netboxid, n.deviceid, n.sysname, NULL AS subid
FROM maint m INNER JOIN netbox n ON (n.netboxid::text = m.value)
WHERE m.key = 'netbox' AND m.state = 'active'
UNION
SELECT n.netboxid, n.deviceid, n.sysname, NULL AS subid
FROM maint m INNER JOIN netbox n ON (n.roomid = m.value)
WHERE m.key = 'room' AND m.state = 'active'
UNION
SELECT n.netboxid, n.deviceid, n.sysname, NULL AS subid
FROM maint m INNER JOIN netbox n ON (n.roomid IN
(SELECT roomid FROM room WHERE locationid = m.value))
WHERE m.key = 'location' AND m.state = 'active'
UNION
SELECT n.netboxid, n.deviceid, n.sysname, m.value AS subid
FROM maint m INNER JOIN netbox n ON (n.netboxid IN
(SELECT netboxid FROM service WHERE
serviceid::text LIKE m.value))
WHERE m.key = 'service' AND m.state = 'active'"""
# The full SQL is a set operation to select all boxes that are
# currently on maintenance and subtracts those that are supposed
# to be on maintenance - resulting in a list of boxes that should
# be taken off maintenance immediately.
sql_full = "(%s) \n EXCEPT \n (%s)" % (sql_actual, sql_sched)
db.execute(sql_full)
target = 'eventEngine'
subsystem = 'maintenance'
source = subsystem
severity = 50
eventtype = 'maintenanceState'
state = 'e'
value = 0
for (netboxid, deviceid, sysname, subid) in db.fetchall():
if netboxid in boxes_off_maintenance:
# MaintenenceOff-events posted during this run might not
# have been processed by eventEngine yet. We discard these
# boxes here.
continue
# If it's a service, we have to set subid also
if subid is None:
_logger.info("Box %s (%d) is on unscheduled maintenance. "
"Taking off maintenance now.", sysname, netboxid)
subid = False
else:
_logger.info(
"Service (%d) at box %s (%d) is on unscheduled maintenance. "
"Taking off maintenance...", subid, sysname, netboxid)
subid = int(subid)
# Create event
event = nav.event.Event(
source=source, target=target,
deviceid=deviceid, netboxid=netboxid, subid=subid,
eventtypeid=eventtype, state=state, value=value, severity=severity)
result = event.post()
_logger.debug("Event: %s, Result: %s", event, result)
# Commit transaction
_get_dbconn().commit()
def send_event(events, maxdate_boxes, boxes_off_maintenance):
"""Sends events to the event queue."""
db = _get_db()
for curr_event in events:
event_type = curr_event['type']
taskid = curr_event['taskid']
# Get all components related to task/event
sql = """SELECT key, value FROM maint_component
WHERE maint_taskid = %(maint_taskid)s"""
data = {'maint_taskid': taskid}
db.execute(sql, data)
for (key, val) in db.fetchall():
# Prepare event variables
target = 'eventEngine'
subsystem = 'maintenance'
source = subsystem
severity = 50
eventtype = 'maintenanceState'
if event_type == 'active':
state = 's' # s = start
value = 100
elif event_type == 'passed':
state = 'e' # e = end
value = 0
# Get all related netboxes
netboxes = []
if key in ('location', 'room'):
if key == 'location':
sql = """SELECT netboxid, sysname, deviceid
FROM netbox INNER JOIN room USING (roomid)
WHERE locationid = %(locationid)s"""
data = {'locationid': val}
_logger.debug("location query: %s", sql % data)
db.execute(sql, data)
_logger.debug("location number of results: %d", db.rowcount)
elif key == 'room':
sql = """SELECT netboxid, sysname, deviceid
FROM netbox
WHERE roomid = %(roomid)s"""
data = {'roomid': val}
_logger.debug("room query: %s", sql % data)
db.execute(sql, data)
_logger.debug("room number of results: %d", db.rowcount)
for (netboxid, sysname, deviceid) in db.fetchall():
netboxes.append({'netboxid': netboxid,
'sysname': sysname,
'deviceid': deviceid,
'cvar': 'netbox',
'cval': sysname})
elif key == 'netbox':
sql = """SELECT netboxid, sysname, deviceid
FROM netbox
WHERE netboxid = %(netboxid)s"""
data = {'netboxid': int(val)}
_logger.debug("netbox query: %s", sql % data)
db.execute(sql, data)
_logger.debug("netbox number of results: %d", db.rowcount)
result = db.fetchone()
if result:
(netboxid, sysname, deviceid) = result
netboxes.append({'netboxid': netboxid,
'sysname': sysname,
'deviceid': deviceid,
'cvar': 'netbox',
'cval': sysname})
elif key == 'service':
sql = """SELECT netboxid, sysname, deviceid, handler
FROM service INNER JOIN netbox USING (netboxid)
WHERE serviceid = %(serviceid)s"""
data = {'serviceid': int(val)}
_logger.debug("service query: %s", sql % data)
db.execute(sql, data)
_logger.debug("service number of results: %d", db.rowcount)
result = db.fetchone()
if result:
(netboxid, sysname, deviceid, handler) = result
netboxes.append({'netboxid': netboxid,
'sysname': sysname,
'deviceid': deviceid,
'serviceid': int(val),
'servicename': handler,
'cvar': 'service',
'cval': handler})
elif key == 'module':
# Unsupported as of NAV 3.2
raise DeprecationWarning("Deprecated component key")
# Create events for all related netboxes
for netbox in netboxes:
if event_type == 'passed' and netbox['netboxid'] in maxdate_boxes:
netbox_id = netbox['netboxid']
if maxdate_boxes[netbox_id] > curr_event['maint_end']:
_logger.debug(
"Skip stop event for netbox %s. It's on "
"maintenance until %s.",
str(netbox['netboxid']),
str(curr_event['maint_end'])
)
continue
# Append to list of boxes taken off maintenance
# during this run
if event_type == 'passed':
boxes_off_maintenance.append(netbox['netboxid'])
if 'serviceid' in netbox:
subid = netbox['serviceid']
else:
subid = None
# Create event
event = nav.event.Event(source=source, target=target,
deviceid=netbox['deviceid'],
netboxid=netbox['netboxid'], subid=subid,
eventtypeid=eventtype, state=state, value=value,
severity=severity)
event[netbox['cvar']] = netbox['cval']
event['maint_taskid'] = taskid
# Add event to eventq
result = event.post()
_logger.debug("Event: %s, Result: %s", event, result)
# Update state
sql = """UPDATE maint_task
SET state = %(state)s
WHERE maint_taskid = %(maint_taskid)s"""
data = {'state': event_type,
'maint_taskid': taskid}
db.execute(sql, data)
# Commit transaction
_get_dbconn().commit()