# -*- coding: utf-8 -*- # This file is part of Tautulli. # # Tautulli is free software: you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # # Tautulli is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with Tautulli. If not, see . from urllib.parse import parse_qsl import arrow import httpagentparser import plexpy from plexpy import common from plexpy import database from plexpy import datatables from plexpy import helpers from plexpy import libraries from plexpy import logger from plexpy import plextv from plexpy import session def refresh_users(): logger.info("Tautulli Users :: Requesting users list refresh...") result = plextv.PlexTV().get_full_users_list() server_id = plexpy.CONFIG.PMS_IDENTIFIER if not server_id: logger.error("Tautulli Users :: No PMS identifier, cannot refresh users. Verify server in settings.") return if result: monitor_db = database.MonitorDatabase() # Keep track of user_id to update is_active status user_ids = [0] # Local user always considered active new_users = [] for item in result: if item.get('shared_libraries'): item['shared_libraries'] = ';'.join(item['shared_libraries']) # Only append user if libraries are shared user_ids.append(helpers.cast_to_int(item['user_id'])) elif item.get('server_token'): libs = libraries.Libraries().get_sections() item['shared_libraries'] = ';'.join([str(l['section_id']) for l in libs]) # Only append user if libraries are shared user_ids.append(helpers.cast_to_int(item['user_id'])) keys_dict = {"user_id": item.pop('user_id')} # Check if we've set a custom avatar if so don't overwrite it. if keys_dict['user_id']: avatar_urls = monitor_db.select("SELECT thumb, custom_avatar_url " "FROM users WHERE user_id = ?", [keys_dict['user_id']]) if avatar_urls: if not avatar_urls[0]['custom_avatar_url'] or \ avatar_urls[0]['custom_avatar_url'] == avatar_urls[0]['thumb']: item['custom_avatar_url'] = item['thumb'] else: item['custom_avatar_url'] = item['thumb'] # Check if title is the same as the username if item['title'] == item['username']: item['title'] = None # Check if username is blank (Managed Users) if not item['username']: item['username'] = item['title'] result = monitor_db.upsert('users', key_dict=keys_dict, value_dict=item) if result == 'insert': new_users.append(item['username']) query = "UPDATE users SET is_active = 0 WHERE user_id NOT IN ({})".format(", ".join(["?"] * len(user_ids))) monitor_db.action(query=query, args=user_ids) # Add new users to logger username filter logger.filter_usernames(new_users) logger.info("Tautulli Users :: Users list refreshed.") return True else: logger.warn("Tautulli Users :: Unable to refresh users list.") return False class Users(object): def __init__(self): pass def get_datatables_list(self, kwargs=None, grouping=None): default_return = {'recordsFiltered': 0, 'recordsTotal': 0, 'draw': 0, 'data': []} data_tables = datatables.DataTables() custom_where = [['users.deleted_user', 0]] if grouping is None: grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES if session.get_session_user_id(): 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')]) group_by = 'session_history.reference_id' if grouping else 'session_history.id' columns = ["users.id AS row_id", "users.user_id", "users.username", "(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = '' \ THEN users.username ELSE users.friendly_name END) AS friendly_name", "users.title", "users.email", "users.thumb AS user_thumb", "users.custom_avatar_url AS custom_thumb", "COUNT(DISTINCT %s) AS plays" % group_by, "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 history_row_id", "session_history_metadata.full_title AS last_played", "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_metadata.live", "session_history_metadata.added_at", "session_history_metadata.originally_available_at", "session_history_metadata.guid", "session_history_media_info.transcode_decision", "users.do_notify AS do_notify", "users.keep_history AS keep_history", "users.allow_guest AS allow_guest", "users.is_active AS is_active" ] 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'], join_tables=['session_history', '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("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 = {'row_id': item['row_id'], 'user_id': item['user_id'], 'username': item['username'], 'friendly_name': item['friendly_name'], 'title': item['title'], 'email': item['email'], 'user_thumb': user_thumb, 'plays': item['plays'], 'duration': item['duration'], 'last_seen': item['last_seen'], 'last_played': item['last_played'], 'history_row_id': item['history_row_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'], 'live': item['live'], 'originally_available_at': item['originally_available_at'], 'guid': item['guid'], 'transcode_decision': item['transcode_decision'], 'do_notify': item['do_notify'], 'keep_history': item['keep_history'], 'allow_guest': item['allow_guest'], 'is_active': item['is_active'] } 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': []} 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 AS history_row_id", "MIN(session_history.started) AS first_seen", "MAX(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_metadata.live", "session_history_metadata.added_at", "session_history_metadata.originally_available_at", "session_history_metadata.guid", "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("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 = {'history_row_id': item['history_row_id'], 'last_seen': item['last_seen'], 'first_seen': item['first_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'], 'live': item['live'], 'originally_available_at': item['originally_available_at'], 'guid': item['guid'], '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 set_config(self, user_id=None, friendly_name='', custom_thumb='', do_notify=1, keep_history=1, allow_guest=1): if str(user_id).isdigit(): monitor_db = database.MonitorDatabase() user = monitor_db.select_single('SELECT username FROM users WHERE user_id = ?', [user_id]) if user.get('username') == friendly_name: friendly_name = None key_dict = {'user_id': user_id} value_dict = {'friendly_name': friendly_name, 'custom_avatar_url': custom_thumb, 'do_notify': do_notify, 'keep_history': keep_history, 'allow_guest': allow_guest } try: monitor_db.upsert('users', value_dict, key_dict) except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for set_config: %s." % e) def get_details(self, user_id=None, user=None, email=None, include_last_seen=False): default_return = {'row_id': 0, 'user_id': 0, 'username': 'Local', 'friendly_name': 'Local', 'user_thumb': common.DEFAULT_USER_THUMB, 'email': '', 'is_active': 1, 'is_admin': '', 'is_home_user': 0, 'is_allow_sync': 0, 'is_restricted': 0, 'do_notify': 0, 'keep_history': 1, 'allow_guest': 0, 'deleted_user': 0, 'shared_libraries': (), 'last_seen': None } if user_id in (None, '') and not user and not email: return default_return user_details = self.get_user_details(user_id=user_id, user=user, email=email, include_last_seen=include_last_seen) if user_details: return user_details else: logger.warn("Tautulli Users :: Unable to retrieve user %s from database. Requesting user list refresh." % user_id if user_id else user) # Let's first refresh the user list to make sure the user isn't newly added and not in the db yet refresh_users() user_details = self.get_user_details(user_id=user_id, user=user, email=email, include_last_seen=include_last_seen) if user_details: return user_details else: logger.warn("Tautulli Users :: Unable to retrieve user %s from database. Returning 'Local' user." % user_id if user_id else user) # If there is no user data we must return something # Use "Local" user to retain compatibility with PlexWatch database value return default_return def get_user_details(self, user_id=None, user=None, email=None, include_last_seen=False): last_seen = 'NULL' join = '' if include_last_seen: last_seen = "MAX(session_history.started)" join = "LEFT OUTER JOIN session_history ON users.user_id = session_history.user_id" monitor_db = database.MonitorDatabase() try: if str(user_id).isdigit(): where = "users.user_id = ?" args = [user_id] elif user: where = "users.username = ?" args = [user] elif email: where = "users.email = ?" args = [email] else: raise Exception("Missing user_id, username, or email") query = "SELECT users.id AS row_id, users.user_id, username, friendly_name, " \ "thumb AS user_thumb, custom_avatar_url AS custom_thumb, " \ "email, is_active, is_admin, is_home_user, is_allow_sync, is_restricted, " \ "do_notify, keep_history, deleted_user, " \ "allow_guest, shared_libraries, %s AS last_seen " \ "FROM users %s " \ "WHERE %s COLLATE NOCASE" % (last_seen, join, where) result = monitor_db.select(query, args=args) except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for get_user_details: %s." % e) result = [] user_details = {} if result: for item in result: if session.get_session_user_id(): friendly_name = session.get_session_user() elif item['friendly_name']: friendly_name = item['friendly_name'] else: friendly_name = item['username'] 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 shared_libraries = tuple(item['shared_libraries'].split(';')) if item['shared_libraries'] else () user_details = {'row_id': item['row_id'], 'user_id': item['user_id'], 'username': item['username'], 'friendly_name': friendly_name, 'user_thumb': user_thumb, 'email': item['email'], 'is_active': item['is_active'], 'is_admin': item['is_admin'], 'is_home_user': item['is_home_user'], 'is_allow_sync': item['is_allow_sync'], 'is_restricted': item['is_restricted'], 'do_notify': item['do_notify'], 'keep_history': item['keep_history'], 'deleted_user': item['deleted_user'], 'allow_guest': item['allow_guest'], 'shared_libraries': shared_libraries, 'last_seen': item['last_seen'] } return user_details def get_watch_time_stats(self, user_id=None, grouping=None, query_days=None): if not session.allow_session_user(user_id): return [] if grouping is None: grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES if query_days and query_days is not None: query_days = map(helpers.cast_to_int, str(query_days).split(',')) else: query_days = [1, 7, 30, 0] timestamp = helpers.timestamp() monitor_db = database.MonitorDatabase() user_watch_time_stats = [] group_by = 'reference_id' if grouping else 'id' for days in query_days: timestamp_query = timestamp - days * 24 * 60 * 60 try: if days > 0: if str(user_id).isdigit(): query = "SELECT (SUM(stopped - started) - " \ " SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, " \ "COUNT(DISTINCT %s) AS total_plays " \ "FROM session_history " \ "WHERE stopped >= %s " \ "AND user_id = ? " % (group_by, timestamp_query) result = monitor_db.select(query, args=[user_id]) else: result = [] else: if str(user_id).isdigit(): query = "SELECT (SUM(stopped - started) - " \ " SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, " \ "COUNT(DISTINCT %s) AS total_plays " \ "FROM session_history " \ "WHERE user_id = ? " % group_by result = monitor_db.select(query, args=[user_id]) else: result = [] except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for get_watch_time_stats: %s." % e) result = [] for item in result: if item['total_time']: total_time = item['total_time'] total_plays = item['total_plays'] else: total_time = 0 total_plays = 0 row = {'query_days': days, 'total_time': total_time, 'total_plays': total_plays } user_watch_time_stats.append(row) return user_watch_time_stats def get_player_stats(self, user_id=None, grouping=None): if not session.allow_session_user(user_id): return [] if grouping is None: grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES monitor_db = database.MonitorDatabase() player_stats = [] result_id = 0 group_by = 'reference_id' if grouping else 'id' try: if str(user_id).isdigit(): query = "SELECT player, COUNT(DISTINCT %s) as total_plays, (SUM(stopped - started) - " \ "SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, " \ "platform " \ "FROM session_history " \ "WHERE user_id = ? " \ "GROUP BY player " \ "ORDER BY total_plays DESC, total_time DESC" % group_by result = monitor_db.select(query, args=[user_id]) else: result = [] except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for get_player_stats: %s." % e) result = [] for item in result: # Rename Mystery platform names platform = common.PLATFORM_NAME_OVERRIDES.get(item['platform'], item['platform']) platform_name = next((v for k, v in common.PLATFORM_NAMES.items() if k in platform.lower()), 'default') row = {'player_name': item['player'], 'platform': platform, 'platform_name': platform_name, 'total_plays': item['total_plays'], 'total_time': item['total_time'], 'result_id': result_id } player_stats.append(row) result_id += 1 return player_stats def get_recently_watched(self, user_id=None, limit='10'): if not session.allow_session_user(user_id): return [] monitor_db = database.MonitorDatabase() recently_watched = [] if not limit.isdigit(): limit = '10' try: if str(user_id).isdigit(): query = "SELECT session_history.id, session_history.media_type, guid, " \ "session_history.rating_key, session_history.parent_rating_key, session_history.grandparent_rating_key, " \ "title, parent_title, grandparent_title, original_title, " \ "thumb, parent_thumb, grandparent_thumb, media_index, parent_media_index, " \ "year, originally_available_at, added_at, live, started, user " \ "FROM session_history_metadata " \ "JOIN session_history ON session_history_metadata.id = session_history.id " \ "WHERE user_id = ? " \ "GROUP BY (CASE WHEN session_history.media_type = 'track' THEN session_history.parent_rating_key " \ " ELSE session_history.rating_key END) " \ "ORDER BY MAX(started) DESC LIMIT ?" result = monitor_db.select(query, args=[user_id, limit]) else: result = [] except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for get_recently_watched: %s." % e) result = [] for row in result: if row['media_type'] == 'episode' and row['parent_thumb']: thumb = row['parent_thumb'] elif row['media_type'] == 'episode': thumb = row['grandparent_thumb'] else: thumb = row['thumb'] recent_output = {'row_id': row['id'], 'media_type': row['media_type'], 'rating_key': row['rating_key'], 'parent_rating_key': row['parent_rating_key'], 'grandparent_rating_key': row['grandparent_rating_key'], 'title': row['title'], 'parent_title': row['parent_title'], 'grandparent_title': row['grandparent_title'], 'original_title': row['original_title'], 'thumb': thumb, 'media_index': row['media_index'], 'parent_media_index': row['parent_media_index'], 'year': row['year'], 'originally_available_at': row['originally_available_at'], 'live': row['live'], 'guid': row['guid'], 'time': row['started'], 'user': row['user'] } recently_watched.append(recent_output) return recently_watched def get_users(self, include_deleted=False): monitor_db = database.MonitorDatabase() where = '' if include_deleted else 'WHERE deleted_user = 0' try: query = "SELECT id AS row_id, user_id, username, friendly_name, thumb, custom_avatar_url, email, " \ "is_active, is_admin, is_home_user, is_allow_sync, is_restricted, " \ "do_notify, keep_history, allow_guest, shared_libraries, " \ "filter_all, filter_movies, filter_tv, filter_music, filter_photos " \ "FROM users %s" % where result = monitor_db.select(query=query) except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for get_users: %s." % e) return [] users = [] for item in result: shared_libraries = tuple(item['shared_libraries'].split(';')) if item['shared_libraries'] else () user = {'row_id': item['row_id'], 'user_id': item['user_id'], 'username': item['username'], 'friendly_name': item['friendly_name'] or item['username'], 'thumb': item['custom_avatar_url'] or item['thumb'], 'email': item['email'], 'is_active': item['is_active'], 'is_admin': item['is_admin'], 'is_home_user': item['is_home_user'], 'is_allow_sync': item['is_allow_sync'], 'is_restricted': item['is_restricted'], 'do_notify': item['do_notify'], 'keep_history': item['keep_history'], 'allow_guest': item['allow_guest'], 'shared_libraries': shared_libraries, 'filter_all': item['filter_all'], 'filter_movies': item['filter_movies'], 'filter_tv': item['filter_tv'], 'filter_music': item['filter_music'], 'filter_photos': item['filter_photos'], } users.append(user) return users def delete(self, user_id=None, row_ids=None, purge_only=False): monitor_db = database.MonitorDatabase() if row_ids and row_ids is not None: row_ids = list(map(helpers.cast_to_int, row_ids.split(','))) # Get the user_ids corresponding to the row_ids result = monitor_db.select("SELECT user_id FROM users " "WHERE id IN ({})".format(",".join(["?"] * len(row_ids))), row_ids) success = [] for user in result: success.append(self.delete(user_id=user['user_id'], purge_only=purge_only)) return all(success) elif str(user_id).isdigit(): delete_success = database.delete_user_history(user_id=user_id) if purge_only: return delete_success else: logger.info("Tautulli Users :: Deleting user with user_id %s from database." % user_id) try: monitor_db.action("UPDATE users " "SET deleted_user = 1, keep_history = 0, do_notify = 0 " "WHERE user_id = ?", [user_id]) return delete_success except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for delete: %s." % e) else: return False def undelete(self, user_id=None, username=None): monitor_db = database.MonitorDatabase() try: if user_id and str(user_id).isdigit(): query = "SELECT * FROM users WHERE user_id = ?" result = monitor_db.select(query=query, args=[user_id]) if result: logger.info("Tautulli Users :: Re-adding user with id %s to database." % user_id) monitor_db.action("UPDATE users " "SET deleted_user = 0, keep_history = 1, do_notify = 1 " "WHERE user_id = ?", [user_id]) return True else: return False elif username: query = "SELECT * FROM users WHERE username = ?" result = monitor_db.select(query=query, args=[username]) if result: logger.info("Tautulli Users :: Re-adding user with username %s to database." % username) monitor_db.action("UPDATE users " "SET deleted_user = 0, keep_history = 1, do_notify = 1 " "WHERE username = ?", [username]) return True else: return False except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for undelete: %s." % e) # Keep method for PlexWatch/Plexivity import def get_user_id(self, user=None): if user: try: monitor_db = database.MonitorDatabase() query = "SELECT user_id FROM users WHERE username = ?" result = monitor_db.select_single(query, args=[user]) if result: return result['user_id'] else: return None except: return None return None def get_user_names(self, kwargs=None): monitor_db = database.MonitorDatabase() user_cond = '' if session.get_session_user_id(): 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("Tautulli Users :: Unable to execute database query for get_user_names: %s." % e) return None return session.friendly_name_to_username(result) def get_tokens(self, user_id=None): tokens = { 'allow_guest': 0, 'user_token': '', 'server_token': '' } if user_id: try: monitor_db = database.MonitorDatabase() query = "SELECT allow_guest, user_token, server_token FROM users " \ "WHERE user_id = ? AND deleted_user = 0" result = monitor_db.select_single(query, args=[user_id]) if result: tokens = {'allow_guest': result['allow_guest'], 'user_token': result['user_token'], 'server_token': result['server_token'] } return tokens else: return tokens except: return tokens return tokens def get_filters(self, user_id=None): if not user_id: return {} try: monitor_db = database.MonitorDatabase() query = "SELECT filter_all, filter_movies, filter_tv, filter_music, filter_photos FROM users " \ "WHERE user_id = ?" result = monitor_db.select_single(query, args=[user_id]) except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for get_filters: %s." % e) result = {} filters_list = {} for k, v in result.items(): filters = {} for f in v.split('|'): if 'contentRating=' in f or 'label=' in f: filters.update(dict(parse_qsl(f))) filters['content_rating'] = tuple(f for f in filters.pop('contentRating', '').split(',') if f) filters['labels'] = tuple(f for f in filters.pop('label', '').split(',') if f) filters_list[k] = filters return filters_list def set_user_login(self, user_id=None, user=None, user_group=None, ip_address=None, host=None, user_agent=None, success=0, expiry=None, jwt_token=None): if user_id is None or str(user_id).isdigit(): monitor_db = database.MonitorDatabase() if expiry is not None: expiry = helpers.datetime_to_iso(expiry) keys = {'timestamp': helpers.timestamp(), 'user_id': user_id} values = {'user': user, 'user_group': user_group, 'ip_address': ip_address, 'host': host, 'user_agent': user_agent, 'success': success, 'expiry': expiry, 'jwt_token': jwt_token} try: monitor_db.upsert(table_name='user_login', key_dict=keys, value_dict=values) except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for set_login_log: %s." % e) def get_user_login(self, jwt_token): monitor_db = database.MonitorDatabase() result = monitor_db.select_single("SELECT * FROM user_login " "WHERE jwt_token = ?", [jwt_token]) return result def clear_user_login_token(self, jwt_token=None, row_ids=None): monitor_db = database.MonitorDatabase() if jwt_token: logger.debug("Tautulli Users :: Clearing user JWT token.") try: monitor_db.action("UPDATE user_login SET jwt_token = NULL " "WHERE jwt_token = ?", [jwt_token]) except Exception as e: logger.error("Tautulli Users :: Unable to clear user JWT token: %s.", e) return False elif row_ids and row_ids is not None: row_ids = list(map(helpers.cast_to_int, row_ids.split(','))) logger.debug("Tautulli Users :: Clearing JWT tokens for row_ids %s.", row_ids) try: monitor_db.action("UPDATE user_login SET jwt_token = NULL " "WHERE id in ({})".format(",".join(["?"] * len(row_ids))), row_ids) except Exception as e: logger.error("Tautulli Users :: Unable to clear JWT tokens: %s.", e) return False return True def get_datatables_user_login(self, user_id=None, jwt_token=None, kwargs=None): default_return = {'recordsFiltered': 0, 'recordsTotal': 0, 'draw': 0, 'data': []} if not session.allow_session_user(user_id): return default_return data_tables = datatables.DataTables() if session.get_session_user_id(): 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.id AS row_id", "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", "user_login.expiry", "user_login.jwt_token", "(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("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']) expiry = None current = False if item['jwt_token'] and item['expiry']: _expiry = helpers.iso_to_datetime(item['expiry']) if _expiry > arrow.now(): expiry = _expiry.strftime('%Y-%m-%d %H:%M:%S') current = (item['jwt_token'] == jwt_token) row = {'row_id': item['row_id'], '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'], 'expiry': expiry, 'current': current, '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 def delete_login_log(self): monitor_db = database.MonitorDatabase() try: logger.info("Tautulli Users :: Clearing login logs from database.") monitor_db.action('DELETE FROM user_login') monitor_db.action('VACUUM') return True except Exception as e: logger.warn("Tautulli Users :: Unable to execute database query for delete_login_log: %s." % e) return False