Exporting Sisense User Lists to CSV
Exporting Sisense User Lists to CSV
Exporting Sisense users requires using the REST API. While the user list is visible under Admin > User Management > Users, there is no option to download and manipulate this data directly.
From The Users Info, Admin can generate some insights, Such as:
- Review Active/Inactive Users
- Review Last Login
- Group the Users by Roles
Sisense Provide 2 endpoints to get the list of all users.
- Version 0.9 --> Get /users
- Version 1.0 --> Get /users
The sample Python script below utilizes the GET /users endpoint on version 1.0
Implementation Steps
- Open Python and Install the relative libraries in case needed
- Update the Following Variables In the Python Script:
- Personal Server
- Token - Sisense Authentication Token. Can be achieved By Api Version 1.0 - Post /authentication/login
- CSV Path - Local PC file path for the CSV to download
3. Run the Script
Python Script
import json
import requests
import pandas as pd
# Define global variables
server = '' # Replace with your actual server URL. for example: https://url.sisense.com
token = '' # Sisense Authentication token
path = ''
"""
Define the path variable according to your operating system and file location
Example for Windows:
path = r'C:\\Users\demo.demo\Desktop\sisense_users.csv'
Example for Mac:
path = '/Users/demo.demo/Desktop/sisense_users.csv'
"""
# Fixed variables: `user_columns`, `params`, and `headers` are fixed and should not be updated.
user_columns = ['_id','active','roleId','created','tenantId','email','firstName','lastName','userName','lastLogin','lastActivity'] # Define the fields to be retrieved for users
params = {
"fields": ",".join(user_columns)
}
headers = {
"Authorization": f"Bearer {token}",
"Content-Type": "application/json"
}
# Retrieves user data from the specified server endpoint, filters it based on provided user_columns, and returns a DataFrame.
def get_users(server, token, headers, user_columns, params):
endpoint = '/api/v1/users'
url = f'{server}{endpoint}'
try:
request = requests.get(url=url, headers=headers, params=params)
request.raise_for_status() # Check for request success
respond = request.json()
user_data = []
for user in respond:
user_dict = {}
for key, value in user.items():
if key in user_columns:
user_dict[key] = value
user_data.append(user_dict) # Append dictionary containing all attributes
return pd.DataFrame(user_data)
except requests.exceptions.RequestException as e:
print('Error:', e)
return None # Return None or handle error appropriately
# Modifies the input DataFrame by converting specific columns to datetime format, renaming certain columns, and saving the DataFrame to a CSV file
def modify_df(df_users):
# Convert columns to datetime format
df_users["created"] = pd.to_datetime(df_users["created"])
df_users["lastLogin"] = pd.to_datetime(df_users["lastLogin"])
df_users["lastActivity"] = pd.to_datetime(df_users["lastActivity"])
# Rename specific columns in the DataFrame
df_users.rename(columns={'_id': 'user_id',
'userName': 'user_name',
'firstName': 'first_name',
'lastName': 'last_name',
'active': 'is_active',
'roleId': 'role_id',
'created': 'created_date',
'tenantId': 'tenant_id',
'lastLogin': 'last_login',
'lastActivity': 'last_activity'}, inplace=True)
# Reorder columns
ordered_columns = [
'user_id', 'user_name', 'first_name', 'last_name', 'is_active',
'role_id', 'created_date', 'tenant_id', 'last_login', 'last_activity'
]
df_users = df_users[ordered_columns]
# Save the DataFrame to a CSV file without including the index
df_users.to_csv(path, index=False)
return df_users
# Retrieve users data using the get_users function
df_users = get_users(server, token, headers, user_columns, params)
# Modify df_users data frame into the final output
df_result = modify_df(df_users)
# Print the first few rows of the modified DataFrame
print(df_result.head())
# Print a message indicating the end of the program
print("program completed")
1 comment
Vadim Pidgornyi
·1 year agoJust a little bit easier way to achieve the same
https://community.sisense.com/t5/knowledge-base/script-to-retrieve-the-user-list-with-first-name-and-last-email/ta-p/19359https://community.sisense.com/t5/knowledge-base/how-to-export-a-list-of-sisense-users/ta-p/8847