Use Case and Problem
Amazon QuickSight is a powerful tool for business intelligence and data analytics; however, extracting user information, especially for substantial number of users, can be challenging due to limitations in both the Amazon QuickSight console and AWS CLI when exporting multiple users simultaneously.
In this scenario, a customer needed to export more than 900 users from Amazon QuickSight to generate a table in Redshift. However, they encountered the constraint that Amazon QuickSight console could only display 50 users per page, and the AWS CLI could export only the initial 100 users. The process of manually copying the next token and repeatedly executing the command to export all users proved to be time-consuming and hectic.
Sample users list in QuickSight
How to Solve the Problem
This article shows about how customers can export all the Amazon QuickSight users to a CSV file using Windows PowerShell, Python and Lambda function.
Prerequisites
• Install and configure AWS CLI to use PowerShell/Python option
(Install or update to the latest version of the AWS CLI - AWS Command Line Interface) - It will install the Amazon QuickSight command-lets. Configure the AWS CLI profile with the account from where Amazon QuickSight users are to be exported.
• You need to be an Amazon QuickSight Admin with permissions to view and manage users
Steps
This article provides a code in PowerShell, Python and Lambda that automates the process of exporting all the users from Amazon QuickSight and saves time and effort. The code exports the user attributes from Amazon QuickSight, then parses the output and appends the user details to a CSV file in just few seconds.
Solution 1 - Using PowerShell/Python code to export the users from Amazon QuickSight.
- Download script
a. For using PowerShell download the attached “export_quicksight_users.ps1” PowerShell script.
b. For using Python download the attached “export_quicksight_users.py” python script. - Run the script
a. For using PowerShell open the PowerShell prompt and run the script as shown in the following screenshot. The script requires two inputs - account Id and Amazon QuickSight namespace (By default the namespace is “default”)
b. For using the Python script open the command line prompt and run the script as shown in the following screenshot. The script requires two inputs - account id and Amazon QuickSight namespace (By default the namespace is “default”)
- The script will export all the users to a CSV file named “quicksight_users_YYYYMMDDHHmmSS.csv”, where YYYYMMDDHHmmSS will be replaced with the date time stamp.
Solution 2 - Using Lambda function to export the users from Amazon QuickSight to S3 bucket.
Resources Required
Lambda Function
Lambda function will return a list of all of the Amazon QuickSight users for an account.
Amazon S3 bucket:
Amazon S3 bucket will store the result CSV file generated.
IAM Role:
IAM role will be used by Lambda function to get access to Amazon S3 bucket.
Steps:
-
Boto3 documentation of Amazon QuickSight list_users command-let - list_users - Boto3 1.34.61 documentation
-
Download the attached lambda function and update the below variables before running the function -
a. account_id - The ID for the Amazon Web Services account that the user is in. Currently, you use the ID for the Amazon Web Services account that contains your Amazon QuickSight account.
b. namespace - The namespace. Currently, you should set this to default.
c. bucket_name - Name of the bucket to save the result csv file.
-
Deploy the Lambda function.
-
Run the Lambda function.
-
Download the result file from Amazon S3 Bucket.
Attachments
PowerShell Code
############################################################################################
# Program : export_quicksight_users.ps1 #
# Description : This program exports all the quicksight users in a csv file from an AWS #
# Account using AWS CLI #
# Pre-requisite : #
# AWS Cli installed and configured for the aws account where QuickSight is deployed #
############################################################################################
###############
# Main Script #
###############
#Defining variables
$accountId = $null
$nameSpace = $null
# Take accountId and NameSpace as user inputs
While (!$accountId -or !$nameSpace)
{
# Get user Inputs
If(!$accountId) { $accountId = Read-Host -Prompt "Enter account Id" }
If(!$nameSpace) { $nameSpace = Read-Host -Prompt "Enter namespace" }
}
#Creating the result CSV file
$CurrentDir = (Get-Location).Path
$D = (Get-Date -UFormat %Y%m%d%H%M%S)
$qs_result_file = "$CurrentDir\quicksight_users_$D.csv"
$qs_header = "UserName,Email,Role,IdentityType,Active,PrindicpalId"
$qs_header | Out-File $qs_result_file -Encoding Ascii
# Start collecting QuickSight Users
$start_userslist = (aws quicksight list-users --aws-account-id $accountId --namespace $nameSpace | ConvertFrom-Json)
If($start_userslist)
{
$foundUsers = $start_userslist.UserList
ForEach($foundUser in $foundUsers)
{
$userName = $foundUser.UserName
$userEmail = $foundUser.Email
$userRole = $foundUser.Role
$identityType = $foundUser.IdentityType
$userActive = $foundUser.Active
$userPrincipalId = $foundUser.PrincipalId
$userData = "$userName,$userEmail,$userRole,$identityType,$userActive,$userPrincipalId"
$userData | Out-File $qs_result_file -Encoding Ascii -Append
}
# Checking for the next token
$nextToken = $start_userslist.NextToken
If($nextToken)
{
while($nextToken)
{
# Start collecting QuickSight Users
$next_userslist = (aws quicksight list-users --aws-account-id $accountId --namespace $nameSpace --next-token $nextToken | ConvertFrom-Json)
If($next_userslist)
{
$nextUsers = $next_userslist.UserList
ForEach($nextUser in $nextUsers)
{
$userName = $nextUser.UserName
$userEmail = $nextUser.Email
$userRole = $nextUser.Role
$identityType = $nextUser.IdentityType
$userActive = $nextUser.Active
$userPrincipalId = $nextUser.PrincipalId
$userData = "$userName,$userEmail,$userRole,$identityType,$userActive,$userPrincipalId"
$userData | Out-File $qs_result_file -Encoding Ascii -Append
}
# Checking for the next token
$nextToken = $next_userslist.NextToken
}
}
}
Write-Host "Result file generated: $qs_result_file"
}
Python Code
import subprocess
import datetime
from pathlib import Path
import os
import json
# Get user inputs
account_id = input("Enter AWS account ID: ")
namespace = input("Enter QuickSight namespace: ")
# Get current directory and create a folder with datetime stamp
current_dir = str(Path.cwd())
timestamp = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
folder_path = os.path.join(current_dir, timestamp)
os.makedirs(folder_path)
# Define result files
result_file_path = os.path.join(folder_path, "quicksight_userslist.csv")
header = "UserName,Email,Role,IdentityType,Active,PrindicpalId\n"
with open(result_file_path, "w") as f:
f.write(header)
log_file_path = os.path.join(folder_path, f"logs_list_tags_{timestamp}.txt")
# Collect QuickSight users
next_token = None
while True:
# Construct AWS CLI command to list QuickSight users
cmd = ["aws", "quicksight", "list-users", "--aws-account-id", account_id, "--namespace", namespace]
if next_token:
cmd.extend(["--next-token", next_token])
# Execute AWS CLI command as subprocess
result = subprocess.run(cmd, capture_output=True, text=True)
# Parse JSON output and write to result file
output = json.loads(result.stdout)
for user in output["UserList"]:
user_data = ",".join([user["UserName"], user["Email"], user["Role"], user["IdentityType"], str(user["Active"]), user["PrincipalId"]]) + "\n"
with open(result_file_path, "a") as f:
f.write(user_data)
# Check for next token and break if not found
if "NextToken" in output:
next_token = output["NextToken"]
else:
break
Lambda Function Code
import json
import boto3
import os
import datetime
def lambda_handler(event, context):
#define variables
account_id = 'UPDATE ACCOUNT ID'
namespace = 'default'
bucket_name = 'quicksight-usersdump'
#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\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']:
user_data = ",".join([user['UserName'], user['Email'], user['Role'], user['IdentityType'], str(user['Active']), user['PrincipalId']]) + "\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
}
Conclusion
Using the above methods all the Amazon QuickSight users can be easily downloaded to 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 18 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.