Downloading all delivery addresses of all MicroStrategy users

Manager, Sales Engineering • MicroStrategy

This Python script downloads information about all users and all delivery addresses assigned to them. With big implementations it could be thousands of records that are difficult to track and maintain.

It downloads information about users from group 'Everyone'. Users with no delivery addresses are omitted.
The output is a CSV file that can be later processed.
Example output CSV:


"User_Name","User_ID","User_Login","Address_ID","name","deliveryMode","value","isDefault"
"Administrator","54F3D26011D2896560009A8E67019608","Administrator","A778D6BD11E52EFF0BC70080EF357270","admin","EMAIL","admin@yourorganization.com","False"
"Administrator","54F3D26011D2896560009A8E67019608","Administrator","0FAEA06701784CA38C729B866F1110B6","Gmail","EMAIL","kowalski@gmail.com","True"
"Administrator","54F3D26011D2896560009A8E67019608","Administrator","394BD5974B65FB3AF894B69C9FFBF2CE","Remote Diagnostics","EMAIL","diagnostics@Strategy.com","False"
"Administrator","54F3D26011D2896560009A8E67019608","Administrator","A524B2AA9FF94B2195E151AEB6CBDE83","Yahoo","EMAIL","kowalski@yahoo.com","False"
"Brian Kelt","E96A7B9311D4BBCE10004694316DE8A4","bkelt","17CCD93314DC4C9FAE0208876CE51840","gmail","EMAIL","bkelt@gmail.com","True"
"Brian Kelt","E96A7B9311D4BBCE10004694316DE8A4","bkelt","2AE3FDB85B674A1E804292B7F49D19CD","outlook","EMAIL","bkelt@outlook.com","False"
"Strategy Web User","C1E141E611D603A2100086B3A5E8F8A4","User","DAB285532C1D4659B89C9E224CA8E8E2","Office Address","EMAIL","user@yourorganization.com","True"
"testow","8DE1505E4726D15A911868A6A5C4F131","testow","BD6A318341B55EB83E07C79BCBACF792","8DE1505E4726D15A911868A6A5C4F131","EMAIL","testow@mstr.com","True"

Please note - I've added color formatting in the code below (http://hilite.me/). Code copied from here might not work due to indentation issues. In that case, use attached file with sample code. Also this CRM is adding ';' after URLs.


# Robert Prochowicz / 2020-12-31
# tested on MSTR 11.3
# This script downloads information about all users and all delivery addresses assigned to them.
# With big implementations it could be thousands of records
# This script replaces and combines the following Command Manager Outline
### LIST ALL PROPERTIES FOR USERS IN GROUP "Everyone";
# Additionally, it provides information if the address is default which the Outline can't do
 
import pandas as pd
import csv
from datetime import datetime
from mstrio.connection import Connection
 
#### Parameters ####
api_login = ''
api_password = ''
base_url = 'http ://MSTR-SERVER/MicroStrategyLibrary/'
env_id = 'test1' #CSV files will be saved with this prefix
group_id = 'C82C6B1011D2894CC0009D9F29718E4F' #this is ID of user group Everyone
 
### FUNCTIONS
def getGroupMembers(connection, group_id):
    r = connection.session.get(url=connection.base_url + "/api/usergroups/" + group_id + '/members?includeAccess=false')
    usergroup_members = r.json()
    return usergroup_members
 
def getAddresses(connection, user_ID):
    r = connection.session.get(url=connection.base_url + "/api/users/" + user_ID + '/addresses')
    addresses = r.json()['addresses']
    return addresses
 
conn = Connection(base_url,api_login,api_password)
ev = getGroupMembers(conn, group_id)
 
dt1=datetime.now() # This can be a long process that can take minutes, so we will measure it.
print("\n", dt1)
addr_list = []
count_ug=0
 
for u in ev:
    addd = getAddresses(conn, u['id'])
    for a in addd:
        addr_list.append([u['name'], u['id'],u['username'], a['id'], a['name'], a['deliveryMode'], a['value'], a['isDefault']])
    count_ug+=1 # this counter will give you hints how long the whole process will take
    if (count_ug % 100) == 0:
        eta = (len(ev)/count_ug)*(datetime.now()-dt1)
        print(count_ug, "users processed / ETA: ", eta) 
 
# Export to CSV
df = pd.DataFrame(addr_list)
#cleaning weird descriptions that can mess with CSV format
df.replace(to_replace=[r"\\t|\\n|\\r", "\t|\n|\r"], value=["",""], regex=True, inplace=True)
df.to_csv(env_id+'addresses.csv', index = None,quoting=csv.QUOTE_ALL,
          header=["User_Name","User_ID","User_Login","Address_ID","name","deliveryMode","value","isDefault"])
 
conn.close()

Comment

0 comments

Details

Knowledge Article

Published:

December 31, 2020

Last Updated:

November 25, 2022

"User_Name","User_ID","User_Login","Address_ID","name","deliveryMode","value","isDefault" "Administrator","54F3D26011D2896560009A8E67019608","Administrator","A778D6BD11E52EFF0BC70080EF357270","admin","EMAIL","admin@yourorganization.com","False" "Administrator","54F3D26011D2896560009A8E67019608","Administrator","0FAEA06701784CA38C729B866F1110B6","Gmail","EMAIL","kowalski@gmail.com","True" "Administrator","54F3D26011D2896560009A8E67019608","Administrator","394BD5974B65FB3AF894B69C9FFBF2CE","Remote Diagnostics","EMAIL","diagnostics@Strategy.com","False" "Administrator","54F3D26011D2896560009A8E67019608","Administrator","A524B2AA9FF94B2195E151AEB6CBDE83","Yahoo","EMAIL","kowalski@yahoo.com","False" "Brian Kelt","E96A7B9311D4BBCE10004694316DE8A4","bkelt","17CCD93314DC4C9FAE0208876CE51840","gmail","EMAIL","bkelt@gmail.com","True" "Brian Kelt","E96A7B9311D4BBCE10004694316DE8A4","bkelt","2AE3FDB85B674A1E804292B7F49D19CD","outlook","EMAIL","bkelt@outlook.com","False" "Strategy Web User","C1E141E611D603A2100086B3A5E8F8A4","User","DAB285532C1D4659B89C9E224CA8E8E2","Office Address","EMAIL","user@yourorganization.com","True" "testow","8DE1505E4726D15A911868A6A5C4F131","testow","BD6A318341B55EB83E07C79BCBACF792","8DE1505E4726D15A911868A6A5C4F131","EMAIL","testow@mstr.com","True"

# Robert Prochowicz / 2020-12-31 # tested on MSTR 11.3 # This script downloads information about all users and all delivery addresses assigned to them. # With big implementations it could be thousands of records # This script replaces and combines the following Command Manager Outline ### LIST ALL PROPERTIES FOR USERS IN GROUP "Everyone"; # Additionally, it provides information if the address is default which the Outline can't do import pandas as pd import csv from datetime import datetime from mstrio.connection import Connection #### Parameters #### api_login = '' api_password = '' base_url = 'http ://MSTR-SERVER/MicroStrategyLibrary/' env_id = 'test1' #CSV files will be saved with this prefix group_id = 'C82C6B1011D2894CC0009D9F29718E4F' #this is ID of user group Everyone ### FUNCTIONS def getGroupMembers(connection, group_id): r = connection.session.get(url=connection.base_url + "/api/usergroups/" + group_id + '/members?includeAccess=false') usergroup_members = r.json() return usergroup_members def getAddresses(connection, user_ID): r = connection.session.get(url=connection.base_url + "/api/users/" + user_ID + '/addresses') addresses = r.json()['addresses'] return addresses conn = Connection(base_url,api_login,api_password) ev = getGroupMembers(conn, group_id) dt1=datetime.now() # This can be a long process that can take minutes, so we will measure it. print("\n", dt1) addr_list = [] count_ug=0 for u in ev: addd = getAddresses(conn, u['id']) for a in addd: addr_list.append([u['name'], u['id'],u['username'], a['id'], a['name'], a['deliveryMode'], a['value'], a['isDefault']]) count_ug+=1 # this counter will give you hints how long the whole process will take if (count_ug % 100) == 0: eta = (len(ev)/count_ug)*(datetime.now()-dt1) print(count_ug, "users processed / ETA: ", eta) # Export to CSV df = pd.DataFrame(addr_list) #cleaning weird descriptions that can mess with CSV format df.replace(to_replace=[r"\\t|\\n|\\r", "\t|\n|\r"], value=["",""], regex=True, inplace=True) df.to_csv(env_id+'addresses.csv', index = None,quoting=csv.QUOTE_ALL, header=["User_Name","User_ID","User_Login","Address_ID","name","deliveryMode","value","isDefault"]) conn.close()