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 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 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()