This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
AssafHanina
Sisense Team Member
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 07-23-2024 09:48 AM
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")
Labels:
Rate this article:
Comments
Vadim_Pidgornyi
Sisense Team Member
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
10-16-2024
07:30 AM
Just a little bit easier way to achieve the same