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)