How to export all Amazon QuickSight groups for a large number of QuickSight users
Ravi Kumar, Sr. Technical Account Manager, Amazon Web Services
Amazon QuickSight is a powerful tool for business intelligence and data analytics. Extracting
groups that a user belongs to, especially when that group includes substantial number of users,
can be challenging due to limitations in both the Amazon QuickSight console and AWS CLI when
exporting large number of users’ information simultaneously.
In an organizational restructure, a customer faced the challenge of managing over 1,000 users
across 50+ diverse QuickSight groups. The task became complex as they needed to identify
group affiliations for each user following the changes.
However, navigating this process became time-consuming due to constraints in the Amazon
QuickSight console. The console’s search functionality is limited to a specific group and requires
the full username, which was especially cumbersome given the lengthy usernames of federated
users. Additionally, the AWS CLI can only display details of 100 users at a time, making the
manual process of copying the next token and repeatedly executing commands to view all user
groups both tedious and demanding.
Sample users and group list in QuickSight
How to Solve the Problem
This article shows how customers can export all the Amazon QuickSight users along with all their group affiliations to a CSV file using a Lambda function.
Steps
This article provides a python code that can be used in a Lambda function to automates the process of exporting all the users and their group information from Amazon QuickSight. The code exports the user attributes from Amazon QuickSight, then parses the output and appends the user details to a CSV file.
Resources required:
Lambda Function:
Lambda function will return a list of all of the Amazon QuickSight users and their groups in an AWS Account.
Amazon S3 bucket:
Amazon S3 bucket will store the result CSV file generated.
IAM Role:
IAM role used by Lambda function should have below access
- Write access to S3 Bucket
- Invoke QuickSight list_user_groups API
Boto3 documentation of Amazon QuickSight list_user_groups command-let - list_user_groups — Boto3 Docs 1.26.94 documentation
Steps:
To create a Python function
- Open the Lambda console.
- Choose Create function.
- Configure the following settings:
• Function name: Enter a name for the function.
• Runtime: Choose Python 3.12 or latest - Leave architecture set to x86_64 and choose Create function.
Lambda creates a function that returns the message Hello from Lambda! Lambda also creates an execution role for your function. An execution role is an AWS Identity and Access Management (IAM) role that grants a Lambda function permission to access AWS services and resources. For your function, the role that Lambda creates grants basic permissions to write to CloudWatch Logs.
To modify the code in the console
-
Choose the Code tab.
In the console’s built-in code editor, you should see the function code that Lambda created. If you don’t see the lambda_function.py tab in the code editor, select lambda_function.py in the file explorer as shown on the following diagram.
-
Paste the following code into the lambda_function.py tab, replacing the code that Lambda created.
import json
import boto3
import os
import datetime
def lambda_handler(event, context):
#define variables
account_id = os.environ['account_id']
namespace = os.environ['namespace']
bucket_name = os.environ['bucket_name']
#Get current date time for folder creation
timestamp = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
folder_path =f"/tmp/{timestamp}"
os.makedirs(folder_path)
#create quicksight client
quicksight_client = boto3.client('quicksight')
# Define result files
result_file_name = f"quicksight_userslist_{timestamp}.csv"
result_file_path = os.path.join(folder_path, result_file_name)
header = "UserName,Email,Role,IdentityType,Active,PrincipalId,Groups\n"
with open(result_file_path, "w") as f:
f.write(header)
# collect quicksight users
next_token = None
while True:
if next_token is None:
response = quicksight_client.list_users(
AwsAccountId = account_id,
Namespace = namespace
)
else:
response = quicksight_client.list_users(
AwsAccountId = account_id,
Namespace = namespace,
NextToken = next_token
)
# process user list
for user in response['UserList']:
response_usergrp = quicksight_client.list_user_groups(
UserName = user['UserName'],
AwsAccountId = account_id,
Namespace = namespace
)
userGroups = []
for group in response_usergrp['GroupList']:
userGroups.append(group['GroupName'])
user_groups = " | ".join(userGroups)
user_data = ",".join([user['UserName'], user['Email'], user['Role'], user['IdentityType'], str(user['Active']), user['PrincipalId'], user_groups]) + "\n"
with open(result_file_path, "a") as f:
f.write(user_data)
#check for next token and break if not found
if 'NextToken' not in response:
break
else:
next_token = response['NextToken']
# upload the csv file to S3 bucket
s3_client = boto3.client('s3')
s3_key = f"quicksight_users/{result_file_name}"
s3_client.upload_file(result_file_path, bucket_name, s3_key)
# return s3 bucket and key information of the uploaded file
return {
'bucketName': bucket_name,
's3Key': s3_key
}
-
Select Deploy to update your function’s code. When Lambda has deployed the changes, the console displays a banner letting you know that it’s successfully updated your function.
-
Click on the Configuration → Environment variables to add below environment variables (update account_id and bucket_name)
-
Run the Lambda function.
-
Download the result file from Amazon S3 Bucket.
Conclusion:
Using the above script all the Amazon QuickSight users can be easily downloaded as a CSV file.
Author & Company Bio
Ravi Kumar is a Senior Technical Account Manager in AWS Enterprise Support who helps enterprise support customers streamline their cloud operations on AWS. He is a results-driven IT professional with over 19 years of experience. In his spare time, Ravi enjoys painting and traveling.
Article Disclaimer: Any code shared in Community articles is to be considered a sample and is not endorsed by AWS.