Author: Aaron MacGillivary (aaron@junctionapps.ca)
Provided as a demo without any warranty of any kind, use at own risk
One of the items missing from the stock 3E reports is a clean listing of users, which dashboards they have, and what their roles are.
This does not provide what each of the roles can do, but by their names, if set up correctly, should provide a decent summary. This report is more meant for quickly identifying people who have roles that maybe shouldn't due to staffing changes, or those that are in a particular job function and should have the same roles as someone else.
Some basic familarity with Python is assumed.
You'll need pyodbc
, xlsxwriter
, and pandas
installed in order for this work work. Many Python folks will have these installed/available already. The method to install depends on how you have your Python environment setup. If you are using virtual environments, activating the virtual environment and running the pip
commands below will normally suffice.
Full instructions at: https://pandas.pydata.org/docs/getting_started/install.html
But I generally use the PyPI method.
pip install pandas
Full documentation at: https://xlsxwriter.readthedocs.io/
But again, generally the PyPI method using pip works well.
pip install XlsxWriter
From the homepage for the project:
pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience.
Windows users may want to read https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-ver16
When your drivers are installed, the PyPI pip method for install works well.
pip install pyodbc
Once all of the requirements are available, the code below should function properly.
import os
import pyodbc
import pandas as pd
import xlsxwriter
# you can type in your values directly here, but I'd advise against it.
# if you do you'd have something like:
# elite_db = "PROD_DB" instead of the line below for the elite_db
# I put them in enviornment variables for my notebook environment (there may be more secure ways)
elite_server = os.environ['ELITE_PROD_HOST']
elite_db = os.environ['ELITE_PROD_DB']
elite_user = os.environ['ELITE_RO_NAME']
elite_pass = os.environ['ELITE_RO_PASS']
# this sets up the connection to the database
# if you've looked into any of the 3E config files you'll see something similar used
conn_str = f'DRIVER={{FreeTDS}};SERVER={elite_server};PORT=1433;DATABASE={elite_db};UID={elite_user};PWD={elite_pass};TDS_Version=8.0;ClientCharset=UTF-8'
# windows users should use:
# conn_str = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={elite_server};PORT=1433;DATABASE={elite_db};UID={elite_user};PWD={elite_pass};ClientCharset=UTF-8'
e3_conn = pyodbc.connect(conn_str)
# This gets a full lising of users, their dashboards, and their roles, but not
# in a pretty way. We'll pivot on this data in the next step.
# you could take the output from this, copy into Excel, and do your pivot tables there.
# In my real life example of this, this whole process is scheduled/automated, and emails the
# resulting excel file to the people who want to see it
sql = """
select
NxBaseUser.baseusername,
nxbaseuser.isactive,
NxFWKAppObject.AppObjectCode 'dashboard',
mcrole.BaseUserName 'role',
nxrole.Description 'Role Description',
nxroleuser.RolePrecedence,
1 'hasdashboard'
from NxFWKUser
join NxBaseUser on NxFWKUser.NxFWKUserID = NxBaseUser.NxBaseUserID
join NxFWKAppObject on NxFWKUser.Dashboard = NxFWKAppObject.NXFWKAppObjectID
join nxroleuser on nxfwkuser.NxFWKUserID = nxroleuser.UserID
join nxrole on nxroleuser.RoleID = nxrole.NxRoleID
join nxbaseuser mcrole on mcrole.NxBaseUserID = nxroleuser.RoleID
"""
df = pd.read_sql(sql, e3_conn)
sheet_name = 'UsersRoles'
df_p = df.pivot_table(index=['baseusername', 'isactive', 'dashboard'],
columns='role',
values='hasdashboard',
aggfunc='sum',
fill_value=""
)
timestamp = pd.to_datetime('now').tz_localize('UTC').tz_convert('America/Halifax')
filename = f'dashboards_users_TE_3E_PROD_{timestamp:%Y%m%d}.xlsx'
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet(sheet_name)
# formatters:
# a note on rotating 90: o365 online does not render the rotated text correctly
row_heading_format = workbook.add_format({'rotation': 90, 'bold': True})
index_format = workbook.add_format({'bold': True})
col_count = df_p.columns.size + 3
worksheet.set_column(0, 0, 25) # baseusername
worksheet.set_column(1, 1, 7) # isactive
worksheet.set_column(2, 2, 35) # dashboard name
worksheet.set_column(3, col_count, 3) # all the roles
# create the heading row
for col_idx, column_heading in enumerate(df_p.columns, start=3):
worksheet.write(0,col_idx, column_heading, row_heading_format)
# create the index row
for col_idx, index_name in enumerate(df_p.index.names):
worksheet.write(1, col_idx, index_name, index_format)
# write out the data
for row_idx, row in enumerate([(df_p.index[i],row) for i, row in enumerate(df_p[df_p.columns].values)],
start=2):
col_pos = 0
for index_item_value in row[0]:
worksheet.write(row_idx,col_pos, index_item_value)
col_pos += 1
for row_item_value in row[1]:
worksheet.write(row_idx,col_pos, row_item_value)
col_pos += 1
# create a data filter
worksheet.autofilter(1, 0, len(df_p.index) + 1, col_count)
# freeze panes
worksheet.freeze_panes(2, 3)
workbook.close()
e3_conn.close()