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
