{ "cells": [ { "cell_type": "markdown", "id": "ee9ecf3b-7afb-4f21-b373-ab567208d3a8", "metadata": {}, "source": [ "# Create Excel File with Users, Roles and Dashboards\n", "\n", "Author: Aaron MacGillivary (aaron@junctionapps.ca)\n", "\n", "**Provided as a demo without any warranty of any kind, use at own risk**\n", "\n", "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.\n", "\n", "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.\n", "\n", "Some basic familarity with Python is assumed.\n", "\n", "## Requirements\n", "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.\n", "\n", "\n", "### Pandas\n", "Full instructions at: https://pandas.pydata.org/docs/getting_started/install.html\n", "\n", "But I generally use the PyPI method.\n", "\n", "`pip install pandas`\n", "\n", "### xlsxwriter\n", "Full documentation at: https://xlsxwriter.readthedocs.io/\n", "\n", "But again, generally the PyPI method using pip works well.\n", "\n", "`pip install XlsxWriter`\n", "\n", "### pyodbc\n", "From the [homepage for the project](https://github.com/mkleehammer/pyodbc):\n", "> 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.\n", "\n", "Windows users may want to read https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-ver16\n", "\n", "When your drivers are installed, the PyPI pip method for install works well.\n", "\n", "`pip install pyodbc`\n", "\n", "Once all of the requirements are available, the code below should function properly." ] }, { "cell_type": "code", "execution_count": 1, "id": "121b733c-a0d6-4509-8d93-5f5856520355", "metadata": {}, "outputs": [], "source": [ "import os\n", "import pyodbc \n", "import pandas as pd\n", "import xlsxwriter" ] }, { "cell_type": "code", "execution_count": 2, "id": "e30ba8ac-872b-4a9d-a56a-7ce139c7a1d2", "metadata": {}, "outputs": [], "source": [ "# you can type in your values directly here, but I'd advise against it.\n", "# if you do you'd have something like:\n", "# elite_db = \"PROD_DB\" instead of the line below for the elite_db\n", "# I put them in enviornment variables for my notebook environment (there may be more secure ways)\n", "\n", "elite_server = os.environ['ELITE_PROD_HOST']\n", "elite_db = os.environ['ELITE_PROD_DB']\n", "elite_user = os.environ['ELITE_RO_NAME']\n", "elite_pass = os.environ['ELITE_RO_PASS']" ] }, { "cell_type": "code", "execution_count": 3, "id": "77561fae-4d92-47b9-82aa-5a435f955791", "metadata": {}, "outputs": [], "source": [ "# this sets up the connection to the database\n", "# if you've looked into any of the 3E config files you'll see something similar used\n", "\n", "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'\n", "# windows users should use:\n", "# 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'\n", "\n", "e3_conn = pyodbc.connect(conn_str)" ] }, { "cell_type": "code", "execution_count": 4, "id": "762bfcae-e40f-4be4-bcfc-aaf5ddeef5ec", "metadata": {}, "outputs": [], "source": [ "# This gets a full lising of users, their dashboards, and their roles, but not \n", "# in a pretty way. We'll pivot on this data in the next step.\n", "# you could take the output from this, copy into Excel, and do your pivot tables there.\n", "# In my real life example of this, this whole process is scheduled/automated, and emails the \n", "# resulting excel file to the people who want to see it\n", "\n", "sql = \"\"\"\n", "select \n", " NxBaseUser.baseusername,\n", " nxbaseuser.isactive,\n", " NxFWKAppObject.AppObjectCode 'dashboard',\n", " mcrole.BaseUserName 'role',\n", " nxrole.Description 'Role Description',\n", " nxroleuser.RolePrecedence,\n", " 1 'hasdashboard'\n", "\n", " from NxFWKUser\n", " join NxBaseUser on NxFWKUser.NxFWKUserID = NxBaseUser.NxBaseUserID \n", " join NxFWKAppObject on NxFWKUser.Dashboard = NxFWKAppObject.NXFWKAppObjectID\n", "\n", " join nxroleuser on nxfwkuser.NxFWKUserID = nxroleuser.UserID\n", " join nxrole on nxroleuser.RoleID = nxrole.NxRoleID\n", " join nxbaseuser mcrole on mcrole.NxBaseUserID = nxroleuser.RoleID\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 5, "id": "11d15222-8aa6-4518-b992-29c6605c1805", "metadata": {}, "outputs": [], "source": [ "df = pd.read_sql(sql, e3_conn)\n", "\n", "sheet_name = 'UsersRoles'\n", "df_p = df.pivot_table(index=['baseusername', 'isactive', 'dashboard'], \n", " columns='role', \n", " values='hasdashboard', \n", " aggfunc='sum',\n", " fill_value=\"\"\n", " )" ] }, { "cell_type": "code", "execution_count": 6, "id": "1e1d06f8-ad1e-4cea-b920-e86b856d3f24", "metadata": {}, "outputs": [], "source": [ "timestamp = pd.to_datetime('now').tz_localize('UTC').tz_convert('America/Halifax')\n", "filename = f'dashboards_users_TE_3E_PROD_{timestamp:%Y%m%d}.xlsx'\n", "workbook = xlsxwriter.Workbook(filename)\n", "worksheet = workbook.add_worksheet(sheet_name)\n", "\n", "# formatters:\n", "# a note on rotating 90: o365 online does not render the rotated text correctly\n", "row_heading_format = workbook.add_format({'rotation': 90, 'bold': True})\n", "index_format = workbook.add_format({'bold': True})\n", "\n", "col_count = df_p.columns.size + 3\n", "worksheet.set_column(0, 0, 25) # baseusername\n", "worksheet.set_column(1, 1, 7) # isactive\n", "worksheet.set_column(2, 2, 35) # dashboard name\n", "worksheet.set_column(3, col_count, 3) # all the roles\n", "\n", "# create the heading row\n", "for col_idx, column_heading in enumerate(df_p.columns, start=3):\n", " worksheet.write(0,col_idx, column_heading, row_heading_format)\n", "\n", "# create the index row\n", "for col_idx, index_name in enumerate(df_p.index.names):\n", " worksheet.write(1, col_idx, index_name, index_format)\n", "\n", "# write out the data\n", "for row_idx, row in enumerate([(df_p.index[i],row) for i, row in enumerate(df_p[df_p.columns].values)],\n", " start=2):\n", " col_pos = 0\n", " for index_item_value in row[0]:\n", " worksheet.write(row_idx,col_pos, index_item_value)\n", " col_pos += 1\n", " for row_item_value in row[1]:\n", " worksheet.write(row_idx,col_pos, row_item_value)\n", " col_pos += 1\n", "\n", "# create a data filter\n", "worksheet.autofilter(1, 0, len(df_p.index) + 1, col_count)\n", "\n", "# freeze panes\n", "worksheet.freeze_panes(2, 3)\n", "\n", "workbook.close()" ] }, { "cell_type": "code", "execution_count": 7, "id": "14b350e2-5ff0-4d4b-8215-c75add71702e", "metadata": {}, "outputs": [], "source": [ "e3_conn.close()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.10" } }, "nbformat": 4, "nbformat_minor": 5 }