def create_report(auth_token, payload): db_session = db_connection().get_session() if "is_default" in payload: result = [] db_session = db_connection().get_session() datasource_id = payload["datasource_id"] login_user = db_utils.get_user_session(auth_token).email domain_id = db_session.query(DataSource).filter( DataSource.datasource_id == datasource_id).first().domain_id reports = default_reports.default_reports datasource_type = db_utils.get_datasource( datasource_id).datasource_type default_datasource_reports = datasource_to_default_report_map[ datasource_type] if default_datasource_reports: reports.extend(default_datasource_reports) for report in reports: existing_report = db_session.query(Report).filter( Report.domain_id == domain_id, Report.name == report["name"]).first() if not existing_report: report["receivers"] = login_user report["datasource_id"] = datasource_id result.append( insert_entry_into_report_table(db_session, auth_token, report)) return result else: return [ insert_entry_into_report_table(db_session, auth_token, payload) ]
def fetch_alerts_count(auth_token): db_session = db_connection().get_session() existing_user = db_utils.get_user_session(auth_token, db_session=db_session) alerts_count = db_session.query(Alert).filter( DataSource.domain_id == existing_user.domain_id, Alert.datasource_id == DataSource.datasource_id, Alert.isOpen == True).count() return alerts_count
def get_user_session(auth_token): db_session = db_connection().get_session() user_session = db_utils.get_user_session(auth_token, db_session) db_session.query(LoginUser).filter( LoginUser.auth_token == auth_token).update( {"last_login_time": datetime.datetime.utcnow()}) db_connection().commit() user_session = json.loads(json.dumps(user_session, cls=alchemy_encoder())) user_session["refresh_token"] = "" user_session["token"] = "" return user_session
def get_alerts(auth_token): db_session = db_connection().get_session() existing_user = db_utils.get_user_session(auth_token, db_session=db_session) alerts = db_session.query(Alert).filter( DataSource.domain_id == existing_user.domain_id, Alert.datasource_id == DataSource.datasource_id) alerts_obj = alerts.order_by(Alert.last_updated.desc()).limit(100).all() alerts.update({"isOpen": False}, synchronize_session='fetch') db_connection().commit() return alerts_obj
def get_activities_for_user(auth_token, user_email, start_time=None): if not auth_token: Logger().info("get_activities_for_user : auth_token is not present") return None db_session = db_connection().get_session() login_user = db_utils.get_user_session(auth_token, db_session) admin_user = login_user if not login_user.is_admin: datasource = db_session.query(DataSource).filter( and_( DataSource.domain_id == admin_user.domain_id, DataSource.datasource_type == constants.ConnectorTypes.GSUITE.value)).first() admin_user = db_session.query(DomainUser).filter( and_( DomainUser.is_admin == True, DomainUser.datasource_id == datasource.datasource_id)).first() results = [] if auth_token and auth_token == constants.INTERNAL_SECRET: reports_service = gutils.get_gdrive_reports_service( None, admin_user.email) if not start_time: start_time = datetime.today() - timedelta(days=7) start_time_string = start_time.isoformat("T") + "Z" results = reports_service.activities().list( userKey=user_email, applicationName='drive', maxResults=50, startTime=start_time_string).execute() else: if not login_user.is_admin and login_user.is_serviceaccount_enabled and login_user.email != user_email: return None reports_service = gutils.get_gdrive_reports_service( auth_token, admin_user.email) results = reports_service.activities().list(userKey=user_email, applicationName='drive', maxResults=50).execute() payload = process_user_activity(user_email, results) return payload
def get_policies(auth_token): db_session = db_connection().get_session() existing_user = db_utils.get_user_session(auth_token, db_session=db_session) user_domain_id = existing_user.domain_id is_admin = existing_user.is_admin is_service_account_is_enabled = existing_user.is_serviceaccount_enabled if is_service_account_is_enabled and is_admin: policies = db_session.query(Policy).filter( and_(DataSource.domain_id == user_domain_id, Policy.datasource_id == DataSource.datasource_id)).all() else: policies = db_session.query(Policy).filter( and_(DataSource.domain_id == user_domain_id, Policy.datasource_id == DataSource.datasource_id, Policy.created_by == existing_user.email)).all() return policies
def get_users_list(auth_token, full_name=None, email=None, member_type=None, datasource_id=None, sort_column=None, sort_order=None, is_admin=None, type=None, page_number=0): db_session = db_connection().get_session() login_user = db_utils.get_user_session(auth_token) user_domain_id = login_user.domain_id login_user_email = login_user.email is_login_user_admin = login_user.is_admin is_service_account_is_enabled = login_user.is_serviceaccount_enabled domain_datasource_ids = [] if not datasource_id: datasources = db_session.query(DataSource).filter( DataSource.domain_id == user_domain_id).all() for ds in datasources: domain_datasource_ids.append(ds.datasource_id) else: domain_datasource_ids = [datasource_id] users_query = db_session.query(DomainUser).filter(DomainUser.datasource_id.in_(domain_datasource_ids)) shared_files_with_external_users = [] if is_service_account_is_enabled and not is_login_user_admin: shared_files_with_external_users = users_query.filter(and_(Resource.datasource_id == ResourcePermission.datasource_id, Resource.resource_owner_id == login_user_email, Resource.resource_id == ResourcePermission.resource_id, ResourcePermission.datasource_id == DomainUser.datasource_id, ResourcePermission.email == DomainUser.email, DomainUser.member_type == constants.EntityExposureType.EXTERNAL.value)) shared_files_with_external_users = filter_on_get_user_list(shared_files_with_external_users, full_name, email, member_type, datasource_id, sort_column, sort_order, is_admin, type, page_number) shared_files_with_external_users = shared_files_with_external_users.all() users_query = users_query.filter(DomainUser.email == login_user_email) users_query = filter_on_get_user_list(users_query, full_name, email, member_type, datasource_id, sort_column, sort_order, is_admin, type, page_number) users_list = users_query.all() return users_list + shared_files_with_external_users
def create_user_payload_for_nonadmin_nonserviceaccount(auth_token): user_info = db_utils.get_user_session(auth_token) results = { "users": [{ "primaryEmail": user_info.email, "name": { "givenName": user_info.first_name, "familyName": user_info.last_name, "fullName": user_info.first_name + " " + user_info.last_name }, "isAdmin": False, "creationTime": str(datetime.datetime.utcnow()), "suspended": False, "id": user_info.email, "thumbnailPhotoUrl": "", "aliases": [], "customerId": "", "lastLoginTime": str(datetime.datetime.utcnow()) }] } return results
def create_policy(auth_token, payload): db_session = db_connection().get_session() datasource_id = payload["datasource_id"] if "is_default" in payload: login_user = db_utils.get_user_session(auth_token).email datasource_obj = get_datasource(datasource_id) datasource_type = datasource_obj.datasource_type db_session = db_connection().get_session() default_policies = datasource_to_default_policy_map[datasource_type] for policy in default_policies: existing_policy = db_session.query(Policy).filter( and_(Policy.datasource_id == datasource_id, Policy.name == policy["name"])).first() if not existing_policy: policy['datasource_id'] = datasource_id if len(policy["actions"]) > 0: policy["actions"][0]["config"]["to"] = login_user policy["created_by"] = login_user insert_entry_into_policy_table(db_session, policy) return else: return insert_entry_into_policy_table(db_session, payload)
def get_users_for_app(auth_token, domain_id, app_id, sort_column_name, sort_order): db_session = db_connection().get_session() # check for non-admin user existing_user = db_utils.get_user_session(auth_token) is_admin = existing_user.is_admin is_service_account_is_enabled = existing_user.is_serviceaccount_enabled login_user_email = existing_user.email datasource_ids = db_session.query(DataSource.datasource_id).filter(DataSource.domain_id == domain_id).all() datasource_ids = [r for r, in datasource_ids] # if servie account and non-admin user, show permission for logged in user only if is_service_account_is_enabled and not is_admin: apps_query = db_session.query(DomainUser).filter(ApplicationUserAssociation.application_id == app_id, DomainUser.email == ApplicationUserAssociation.user_email, ApplicationUserAssociation.user_email == login_user_email, DomainUser.datasource_id == ApplicationUserAssociation.datasource_id, DomainUser.member_type == constants.EntityExposureType.INTERNAL.value) else: apps_query = db_session.query(DomainUser).filter(ApplicationUserAssociation.application_id == app_id, DomainUser.email == ApplicationUserAssociation.user_email, DomainUser.datasource_id == ApplicationUserAssociation.datasource_id, DomainUser.member_type == constants.EntityExposureType.INTERNAL.value) if sort_column_name == "last_login": if sort_order == 'desc': apps_query = apps_query.order_by(DomainUser.last_login_time.desc()) else: apps_query = apps_query.order_by(DomainUser.last_login_time.asc()) apps_query_data = apps_query.all() return apps_query_data
def write_to_csv(auth_token, payload): source = payload["sourceType"] name = payload["resourceName"] type = payload["resourceType"] owner = payload["ownerEmailId"] exposure_type = payload["exposureType"] parent_folder = payload["parentFolder"] modified_date = payload["selectedDate"] logged_in_user = payload["logged_in_user"] selected_fields = payload['selectedFields'] db_session = db_connection().get_session() existing_user = db_utils.get_user_session(auth_token) domain_id = existing_user.domain_id resources_query, resource_alias, parent_alias = fetch_filtered_resources( db_session, auth_token, exposure_type=exposure_type, resource_type=type, prefix=name, owner_email_id=owner, parent_folder=parent_folder, selected_date=modified_date, source_type=source) column_fields = [] column_headers = [] if 'source_type' in selected_fields: column_fields.append(DataSource.datasource_type) column_headers.append("Source") if 'resource_name' in selected_fields: column_fields.append(resource_alias.resource_name) column_headers.append("Name") if 'resource_type' in selected_fields: column_fields.append(resource_alias.resource_type) column_headers.append("Type") if 'resource_owner_id' in selected_fields: column_fields.append(resource_alias.resource_owner_id) column_headers.append("Owner") if 'exposure_type' in selected_fields: column_fields.append(resource_alias.exposure_type) column_headers.append("Exposure Type") if 'parent_name' in selected_fields: column_fields.append(parent_alias.resource_name) column_headers.append("Parent Folder") if 'last_modified_time' in selected_fields: column_fields.append(resource_alias.last_modified_time) column_headers.append("Modified On or Before") resources = resources_query.with_entities(*column_fields).filter( DataSource.datasource_id == resource_alias.datasource_id).all() temp_csv = utils.convert_data_to_csv(resources, column_headers) bucket_name = "adyaapp-" + constants.DEPLOYMENT_ENV + "-data" now = datetime.strftime(datetime.utcnow(), "%Y-%m-%d-%H-%M-%S") #now = str(datetime.utcnow()) key = domain_id + "/export/resource-" + now + ".csv" temp_url = aws_utils.upload_file_in_s3_bucket(bucket_name, key, temp_csv) if temp_url: email_subject = "[Adya] Your requested report is ready for download" link = "<a href=" + temp_url + ">link</a>" email_head = "<p>Hi " + existing_user.first_name + ",</p></br></br>" email_body = "<p>Your requested report is now ready for download from this " + link + ".</p></br></br><p>In case of any questions, please send a mail to [email protected]</p></br></br>" email_signature = "<p>Thanks,</p></br><p>Team Adya</p>" rendered_html = email_head + email_body + email_signature aws_utils.send_email([logged_in_user], email_subject, rendered_html) # adya_emails.send_csv_export_email(logged_in_user, domain_id, temp_url) else: Logger().exception( "Failed to generate url. Please contact administrator")
def fetch_filtered_resources(db_session, auth_token, accessible_by=None, exposure_type='EXT', resource_type='None', prefix='', owner_email_id=None, parent_folder=None, selected_date=None, sort_column_name=None, sort_type=None, datasource_id=None, source_type=None): #db_session = db_connection().get_session() existing_user = db_utils.get_user_session(auth_token) user_domain_id = existing_user.domain_id loggged_in_user_email = existing_user.email is_admin = existing_user.is_admin domain_datasource_ids = [] if datasource_id: domain_datasource_ids = [datasource_id] else: domain_datasource_ids = db_session.query( DataSource.datasource_id).filter( DataSource.domain_id == user_domain_id).all() domain_datasource_ids = [r for r, in domain_datasource_ids] # resources = [] resource_alias = aliased(Resource) parent_alias = aliased(Resource) resources_query = db_session.query( resource_alias, parent_alias.resource_name).outerjoin( parent_alias, and_(resource_alias.parent_id == parent_alias.resource_id, resource_alias.datasource_id == parent_alias.datasource_id)) if source_type: resources_query = resources_query.filter( resource_alias.datasource_id == source_type) if accessible_by and not owner_email_id: users_info = db_session.query(DomainUser).filter( and_(DomainUser.datasource_id.in_(domain_datasource_ids), DomainUser.email == accessible_by)).all() parent_ids = [] for user in users_info: for group in user.groups: parent_ids.append(group.email) email_list = parent_ids + [accessible_by] resource_ids = db_session.query(ResourcePermission.resource_id).filter( and_(ResourcePermission.datasource_id.in_(domain_datasource_ids), ResourcePermission.email.in_(email_list))) resources_query = resources_query.filter( resource_alias.resource_id.in_(resource_ids)) resources_query = resources_query.filter( resource_alias.resource_owner_id != accessible_by) if selected_date: resources_query = resources_query.filter( resource_alias.last_modified_time <= selected_date) if parent_folder: resources_query = resources_query.filter( parent_alias.resource_name == parent_folder) if owner_email_id: resources_query = resources_query.filter( resource_alias.resource_owner_id.ilike("%" + owner_email_id + "%")) if resource_type: resources_query = resources_query.filter( resource_alias.resource_type == resource_type) if exposure_type: resources_query = resources_query.filter( resource_alias.exposure_type == exposure_type) if prefix: resources_query = resources_query.filter( resource_alias.resource_name.ilike("%" + prefix + "%")) if not is_admin: resources_query = resources_query.filter( resource_alias.resource_owner_id == loggged_in_user_email) resources_query = resources_query.filter( resource_alias.datasource_id.in_(domain_datasource_ids)) if not sort_column_name: sort_column_name = "last_modified_time" sort_column_obj = None if sort_column_name == "last_modified_time": sort_column_obj = resource_alias.last_modified_time elif sort_column_name == "resource_owner_id": sort_column_obj = resource_alias.resource_owner_id elif sort_column_name == "resource_name": sort_column_obj = resource_alias.resource_name elif sort_column_name == "exposure_type": sort_column_obj = resource_alias.exposure_type elif sort_column_name == "resource_type": sort_column_obj = resource_alias.resource_type elif sort_column_name == "datasource_id": sort_column_obj = resource_alias.datasource_id if sort_column_obj: if sort_type == "asc": sort_column_obj = sort_column_obj.asc() else: sort_column_obj = sort_column_obj.desc() resources_query = resources_query.order_by(sort_column_obj) return resources_query, resource_alias, parent_alias
def subscribe(auth_token, domain_id, datasource_id, page_num): page_num = page_num if page_num else 0 db_session = db_connection().get_session() datasource = db_session.query(DataSource).filter( DataSource.datasource_id == datasource_id).first() if not datasource: return is_local_deployment = constants.DEPLOYMENT_ENV == "local" if page_num == 0: if not is_local_deployment: # set up a resubscribe handler that runs every 6 hours cron(0 0/6 ? * * *) aws_utils.create_cloudwatch_event( "handle_channel_expiration", "cron(0 0/6 ? * * *)", aws_utils.get_lambda_name( "get", urls.HANDLE_GDRIVE_CHANNEL_EXPIRATION_PATH, "gsuite")) # since we dont always get notification for changes, adding an event which will run every hour and check for drive changes aws_utils.create_cloudwatch_event( "gdrive_periodic_changes_poll", "cron(0 0/1 * * ? *)", aws_utils.get_lambda_name("get", urls.GDRIVE_PERIODIC_CHANGES_POLL, "gsuite")) db_session.query(PushNotificationsSubscription).filter( PushNotificationsSubscription.datasource_id == datasource_id).delete() #check for admin user existing_user = db_utils.get_user_session(auth_token) login_user_email = existing_user.email is_admin = existing_user.is_admin is_service_account_is_enabled = existing_user.is_serviceaccount_enabled if is_service_account_is_enabled and is_admin: #Try subscribing for various activity notifications activities_to_track = [ constants.GSuiteNotificationType.DRIVE_ACTIVITY.value, constants.GSuiteNotificationType.ADMIN_ACTIVITY.value, constants.GSuiteNotificationType.TOKEN_ACTIVITY.value, constants.GSuiteNotificationType.LOGIN_ACTIVITY.value ] for activity in activities_to_track: subscription = prepare_new_subscription( datasource, login_user_email) subscription.notification_type = activity _subscribe_for_activity(db_session, subscription, is_local_deployment) db_session.add(subscription) #Try subscribing for drive change notifications if datasource.is_serviceaccount_enabled: domain_users = db_session.query(DomainUser).filter( and_(DomainUser.datasource_id == datasource.datasource_id, DomainUser.type == constants.DirectoryEntityType.USER.value, DomainUser.member_type == 'INT')).offset(page_num * 25).limit(25).all() for user in domain_users: subscription = prepare_new_subscription(datasource, user.email) subscription.drive_root_id = "SVC" _subscribe_for_drive_change(db_session, auth_token, subscription, is_local_deployment) db_session.add(subscription) db_connection().commit() #If there are more users, call the api again with next page number if len(domain_users) == 25: query_params = { 'domainId': domain_id, 'dataSourceId': datasource_id, 'pageNum': page_num + 1 } messaging.trigger_post_event( urls.SUBSCRIBE_GDRIVE_NOTIFICATIONS_PATH, auth_token, query_params, {}, "gsuite") elif page_num == 0: Logger().info( "Service account is not enabled, subscribing for push notification using logged in user's creds" ) subscription = prepare_new_subscription(datasource, login_user_email) subscription.drive_root_id = "" _subscribe_for_drive_change(db_session, auth_token, subscription, is_local_deployment) db_session.add(subscription) db_connection().commit()
def write_to_csv(auth_token, payload): source = payload["datasource_id"] type = payload["type"] name = payload["full_name"] email = payload["email"] is_admin = payload["is_admin"] member_type = payload["member_type"] logged_in_user = payload["logged_in_user"] selected_fields = payload['selectedFields'] db_session = db_connection().get_session() existing_user = db_utils.get_user_session(auth_token) domain_id = existing_user.domain_id domain_datasource_ids = [] if not source: datasources = db_session.query(DataSource).filter(DataSource.domain_id == domain_id).all() for datasource in datasources: domain_datasource_ids.append(datasource.datasource_id) else: domain_datasource_ids = [source] users_query = db_session.query(DomainUser).join(DataSource).filter(DomainUser.datasource_id.in_(domain_datasource_ids)) users_query = filter_on_get_user_list(users_query, full_name=name, email=email, member_type=member_type, datasource_id=source, is_admin=is_admin, type=type) column_fields = [] column_headers = [] if 'datasource_id' in selected_fields: column_fields.append(DataSource.datasource_type) column_headers.append('Source') if 'full_name' in selected_fields: column_name = case([(DomainUser.full_name != None, DomainUser.full_name),], else_ = DomainUser.first_name + " " + DomainUser.last_name) column_fields.append(column_name) column_headers.append('Name') if 'email' in selected_fields: column_fields.append(DomainUser.email) column_headers.append('Email') if '' in selected_fields: column_fields.append(DomainUser.photo_url) column_headers.append('Avatar') if 'type' in selected_fields: column_fields.append(DomainUser.type) column_headers.append('Type') if 'last_login' in selected_fields: column_fields.append(DomainUser.last_login_time) column_headers.append('Last Login') if 'is_admin' in selected_fields: column_fields.append(DomainUser.is_admin) column_headers.append('Is Admin') if 'member_type' in selected_fields: column_fields.append(DomainUser.member_type) column_headers.append('Exposure Type') users = users_query.with_entities(*column_fields).all() temp_csv = utils.convert_data_to_csv(users, column_headers) bucket_name = "adyaapp-" + constants.DEPLOYMENT_ENV + "-data" now = datetime.strftime(datetime.utcnow(), "%Y-%m-%d-%H-%M-%S") key = domain_id + "/export/user-" + now + ".csv" temp_url = aws_utils.upload_file_in_s3_bucket(bucket_name, key, temp_csv) if temp_url: email_subject = "[Adya] Your requested report is ready for download" link = "<a href=" + temp_url + ">link</a>" email_head = "<p>Hi " + existing_user.first_name + ",</p></br></br>" email_body = "<p>Your requested report is now ready for download from this " + link + ".</p></br></br><p>In case of any questions, please send a mail to [email protected]</p></br></br>" email_signature = "<p>Thanks,</p></br><p>Team Adya</p>" rendered_html = email_head + email_body + email_signature aws_utils.send_email([logged_in_user], email_subject, rendered_html) else: Logger().exception("Failed to generate url. Please contact administrator")
def remove_all_permissions_for_user(auth_token, domain_id, datasource_id, user_email, initiated_by, log_entry, action_key): db_session = db_connection().get_session() login_user = db_utils.get_user_session(auth_token) login_user_email = login_user.email is_admin = login_user.is_admin is_service_account_is_enabled = login_user.is_serviceaccount_enabled resource_permissions = db_session.query(ResourcePermission).filter( and_(ResourcePermission.datasource_id == datasource_id, ResourcePermission.email == user_email, ResourcePermission.permission_type != "owner", Resource.datasource_id == ResourcePermission.datasource_id, Resource.resource_id == ResourcePermission.resource_id)) if is_service_account_is_enabled and not is_admin: resource_permissions = resource_permissions.filter( and_(Resource.resource_owner_id == login_user_email)) if not log_entry.total_count: log_entry.total_count = resource_permissions.count() db_connection().commit() Logger().info( "remove_all_permissions_for_user : retrieve permission for a user from db" ) resource_permissions = resource_permissions.order_by( Resource.resource_owner_id.asc()).limit(BATCH_COUNT).all() more_to_execute = False if len(resource_permissions) == BATCH_COUNT: more_to_execute = True Logger().info( "remove_all_permissions_for_user : form a permission payload for each owner" ) permissions_to_update_by_resource_owner = {} for permission in resource_permissions: owner = permission.resource.resource_owner_id if owner in permissions_to_update_by_resource_owner: permissions_to_update_by_resource_owner[owner].append(permission) else: permissions_to_update_by_resource_owner[owner] = [permission] if len(permissions_to_update_by_resource_owner) < 1: log_entry.message = 'Action completed successfully' log_entry.status = 'SUCCESS' return response_messages.ResponseMessage( 200, 'Action completed : Nothing to update') response = response_messages.ResponseMessage( 200, 'Action submitted successfully') for owner in permissions_to_update_by_resource_owner: permissions_to_update = permissions_to_update_by_resource_owner[owner] Logger().info( "remove_all_permissions_for_user : call execute_batch_delete function" ) response = execute_batch_delete(auth_token, datasource_id, owner, initiated_by, permissions_to_update, log_entry, action_key, more_to_execute) return response
def get_widget_data(auth_token, widget_id, datasource_id=None, user_email=None, event_filters=None): if not (auth_token or datasource_id): return None db_session = db_connection().get_session() is_admin = False login_user_email = user_email is_service_account_is_enabled = True domain_datasource_ids = [] user_domain_id = None if auth_token: existing_user = db_utils.get_user_session(auth_token) user_domain_id = existing_user.domain_id login_user_email = existing_user.email is_admin = existing_user.is_admin is_service_account_is_enabled = existing_user.is_serviceaccount_enabled datasource_ids = db_session.query(DataSource.datasource_id).filter( DataSource.domain_id == user_domain_id).all() domain_datasource_ids = [r for r, in datasource_ids] elif datasource_id: datasource = db_session.query(DataSource).filter( DataSource.datasource_id == datasource_id).first() user_domain_id = datasource.domain_id is_service_account_is_enabled = datasource.is_serviceaccount_enabled domain_datasource_ids = [datasource.datasource_id] data = None if widget_id == 'usersCount': if is_service_account_is_enabled and not is_admin: user_count_query = db_session.query( ResourcePermission.email).filter( and_( ResourcePermission.datasource_id.in_( domain_datasource_ids), Resource.datasource_id == ResourcePermission.datasource_id, Resource.resource_owner_id == login_user_email, ResourcePermission.resource_id == Resource.resource_id, DomainUser.email == ResourcePermission.email, DomainUser.datasource_id.in_(domain_datasource_ids), DomainUser.member_type == constants.EntityExposureType. EXTERNAL.value)).distinct().count() # add 1 for loggin user user_count_query += 1 else: user_count_query = db_session.query(DomainUser.email).filter( DomainUser.datasource_id.in_( domain_datasource_ids)).distinct().count() data = user_count_query elif widget_id == 'groupsCount': group_count_query = db_session.query(DomainUser).filter( DomainUser.datasource_id.in_(domain_datasource_ids)).filter( DomainUser.type != constants.DirectoryEntityType.USER.value) if is_service_account_is_enabled and not is_admin: group_count_query = group_count_query.filter( DirectoryStructure.datasource_id == DomainUser.datasource_id, DirectoryStructure.member_email == login_user_email, DirectoryStructure.parent_email == DomainUser.email) data = group_count_query.count() elif widget_id == 'filesCount': file_count_query = db_session.query(Resource).filter( and_(Resource.datasource_id.in_(domain_datasource_ids), Resource.resource_type != 'folder')) if is_service_account_is_enabled and not is_admin: file_count_query = file_count_query.filter( Resource.resource_owner_id == login_user_email) data = file_count_query.count() elif widget_id == 'foldersCount': folder_count_query = db_session.query(Resource).filter( and_(Resource.datasource_id.in_(domain_datasource_ids), Resource.resource_type == 'folder')) if is_service_account_is_enabled and not is_admin: folder_count_query = folder_count_query.filter( Resource.resource_owner_id == login_user_email) data = folder_count_query.count() elif widget_id == 'sharedDocsByType': data = {} shared_docsByType_query = db_session.query( Resource.exposure_type, func.count(Resource.exposure_type)).filter( and_( Resource.exposure_type != constants.EntityExposureType.INTERNAL.value, Resource.datasource_id.in_(domain_datasource_ids), Resource.exposure_type != constants.EntityExposureType.PRIVATE.value)).group_by( Resource.exposure_type) if is_service_account_is_enabled and not is_admin: shared_docsByType_query = shared_docsByType_query.filter( Resource.resource_owner_id == login_user_email) shared_docs_by_type = shared_docsByType_query.all() public_count = 0 external_count = 0 domain_count = 0 anyone_with_link_count = 0 trusted_count = 0 for share_type in shared_docs_by_type: if share_type[0] == constants.EntityExposureType.EXTERNAL.value: external_count = share_type[1] elif share_type[ 0] == constants.EntityExposureType.ANYONEWITHLINK.value: anyone_with_link_count = share_type[1] elif share_type[0] == constants.EntityExposureType.PUBLIC.value: public_count = share_type[1] elif share_type[0] == constants.EntityExposureType.DOMAIN.value: domain_count = share_type[1] elif share_type[0] == constants.EntityExposureType.TRUSTED.value: trusted_count = share_type[1] data["rows"] = [ [constants.DocType.PUBLIC_COUNT.value, public_count], [ constants.DocType.ANYONE_WITH_LINK_COUNT.value, anyone_with_link_count ], [constants.DocType.EXTERNAL_COUNT.value, external_count], [constants.DocType.DOMAIN_COUNT.value, domain_count], [constants.DocType.TRUSTED.value, trusted_count] ] data[ "totalCount"] = public_count + external_count + domain_count + anyone_with_link_count + trusted_count elif widget_id == 'sharedDocsList': data = {} shared_docs_list_query = db_session.query( Resource.resource_name, Resource.resource_type).filter( and_( Resource.datasource_id.in_(domain_datasource_ids), or_( Resource.exposure_type == constants.EntityExposureType.EXTERNAL.value, Resource.exposure_type == constants.EntityExposureType.PUBLIC.value, Resource.exposure_type == constants.EntityExposureType.ANYONEWITHLINK.value))) shared_docs_totalcount_query = db_session.query( Resource.resource_name, Resource.resource_type).filter( and_( Resource.datasource_id.in_(domain_datasource_ids), or_( Resource.exposure_type == constants.EntityExposureType.EXTERNAL.value, Resource.exposure_type == constants.EntityExposureType.PUBLIC.value, Resource.exposure_type == constants.EntityExposureType.ANYONEWITHLINK.value))) if is_service_account_is_enabled and not is_admin: shared_docs_list_query = shared_docs_list_query.filter( Resource.resource_owner_id == login_user_email) shared_docs_totalcount_query = shared_docs_totalcount_query.filter( Resource.resource_owner_id == login_user_email) data["rows"] = shared_docs_list_query.limit(5).all() data["totalCount"] = shared_docs_totalcount_query.count() elif widget_id == 'externalUsersList': data = {} #Read from cache external_users_from_cache = db_session.query(ExternalExposure).filter( ExternalExposure.domain_id == user_domain_id).order_by( ExternalExposure.exposure_count.desc()).all() if len(external_users_from_cache) > 0 and ( external_users_from_cache[0].updated_at > (datetime.datetime.utcnow() - timedelta(seconds=600))): rows = [] total_count = 0 for external_user in external_users_from_cache: rows.append( [external_user.email, external_user.exposure_count]) total_count += 1 data["rows"] = rows data["totalCount"] = total_count return data external_user_list = db_session.query( ResourcePermission.email, func.count(ResourcePermission.email)).filter( and_( ResourcePermission.exposure_type == constants.EntityExposureType.EXTERNAL.value, ResourcePermission.datasource_id.in_( domain_datasource_ids))).group_by( ResourcePermission.email).order_by( func.count(ResourcePermission.email).desc()) if is_service_account_is_enabled and not is_admin: external_user_list = external_user_list.filter( and_( Resource.datasource_id == ResourcePermission.datasource_id, Resource.resource_owner_id == login_user_email, ResourcePermission.resource_id == Resource.resource_id)) user_group_emails_and_count = external_user_list.limit(5).all() external_user_emails_count_map = {} # get only external users ; removing channels/groups for row in user_group_emails_and_count: count_for_particular_email = row[1] email = row[0] directory_struct = db_session.query(DirectoryStructure).filter( and_( DirectoryStructure.parent_email == email, DirectoryStructure.datasource_id.in_( domain_datasource_ids), DomainUser.datasource_id == DirectoryStructure.datasource_id, DomainUser.email == DirectoryStructure.member_email, DomainUser.member_type == constants.EntityExposureType.EXTERNAL.value)).all() if directory_struct: for memberdetails in directory_struct: user = memberdetails.member_email if user in external_user_emails_count_map: count = external_user_emails_count_map[user] external_user_emails_count_map[ user] = count + count_for_particular_email else: external_user_emails_count_map[ user] = count_for_particular_email else: if email in external_user_emails_count_map: count = external_user_emails_count_map[email] external_user_emails_count_map[ email] = count + count_for_particular_email else: external_user_emails_count_map[ email] = count_for_particular_email external_user_perms_count = [] total_count = 0 for key, value in external_user_emails_count_map.iteritems(): total_count = total_count + 1 input_list = [key, value] external_user_perms_count.append(input_list) sorted_external_user_list = sorted(external_user_perms_count, key=lambda x: x[1], reverse=True) cache_for_db = [] now = datetime.datetime.utcnow() for ext_user in sorted_external_user_list: cache = {} cache["domain_id"] = user_domain_id cache["email"] = ext_user[0] cache["exposure_count"] = ext_user[1] cache["updated_at"] = now cache_for_db.append(cache) if len(cache_for_db) > 0: db_session.query(ExternalExposure).filter( ExternalExposure.domain_id == user_domain_id).delete() db_session.bulk_insert_mappings(ExternalExposure, cache_for_db) db_connection().commit() data["rows"] = sorted_external_user_list[:5] data["totalCount"] = external_user_list.count() elif widget_id == 'userAppAccess': data = {} apps = db_session.query(Application).distinct(Application.id).filter( Application.id == ApplicationUserAssociation.application_id, ApplicationUserAssociation.datasource_id.in_( domain_datasource_ids)) if is_service_account_is_enabled and not is_admin: apps = apps.filter( ApplicationUserAssociation.user_email == login_user_email) severity = {} low = apps.filter(Application.score < 4).count() medium = apps.filter( and_(Application.score >= 4, Application.score < 7)).count() high = apps.filter( and_(Application.score >= 7, Application.score <= 10)).count() severity['Low Risk'] = low severity['Medium Risk'] = medium severity['High Risk'] = high data["rows"] = severity data["totalCount"] = low + medium + high elif widget_id == 'filesWithFileType': data = {} file_type_query = db_session.query( Resource.resource_type, func.count(Resource.resource_type)).filter( and_( Resource.datasource_id.in_(domain_datasource_ids), Resource.exposure_type != constants.EntityExposureType.INTERNAL.value, Resource.exposure_type != constants.EntityExposureType.PRIVATE.value)).group_by( Resource.resource_type).order_by( func.count(Resource.resource_type).desc()) if is_service_account_is_enabled and not is_admin: file_type_query = file_type_query.filter( Resource.resource_owner_id == login_user_email) all_file_types = file_type_query.all() first_five = all_file_types[0:5] others = all_file_types[5:] totalcount = 0 for count in first_five: totalcount += count[1] others_count = 0 for count in others: others_count += count[1] if others_count > 0: first_five.append(('Others', others_count)) totalcount += others_count data["rows"] = first_five data["totalCount"] = totalcount elif widget_id == "internalUserList": data = {} internal_user_list = db_session.query( Resource.resource_owner_id, func.count(Resource.resource_id)).filter( and_( Resource.datasource_id.in_(domain_datasource_ids), Resource.exposure_type.in_([ constants.EntityExposureType.EXTERNAL.value, constants.EntityExposureType.PUBLIC.value ]))).group_by(Resource.resource_owner_id).order_by( func.count(Resource.resource_id).desc()) if is_service_account_is_enabled and not is_admin: internal_user_list = internal_user_list.filter( Resource.resource_owner_id == login_user_email) data["rows"] = internal_user_list.limit(5).all() data["totalCount"] = internal_user_list.count() elif widget_id == 'expensesByCategory': data = app_controller.get_app_stats(auth_token) elif widget_id == 'activitiesByEventType': activities = activity_db.activity_db().get_event_stats( event_filters, None, None) series_map = {} for activity in activities: print activity event_type = activity["event_type"] date = str(activity["year"]) + "-" + str( activity["month"]) + "-" + str(activity["day"]) if event_type in series_map: series_map[event_type]["data"][date] = activity["count"] else: series_map[event_type] = {"name": event_type, "data": {}} print series_map data = series_map.values() return data
def get_user_stats(auth_token): db_session = db_connection().get_session() login_user = db_utils.get_user_session(auth_token) user_domain_id = login_user.domain_id is_admin = login_user.is_admin is_service_account_is_enabled = login_user.is_serviceaccount_enabled datasources = db_session.query(DataSource).filter( DataSource.domain_id == user_domain_id).all() domain_datasource_ids = [] datasources_map = {} source_stats = {} for ds in datasources: domain_datasource_ids.append(ds.datasource_id) datasources_map[ds.datasource_id] = ds source_stats[ds.datasource_type] = {"count": 0, "value": ds.datasource_id} users = db_session.query(DomainUser).filter(DomainUser.datasource_id.in_(domain_datasource_ids)) shared_files_with_external_users =[] if is_service_account_is_enabled and not is_admin: shared_files_with_external_users = users.filter(and_(Resource.datasource_id == ResourcePermission.datasource_id, Resource.resource_id == ResourcePermission.resource_id, Resource.resource_owner_id == login_user.email, ResourcePermission.datasource_id == DomainUser.datasource_id, ResourcePermission.email == DomainUser.email, DomainUser.member_type == constants.EntityExposureType.EXTERNAL.value)).all() users = users.filter(DomainUser.email == login_user.email) users = users.all() + shared_files_with_external_users domain_stats = {} admin_stats = {"Admin": {"count": 0, "value": 1}, "Non-Admin": {"count": 0, "value": 0}} exposure_stats = {"Internal": {"count": 0, "value": constants.EntityExposureType.INTERNAL.value}, "External": {"count": 0, "value": constants.EntityExposureType.EXTERNAL.value}, "Trusted": {"count": 0, "value": constants.EntityExposureType.TRUSTED.value}} type_stats = {} for user in users: domain_name = utils.get_domain_name_from_email(user.email) if domain_name: if domain_name in domain_stats: domain_stats[domain_name]["count"] = domain_stats[domain_name]["count"] + 1 else: domain_stats[domain_name] = {"count": 1, "value": domain_name} ds = datasources_map[user.datasource_id] source_stats[ds.datasource_type]["count"] = source_stats[ds.datasource_type]["count"] + 1 if user.is_admin == 1: admin_stats["Admin"]["count"] = admin_stats["Admin"]["count"] + 1 else: admin_stats["Non-Admin"]["count"] = admin_stats["Non-Admin"]["count"] + 1 if user.member_type == constants.EntityExposureType.INTERNAL.value: exposure_stats["Internal"]["count"] = exposure_stats["Internal"]["count"] + 1 elif user.member_type == constants.EntityExposureType.EXTERNAL.value: exposure_stats["External"]["count"] = exposure_stats["External"]["count"] + 1 elif user.member_type == constants.EntityExposureType.TRUSTED.value: exposure_stats["Trusted"]["count"] = exposure_stats["Trusted"]["count"] + 1 if user.type in type_stats: type_stats[user.type]["count"] = type_stats[user.type]["count"] + 1 else: type_stats[user.type] = {"count": 1, "value": user.type} stats = [] stats.append({"display_name": "Access", "field_name": "member_type", "stats": exposure_stats}) stats.append({"display_name": "Privileges", "field_name": "is_admin", "stats": admin_stats}) stats.append({"display_name": "Type", "field_name": "type", "stats": type_stats}) stats.append({"display_name": "Connectors", "field_name": "datasource_id", "stats": source_stats}) stats.append({"display_name": "Domains", "field_name": "email", "stats": domain_stats}) return stats