def get_user_names(self, kwargs=None):
monitor_db = database.MonitorDatabase()
user_cond = ''
if session.get_session_user_id() and int(
session.get_session_access_level()) < 3:
user_cond = 'AND user_id = %s ' % session.get_session_user_id()
try:
query = 'SELECT user_id, ' \
'(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" \
THEN users.username ELSE users.friendly_name END) AS friendly_name ' \
'FROM users ' \
'WHERE deleted_user = 0 %s' % user_cond
result = monitor_db.select(query)
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for get_user_names: %s."
% e)
return None
return session.friendly_name_to_username(result)
def get_datatables_list(self, kwargs=None):
default_return = {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
data_tables = datatables.DataTables()
custom_where = [['users.deleted_user', 0]]
if session.get_session_user_id() and int(
session.get_session_access_level()) < 3:
custom_where.append(
['users.user_id',
session.get_session_user_id()])
if kwargs.get('user_id'):
custom_where.append(['users.user_id', kwargs.get('user_id')])
columns = [
'users.user_id',
'(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" \
THEN users.username ELSE users.friendly_name END) AS friendly_name',
'users.thumb AS user_thumb',
'users.custom_avatar_url AS custom_thumb',
'COUNT(session_history.id) AS plays',
'SUM(CASE WHEN session_history.stopped > 0 THEN (session_history.stopped - session_history.started) \
ELSE 0 END) - SUM(CASE WHEN session_history.paused_counter IS NULL THEN 0 ELSE \
session_history.paused_counter END) AS duration',
'MAX(session_history.started) AS last_seen',
'MAX(session_history.id) AS id',
'session_history_metadata.full_title AS last_played',
'servers.pms_name AS last_server', 'session_history.ip_address',
'session_history.platform', 'session_history.player',
'session_history.rating_key',
'session_history_metadata.media_type',
'session_history_metadata.thumb',
'session_history_metadata.parent_thumb',
'session_history_metadata.grandparent_thumb',
'session_history_metadata.parent_title',
'session_history_metadata.year',
'session_history_metadata.media_index',
'session_history_metadata.parent_media_index',
'session_history_media_info.transcode_decision',
'users.do_notify as do_notify',
'users.keep_history as keep_history',
'users.allow_guest as allow_guest'
]
try:
query = data_tables.ssp_query(
table_name='users',
columns=columns,
custom_where=custom_where,
group_by=['users.user_id'],
join_types=[
'LEFT OUTER JOIN', 'LEFT OUTER JOIN', 'LEFT OUTER JOIN',
'LEFT OUTER JOIN'
],
join_tables=[
'session_history', 'session_history_metadata',
'session_history_media_info', 'servers'
],
join_evals=[
['session_history.user_id', 'users.user_id'],
['session_history.id', 'session_history_metadata.id'],
['session_history.id', 'session_history_media_info.id'],
['session_history.server_id', 'servers.id']
],
kwargs=kwargs)
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for get_list: %s."
% e)
return default_return
users = query['result']
rows = []
for item in users:
if item['media_type'] == 'episode' and item['parent_thumb']:
thumb = item['parent_thumb']
elif item['media_type'] == 'episode':
thumb = item['grandparent_thumb']
else:
thumb = item['thumb']
if item['custom_thumb'] and item['custom_thumb'] != item[
'user_thumb']:
user_thumb = item['custom_thumb']
elif item['user_thumb']:
user_thumb = item['user_thumb']
else:
user_thumb = common.DEFAULT_USER_THUMB
# Rename Mystery platform names
platform = common.PLATFORM_NAME_OVERRIDES.get(
item['platform'], item['platform'])
row = {
'user_id': item['user_id'],
'friendly_name': item['friendly_name'],
'user_thumb': user_thumb,
'plays': item['plays'],
'duration': item['duration'],
'last_seen': item['last_seen'],
'last_played': item['last_played'],
'last_server': item['last_server'],
'id': item['id'],
'ip_address': item['ip_address'],
'platform': platform,
'player': item['player'],
'rating_key': item['rating_key'],
'media_type': item['media_type'],
'thumb': thumb,
'parent_title': item['parent_title'],
'year': item['year'],
'media_index': item['media_index'],
'parent_media_index': item['parent_media_index'],
'transcode_decision': item['transcode_decision'],
'do_notify': helpers.checked(item['do_notify']),
'keep_history': helpers.checked(item['keep_history']),
'allow_guest': item['allow_guest']
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': session.friendly_name_to_username(rows),
'draw': query['draw']
}
return dict
def get_datatables_unique_ips(self, user_id=None, kwargs=None):
default_return = {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
if not session.allow_session_user(user_id):
return default_return
data_tables = datatables.DataTables()
custom_where = ['users.user_id', user_id]
columns = [
'session_history.id', 'session_history.started AS last_seen',
'session_history.ip_address',
'COUNT(session_history.id) AS play_count',
'session_history.platform', 'session_history.player',
'session_history.rating_key',
'session_history_metadata.full_title AS last_played',
'session_history_metadata.thumb',
'session_history_metadata.parent_thumb',
'session_history_metadata.grandparent_thumb',
'session_history_metadata.media_type',
'session_history_metadata.parent_title',
'session_history_metadata.year',
'session_history_metadata.media_index',
'session_history_metadata.parent_media_index',
'session_history_media_info.transcode_decision',
'session_history.user',
'session_history.user_id as custom_user_id',
'(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" \
THEN users.username ELSE users.friendly_name END) AS friendly_name'
]
try:
query = data_tables.ssp_query(
table_name='session_history',
columns=columns,
custom_where=[custom_where],
group_by=['ip_address'],
join_types=['JOIN', 'JOIN', 'JOIN'],
join_tables=[
'users', 'session_history_metadata',
'session_history_media_info'
],
join_evals=[
['session_history.user_id', 'users.user_id'],
['session_history.id', 'session_history_metadata.id'],
['session_history.id', 'session_history_media_info.id']
],
kwargs=kwargs)
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for get_unique_ips: %s."
% e)
return default_return
results = query['result']
rows = []
for item in results:
if item["media_type"] == 'episode' and item["parent_thumb"]:
thumb = item["parent_thumb"]
elif item["media_type"] == 'episode':
thumb = item["grandparent_thumb"]
else:
thumb = item["thumb"]
# Rename Mystery platform names
platform = common.PLATFORM_NAME_OVERRIDES.get(
item["platform"], item["platform"])
row = {
'id': item['id'],
'last_seen': item['last_seen'],
'ip_address': item['ip_address'],
'play_count': item['play_count'],
'platform': platform,
'player': item['player'],
'last_played': item['last_played'],
'rating_key': item['rating_key'],
'thumb': thumb,
'media_type': item['media_type'],
'parent_title': item['parent_title'],
'year': item['year'],
'media_index': item['media_index'],
'parent_media_index': item['parent_media_index'],
'transcode_decision': item['transcode_decision'],
'friendly_name': item['friendly_name'],
'user_id': item['custom_user_id']
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': session.friendly_name_to_username(rows),
'draw': query['draw']
}
return dict
def get_datatables_user_login(self, user_id=None, kwargs=None):
default_return = {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
if not session.allow_session_user(user_id):
return default_return
data_tables = datatables.DataTables()
if session.get_session_user_id() and int(
session.get_session_access_level()) < 5:
custom_where = [[
'user_login.user_id',
session.get_session_user_id()
]]
else:
custom_where = [['user_login.user_id', user_id]] if user_id else []
columns = [
'user_login.timestamp', 'user_login.user_id', 'user_login.user',
'user_login.user_group', 'user_login.ip_address',
'user_login.host', 'user_login.user_agent', 'user_login.success',
'(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" \
THEN users.username ELSE users.friendly_name END) AS friendly_name'
]
try:
query = data_tables.ssp_query(
table_name='user_login',
columns=columns,
custom_where=custom_where,
group_by=[],
join_types=['LEFT OUTER JOIN'],
join_tables=['users'],
join_evals=[['user_login.user_id', 'users.user_id']],
kwargs=kwargs)
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for get_datatables_user_login: %s."
% e)
return default_return
results = query['result']
rows = []
for item in results:
(os, browser) = httpagentparser.simple_detect(item['user_agent'])
row = {
'timestamp': item['timestamp'],
'user_id': item['user_id'],
'user_group': item['user_group'],
'ip_address': item['ip_address'],
'host': item['host'],
'user_agent': item['user_agent'],
'os': os,
'browser': browser,
'success': item['success'],
'friendly_name': item['friendly_name'] or item['user']
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': session.friendly_name_to_username(rows),
'draw': query['draw']
}
return dict
