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 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 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 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 getLocation(locationid):
"""
Get location (part of maintenance component)
Input:
locationid ID of location
Returns:
If location found, returns dictionary with results
If no location found, returns false
"""
if not len(locationid):
return False
if hasattr(locationid, 'value'):
locationid = locationid.value
dbconn = nav.db.getConnection('webfront', 'manage')
db = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
sql = """SELECT l.locationid, l.descr AS locationdescr
FROM location l
WHERE locationid = %(locationid)s"""
data = {'locationid': locationid}
logger.debug("getLocation() query: %s", sql % data)
db.execute(sql, data)
logger.debug("getLocation() number of results: %d", db.rowcount)
if not db.rowcount:
return False
result = db.fetchall()
return dict(result[0])
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 getTasks(where=False, order='maint_end DESC'):
"""
Get maintenance tasks
Input:
where Where clause of query. Do NOT use user supplied data in the
where clause without proper sanitation.
Returns:
If tasks found, returns dictionary with results
If no tasks found, returns false
"""
dbconn = nav.db.getConnection('webfront', 'manage')
db = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
select = """SELECT maint_taskid, maint_start, maint_end,
maint_end - maint_start AS interval,
description, author, state
FROM maint_task"""
if where:
sql = "%s WHERE %s ORDER BY %s" % (select, where, order)
else:
sql = "%s ORDER BY %s" % (select, order)
logger.debug("getTask() query: %s", sql)
db.execute(sql)
logger.debug("getTask() number of results: %d", db.rowcount)
if not db.rowcount:
return False
results = [dict(row) for row in db.fetchall()]
# Attach components belonging to this message
for i, result in enumerate(results):
results[i]['components'] = getComponents(results[i]['maint_taskid']) \
or []
return results
def getService(serviceid):
"""
Get service (part of maintenance component)
Input:
serviceid ID of service
Returns:
If service found, returns dictionary with results
If no service found, returns false
"""
if not len(serviceid):
return False
dbconn = nav.db.getConnection('webfront', 'manage')
db = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
sql = """SELECT
s.serviceid, s.handler,
n.netboxid, n.sysname, n.ip,
r.roomid, r.descr AS roomdescr,
l.locationid, l.descr AS locationdescr
FROM service s
JOIN netbox n ON (s.netboxid = n.netboxid)
JOIN room r ON (n.roomid = r.roomid)
JOIN location l ON (r.locationid = l.locationid)
WHERE s.serviceid = %(serviceid)s"""
data = {'serviceid': int(serviceid)}
logger.debug("getService() query: %s", sql % data)
db.execute(sql, data)
logger.debug("getService() number of results: %d", db.rowcount)
if not db.rowcount:
return False
result = db.fetchall()
return dict(result[0])
def getRoom(roomid):
"""
Get room (part of maintenance component)
Input:
roomid ID of room
Returns:
If room found, returns dictionary with results
If no room found, returns false
"""
if not len(roomid):
return False
if hasattr(roomid, 'value'):
roomid = roomid.value
dbconn = nav.db.getConnection('webfront', 'manage')
db = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
sql = """SELECT
r.roomid, r.descr AS roomdescr,
l.locationid, l.descr AS locationdescr
FROM room r
JOIN location l ON (r.locationid = l.locationid)
WHERE roomid = %(roomid)s"""
data = {'roomid': roomid}
logger.debug("getRoom() query: %s", sql % data)
db.execute(sql, data)
logger.debug("getRoom() number of results: %d", db.rowcount)
if not db.rowcount:
return False
result = db.fetchall()
return dict(result[0])
def getComponents(taskid):
"""
Get maintenance components belonging to a maintenance task
Input:
taskid ID of maintenance task
Returns:
If components found, returns dictionary with results
If no components found, returns false
"""
dbconn = nav.db.getConnection('webfront', 'manage')
db = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
sql = """SELECT key, value
FROM maint_component
WHERE maint_taskid = %(maint_taskid)s
ORDER BY key, value"""
data = {'maint_taskid': taskid}
logger.debug("getComponents() query: %s", sql % data)
db.execute(sql, data)
logger.debug("getComponents() number of results: %d", db.rowcount)
if not db.rowcount:
return False
results = [dict(row) for row in db.fetchall()]
# Attach information about the components
for i, result in enumerate(results):
results[i]['info'] = getComponentInfo(results[i]['key'],
results[i]['value']) or None
# Sort components
results = sortComponents(results)
return results
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 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 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()
