def sync_from_oncall(config, engine, purge_old_users=True): # users and teams present in our oncall database oncall_base_url = config.get('oncall-api') if not oncall_base_url: logger.error( 'Missing URL to oncall-api, which we use for user/team lookups. Bailing.' ) return oncall = oncallclient.OncallClient(config.get('oncall-app', ''), config.get('oncall-key', ''), oncall_base_url) oncall_users = fetch_users_from_oncall(oncall) if not oncall_users: logger.warning('No users found. Bailing.') return oncall_team_names = fetch_teams_from_oncall(oncall) if not oncall_team_names: logger.warning('We do not have a list of team names') oncall_team_names = set(oncall_team_names) session = sessionmaker(bind=engine)() # users present in iris' database iris_users = {} for row in engine.execute( '''SELECT `target`.`name` as `name`, `mode`.`name` as `mode`, `target_contact`.`destination` FROM `target` JOIN `user` on `target`.`id` = `user`.`target_id` LEFT OUTER JOIN `target_contact` ON `target`.`id` = `target_contact`.`target_id` LEFT OUTER JOIN `mode` ON `target_contact`.`mode_id` = `mode`.`id` WHERE `target`.`active` = TRUE ORDER BY `target`.`name`'''): contacts = iris_users.setdefault(row.name, {}) if row.mode is None or row.destination is None: continue contacts[row.mode] = row.destination iris_usernames = iris_users.viewkeys() # users from the oncall endpoints and config files metrics.set('users_found', len(oncall_users)) metrics.set('teams_found', len(oncall_team_names)) oncall_users.update(get_predefined_users(config)) oncall_usernames = oncall_users.viewkeys() # set of users not presently in iris users_to_insert = oncall_usernames - iris_usernames # set of existing iris users that are in the user oncall database users_to_update = iris_usernames & oncall_usernames users_to_mark_inactive = iris_usernames - oncall_usernames # get objects needed for insertion target_types = { name: id for name, id in session.execute( 'SELECT `name`, `id` FROM `target_type`') } # 'team' and 'user' modes = { name: id for name, id in session.execute('SELECT `name`, `id` FROM `mode`') } iris_team_names = { name for (name, ) in engine.execute( '''SELECT `name` FROM `target` WHERE `type_id` = %s''', target_types['team']) } target_add_sql = 'INSERT INTO `target` (`name`, `type_id`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE `active` = TRUE' user_add_sql = 'INSERT IGNORE INTO `user` (`target_id`) VALUES (%s)' target_contact_add_sql = '''INSERT INTO `target_contact` (`target_id`, `mode_id`, `destination`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `destination` = %s''' # insert users that need to be logger.info('Users to insert (%d)' % len(users_to_insert)) for username in users_to_insert: logger.info('Inserting %s' % username) try: target_id = engine.execute( target_add_sql, (username, target_types['user'])).lastrowid engine.execute(user_add_sql, (target_id, )) except SQLAlchemyError as e: metrics.incr('users_failed_to_add') metrics.incr('sql_errors') logger.exception('Failed to add user %s' % username) continue metrics.incr('users_added') for key, value in oncall_users[username].iteritems(): if value and key in modes: logger.info('%s: %s -> %s' % (username, key, value)) engine.execute(target_contact_add_sql, (target_id, modes[key], value, value)) # update users that need to be contact_update_sql = 'UPDATE target_contact SET destination = %s WHERE target_id = (SELECT id FROM target WHERE name = %s) AND mode_id = %s' contact_insert_sql = 'INSERT INTO target_contact (target_id, mode_id, destination) VALUES ((SELECT id FROM target WHERE name = %s), %s, %s)' contact_delete_sql = 'DELETE FROM target_contact WHERE target_id = (SELECT id FROM target WHERE name = %s) AND mode_id = %s' logger.info('Users to update (%d)' % len(users_to_update)) for username in users_to_update: try: db_contacts = iris_users[username] oncall_contacts = oncall_users[username] for mode in modes: if mode in oncall_contacts and oncall_contacts[mode]: if mode in db_contacts: if oncall_contacts[mode] != db_contacts[mode]: logger.info('%s: updating %s' % (username, mode)) metrics.incr('user_contacts_updated') engine.execute( contact_update_sql, (oncall_contacts[mode], username, modes[mode])) else: logger.info('%s: adding %s' % (username, mode)) metrics.incr('user_contacts_updated') engine.execute( contact_insert_sql, (username, modes[mode], oncall_contacts[mode])) elif mode in db_contacts: logger.info('%s: deleting %s' % (username, mode)) metrics.incr('user_contacts_updated') engine.execute(contact_delete_sql, (username, modes[mode])) else: logger.debug('%s: missing %s' % (username, mode)) except SQLAlchemyError as e: metrics.incr('users_failed_to_update') metrics.incr('sql_errors') logger.exception('Failed to update user %s' % username) continue # sync teams between iris and oncall teams_to_insert = oncall_team_names - iris_team_names teams_to_deactivate = iris_team_names - oncall_team_names logger.info('Teams to insert (%d)' % len(teams_to_insert)) for t in teams_to_insert: logger.info('Inserting %s' % t) try: target_id = engine.execute(target_add_sql, (t, target_types['team'])).lastrowid metrics.incr('teams_added') except SQLAlchemyError as e: logger.exception('Error inserting team %s: %s' % (t, e)) metrics.incr('teams_failed_to_add') continue session.commit() session.close() # mark users/teams inactive if purge_old_users: logger.info('Users to mark inactive (%d)' % len(users_to_mark_inactive)) for username in users_to_mark_inactive: prune_target(engine, username, 'user') for team in teams_to_deactivate: prune_target(engine, team, 'team')
def sync_from_oncall(config, engine, purge_old_users=True): # users and teams present in our oncall database oncall_base_url = config.get('oncall-api') if not oncall_base_url: logger.error( 'Missing URL to oncall-api, which we use for user/team lookups. Bailing.' ) return oncall = oncallclient.OncallClient(config.get('oncall-app', ''), config.get('oncall-key', ''), oncall_base_url) oncall_users = fetch_users_from_oncall(oncall) if not oncall_users: logger.warning('No users found. Bailing.') return # get teams from oncall-api and separate the list of tuples into two lists of name and ids oncall_teams_api_response = fetch_teams_from_oncall(oncall) if not oncall_teams_api_response: logger.warning('No teams found. Bailing.') return oncall_team_response = list(zip(*oncall_teams_api_response)) oncall_team_names = [name.lower() for name in oncall_team_response[0]] oncall_team_ids = oncall_team_response[1] oncall_response_dict_name_key = dict( zip(oncall_team_names, oncall_team_ids)) oncall_response_dict_id_key = dict(zip(oncall_team_ids, oncall_team_names)) oncall_case_sensitive_dict = { name.lower(): name for name in oncall_team_response[0] } if not oncall_team_names: logger.warning('We do not have a list of team names') oncall_team_names = set(oncall_team_names) oncall_team_ids = set(oncall_team_ids) session = sessionmaker(bind=engine)() # users present in iris' database iris_users = {} for row in engine.execute( '''SELECT `target`.`name` as `name`, `mode`.`name` as `mode`, `target_contact`.`destination` FROM `target` JOIN `user` on `target`.`id` = `user`.`target_id` LEFT OUTER JOIN `target_contact` ON `target`.`id` = `target_contact`.`target_id` LEFT OUTER JOIN `mode` ON `target_contact`.`mode_id` = `mode`.`id` WHERE `target`.`active` = TRUE ORDER BY `target`.`name`'''): contacts = iris_users.setdefault(row.name, {}) if row.mode is None or row.destination is None: continue contacts[row.mode] = row.destination iris_usernames = iris_users.keys() # users from the oncall endpoints and config files metrics.set('users_found', len(oncall_users)) metrics.set('teams_found', len(oncall_team_names)) oncall_users.update(get_predefined_users(config)) oncall_usernames = oncall_users.keys() # set of users not presently in iris users_to_insert = oncall_usernames - iris_usernames # set of existing iris users that are in the user oncall database users_to_update = iris_usernames & oncall_usernames users_to_mark_inactive = iris_usernames - oncall_usernames # get objects needed for insertion target_types = { name: target_id for name, target_id in session.execute( 'SELECT `name`, `id` FROM `target_type`') } # 'team' and 'user' modes = { name: mode_id for name, mode_id in session.execute('SELECT `name`, `id` FROM `mode`') } iris_team_names = { name.lower() for (name, ) in engine.execute( '''SELECT `name` FROM `target` WHERE `type_id` = %s''', target_types['team']) } target_add_sql = 'INSERT INTO `target` (`name`, `type_id`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE `active` = TRUE' oncall_add_sql = 'INSERT INTO `oncall_team` (`target_id`, `oncall_team_id`) VALUES (%s, %s)' user_add_sql = 'INSERT IGNORE INTO `user` (`target_id`) VALUES (%s)' target_contact_add_sql = '''INSERT INTO `target_contact` (`target_id`, `mode_id`, `destination`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `destination` = %s''' # insert users that need to be logger.info('Users to insert (%d)', len(users_to_insert)) for username in users_to_insert: sleep(update_sleep) logger.info('Inserting %s', username) try: target_id = engine.execute( target_add_sql, (username, target_types['user'])).lastrowid engine.execute(user_add_sql, (target_id, )) except SQLAlchemyError as e: metrics.incr('users_failed_to_add') metrics.incr('sql_errors') logger.exception('Failed to add user %s' % username) continue metrics.incr('users_added') for key, value in oncall_users[username].items(): if value and key in modes: logger.info('%s: %s -> %s', username, key, value) try: engine.execute(target_contact_add_sql, (target_id, modes[key], value, value)) except SQLAlchemyError as e: logger.exception('Error adding contact for target id: %s', target_id) metrics.incr('sql_errors') continue # update users that need to be contact_update_sql = 'UPDATE target_contact SET destination = %s WHERE target_id = (SELECT id FROM target WHERE name = %s AND type_id = %s) AND mode_id = %s' contact_insert_sql = 'INSERT INTO target_contact (target_id, mode_id, destination) VALUES ((SELECT id FROM target WHERE name = %s AND type_id = %s), %s, %s)' contact_delete_sql = 'DELETE FROM target_contact WHERE target_id = (SELECT id FROM target WHERE name = %s AND type_id = %s) AND mode_id = %s' logger.info('Users to update (%d)', len(users_to_update)) for username in users_to_update: sleep(update_sleep) try: db_contacts = iris_users[username] oncall_contacts = oncall_users[username] for mode in modes: if mode in oncall_contacts and oncall_contacts[mode]: if mode in db_contacts: if oncall_contacts[mode] != db_contacts[mode]: logger.info('%s: updating %s', username, mode) metrics.incr('user_contacts_updated') engine.execute(contact_update_sql, (oncall_contacts[mode], username, target_types['user'], modes[mode])) else: logger.info('%s: adding %s', username, mode) metrics.incr('user_contacts_updated') engine.execute(contact_insert_sql, (username, target_types['user'], modes[mode], oncall_contacts[mode])) elif mode in db_contacts: logger.info('%s: deleting %s', username, mode) metrics.incr('user_contacts_updated') engine.execute( contact_delete_sql, (username, target_types['user'], modes[mode])) else: logger.debug('%s: missing %s', username, mode) except SQLAlchemyError as e: metrics.incr('users_failed_to_update') metrics.incr('sql_errors') logger.exception('Failed to update user %s', username) continue # sync teams between iris and oncall # iris_db_oncall_team_ids (team_ids in the oncall_team table) # oncall_team_ids (team_ids from oncall api call) # oncall_team_names (names from oncall api call) # oncall_response_dict_name_key (key value pairs of oncall team names and ids from api call) # oncall_response_dict_id_key same as above but key value inverted # oncall_case_sensitive_dict maps the case insensitive oncall name to the original capitalization # iris_team_names (names from target table) # iris_target_name_id_dict dictionary of target name -> target_id mappings # iris_db_oncall_team_id_name_dict dictionary of oncall team_id -> oncall name mappings # get all incoming names that match a target check if that target has an entry in oncall table if not make one iris_target_name_id_dict = { name.lower(): target_id for name, target_id in engine.execute( '''SELECT `name`, `id` FROM `target` WHERE `type_id` = %s''', target_types['team']) } matching_target_names = iris_team_names.intersection(oncall_team_names) if matching_target_names: existing_up_to_date_oncall_teams = { name.lower() for (name, ) in session.execute( '''SELECT `target`.`name` FROM `target` JOIN `oncall_team` ON `oncall_team`.`target_id` = `target`.`id` WHERE `target`.`name` IN :matching_names''', {'matching_names': tuple(matching_target_names)}) } # up to date target names that don't have an entry in the oncall_team table yet matching_target_names_no_oncall_entry = matching_target_names - existing_up_to_date_oncall_teams for t in matching_target_names_no_oncall_entry: logger.info('Inserting existing team into oncall_team %s', t) sleep(update_sleep) try: engine.execute( '''UPDATE `target` SET `active` = TRUE WHERE `id` = %s''', iris_target_name_id_dict[t]) engine.execute(oncall_add_sql, (iris_target_name_id_dict[t], oncall_response_dict_name_key[t])) except SQLAlchemyError as e: logger.exception('Error inserting oncall_team %s: %s', t, e) metrics.incr('sql_errors') continue # rename all mismatching target names iris_db_oncall_team_id_name_dict = { team_id: name.lower() for name, team_id in engine.execute( '''SELECT target.name, oncall_team.oncall_team_id FROM `target` JOIN `oncall_team` ON oncall_team.target_id = target.id''' ) } iris_db_oncall_team_ids = { oncall_team_id for (oncall_team_id, ) in engine.execute( '''SELECT `oncall_team_id` FROM `oncall_team`''') } matching_oncall_ids = oncall_team_ids.intersection(iris_db_oncall_team_ids) name_swaps = {} # find teams in the iris database whose names have changed for oncall_id in matching_oncall_ids: current_name = iris_db_oncall_team_id_name_dict[oncall_id] new_name = oncall_response_dict_id_key[oncall_id] try: if current_name != new_name: # handle edge case of teams swapping names if not iris_target_name_id_dict.get(new_name, None): target_id_to_rename = iris_target_name_id_dict[ current_name] logger.info('Renaming team %s to %s', current_name, new_name) engine.execute( '''UPDATE `target` SET `name` = %s, `active` = TRUE WHERE `id` = %s''', (oncall_case_sensitive_dict[new_name], target_id_to_rename)) else: # there is a team swap so rename to a random name to prevent a violation of unique target name constraint new_name = str(uuid.uuid4()) target_id_to_rename = iris_target_name_id_dict[ current_name] name_swaps[oncall_id] = target_id_to_rename logger.info('Renaming team %s to %s', current_name, new_name) engine.execute( '''UPDATE `target` SET `name` = %s, `active` = TRUE WHERE `id` = %s''', (new_name, target_id_to_rename)) sleep(update_sleep) except SQLAlchemyError as e: logger.exception('Error changing team name of %s to %s', current_name, new_name) metrics.incr('sql_errors') # go back and rename name_swaps to correct value for oncall_id, target_id_to_rename in name_swaps.items(): new_name = oncall_response_dict_id_key[oncall_id] try: engine.execute( '''UPDATE `target` SET `name` = %s, `active` = TRUE WHERE `id` = %s''', (oncall_case_sensitive_dict[new_name], target_id_to_rename)) except SQLAlchemyError as e: logger.exception('Error renaming target: %s', new_name) metrics.incr('sql_errors') continue sleep(update_sleep) # create new entries for new teams # if the team_id doesn't exist in oncall_team at this point then it is a new team. new_team_ids = oncall_team_ids - iris_db_oncall_team_ids logger.info('Teams to insert (%d)' % len(new_team_ids)) for team_id in new_team_ids: t = oncall_case_sensitive_dict[oncall_response_dict_id_key[team_id]] new_target_id = None # add team to target table logger.info('Inserting team %s', t) sleep(update_sleep) try: new_target_id = engine.execute(target_add_sql, (t, target_types['team'])).lastrowid metrics.incr('teams_added') except SQLAlchemyError as e: logger.exception('Error inserting team %s: %s', t, e) metrics.incr('teams_failed_to_add') metrics.incr('sql_errors') continue # add team to oncall_team table if new_target_id: logger.info('Inserting new team into oncall_team %s', t) try: engine.execute(oncall_add_sql, (new_target_id, team_id)) except SQLAlchemyError as e: logger.exception('Error inserting oncall_team %s: %s', t, e) metrics.incr('sql_errors') continue session.commit() session.close() # mark users/teams inactive if purge_old_users: # find active teams that don't exist in oncall anymore updated_iris_team_names = { name.lower() for (name, ) in engine.execute( '''SELECT `name` FROM `target` WHERE `type_id` = %s AND `active` = TRUE''', target_types['team']) } teams_to_deactivate = updated_iris_team_names - oncall_team_names logger.info('Users to mark inactive (%d)' % len(users_to_mark_inactive)) logger.info('Teams to mark inactive (%d)' % len(teams_to_deactivate)) for username in users_to_mark_inactive: prune_target(engine, username, 'user') sleep(update_sleep) for team in teams_to_deactivate: prune_target(engine, team, 'team') sleep(update_sleep)