sample_tableau_notebook_controller

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tableau Server Controller"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TODO\n",
    "\n",
    "- Actually use the much nicer JSON, reformat both sent and received payloads (apparently not available for API 2.4)\n",
    "- Trial run with real admin credentials\n",
    "- Format the list of workbooks and IDs neatly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import untangle\n",
    "import lxml.etree as etree\n",
    "from config import USERNAME, PASSWORD, SITE_ID\n",
    "from requests.packages.urllib3.exceptions import InsecureRequestWarning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check on server info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# disable SSL worries\n",
    "requests.packages.urllib3.disable_warnings(InsecureRequestWarning)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "BASE_URL = \"https://dev-tableau.portal.wellcome.ac.uk/api/2.4/\"\n",
    "SERVER_INFO_URL = BASE_URL + \"serverinfo\"\n",
    "r = requests.get(SERVER_INFO_URL, verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def print_pretty_xml(response_content):\n",
    "    xml = etree.fromstring(response_content)\n",
    "    print(etree.tostring(xml, pretty_print=True).decode())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print_pretty_xml(r.content)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "## Sign in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sign_in_xml = '''<tsRequest>\n",
    "  <credentials name=\"{username}\" password=\"{password}\" >\n",
    "      <site contentUrl=\"\" />\n",
    "  </credentials>\n",
    "</tsRequest>'''.format(username=USERNAME, password=PASSWORD)\n",
    "\n",
    "SIGN_IN_URL = BASE_URL + \"auth/signin\"\n",
    "\n",
    "r = requests.post(SIGN_IN_URL, data=sign_in_xml, verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print_pretty_xml(r.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "untangled_xml = untangle.parse(r.text)\n",
    "auth_token = untangled_xml.tsResponse.credentials['token']\n",
    "site_id = untangled_xml.tsResponse.credentials.site['id']\n",
    "user_id = untangled_xml.tsResponse.credentials.user['id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(auth_token)\n",
    "print(site_id)\n",
    "print(user_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## List workbooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY_WORKBOOKS_URL = BASE_URL + \"sites/{site_id}/\".format(site_id = site_id) + \"workbooks\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY_WORKBOOKS_URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = {'X-Tableau-Auth': auth_token}\n",
    "\n",
    "r = requests.get(QUERY_WORKBOOKS_URL, headers=headers, verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook_response = r.text"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Do something with workbook IDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbooks_untangled = untangle.parse(workbook_response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbooks_untangled.tsResponse.workbooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbooks = workbooks_untangled.tsResponse.workbooks.workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbooks_list = []\n",
    "\n",
    "for workbook in workbooks:\n",
    "    project = workbook.get_elements(\"project\")[0]\n",
    "    project_name = project[\"name\"]\n",
    "    project_id = project[\"id\"]\n",
    "    tags = workbook.get_elements(\"tags\")[0].get_elements()\n",
    "    tag_names = \",\".join([tag.get_attribute(\"label\") for tag in tags])\n",
    "    print(workbook[\"name\"] + \" // \" + workbook[\"id\"])\n",
    "    print(\"    Project name & ID: \" + project_name + \" // \" + project_id)\n",
    "    print(\"    Tags: \" + tag_names)\n",
    "    print(\"\")\n",
    "    workbooks_list.append(\n",
    "        {\n",
    "            \"name\": workbook[\"name\"],\n",
    "            \"id\": workbook[\"id\"],\n",
    "            \"project_name\": project_name,\n",
    "            \"project_id\": project_id,\n",
    "            \"tags\": tag_names,\n",
    "        }\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[workbook for workbook in workbooks_list if \"Admin\" in workbook['tags']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[workbook['id'] for workbook in workbooks_list if \"Admin\" in workbook['tags']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get group ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY_GROUPS_URL = BASE_URL + \"sites/{site_id}/groups\".format(site_id = site_id)\n",
    "\n",
    "headers = {'X-Tableau-Auth': auth_token}\n",
    "\n",
    "r = requests.get(QUERY_GROUPS_URL, headers=headers, verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_xml = r.text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "untangled_group_xml = untangle.parse(group_xml)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "groups = untangled_group_xml.tsResponse.groups.group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_list = []\n",
    "\n",
    "for group in groups:\n",
    "    print(group['name'] + \" // \" + group['id'])\n",
    "    group_list.append({'name': group['name'], 'id': group['id']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[group for group in group_list if \"GDPR\" in group['name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[group['id'] for group in group_list if \"GDPR\" in group['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add permissions to each workbook for the group"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[API docs for this](https://onlinehelp.tableau.com/v10.1/api/rest_api/en-us/help.htm#REST/rest_api_ref.htm#Add_Workbook_Permissions%3FTocPath%3DAPI%2520Reference%7C_____11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_workbook_permissions_url(workbook_id):\n",
    "    return BASE_URL + \"sites/{site_id}/workbooks/{workbook_id}/permissions\".format(site_id = site_id, workbook_id=workbook_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "create_workbook_permissions_url(\"test\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Modify capabilities below**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sample capabilities:\n",
    "```\n",
    "<capability name=\"Read\" mode=\"Allow\" />\n",
    "<capability name=\"Write\" mode=\"Allow\" />\n",
    "<capability name=\"Filter\" mode=\"Allow\" />\n",
    "<capability name=\"ViewUnderlyingData\" mode=\"Allow\" />\n",
    "<capability name=\"ExportImage\" mode=\"Allow\" />\n",
    "<capability name=\"ExportData\" mode=\"Allow\" />\n",
    "<capability name=\"AddComment\" mode=\"Allow\" />\n",
    "<capability name=\"ViewComments\" mode=\"Allow\" />\n",
    "<capability name=\"Delete\" mode=\"Allow\" />\n",
    "<capability name=\"ChangePermissions\" mode=\"Allow\" />\n",
    "<capability name=\"ExportXml\" mode=\"Allow\" />\n",
    "<capability name=\"ChangeHierarchy\" mode=\"Allow\" />\n",
    "<capability name=\"ShareView\" mode=\"Allow\" />\n",
    "<capability name=\"WebAuthoring\" mode=\"Allow\" />\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_workbook_permissions_body(workbook_id, group_id):\n",
    "    string = '''<tsRequest>\n",
    "      <permissions>\n",
    "        <workbook id=\"{workbook_id}\" />\n",
    "        <granteeCapabilities>\n",
    "          <group id=\"{group_id}\" />\n",
    "          <capabilities>\n",
    "            <capability name=\"ViewUnderlyingData\" mode=\"Deny\" />\n",
    "            <capability name=\"WebAuthoring\" mode=\"Deny\" />\n",
    "          </capabilities>\n",
    "        </granteeCapabilities>\n",
    "      </permissions>\n",
    "    </tsRequest>'''.format(workbook_id=workbook_id, group_id=group_id)\n",
    "    \n",
    "    return string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook_ids = [\"f58ea8bc-5ddd-48ac-99bf-47b97bc1c2a1\"]\n",
    "group_ids = [\n",
    "    \"d2e6f150-6996-4868-a2e8-ad922f791730\", # GDPR Name Viewers\n",
    "    \"3ae9cef0-1ffe-46f9-8532-1534461d9bd5\", # GDPR PI Data Viewers\n",
    "    \"0e5b78b1-a662-456d-b546-b8d6a69c6f9d\" # GDPR PIS Data Viewers\n",
    "]\n",
    "\n",
    "for workbook_id in workbook_ids:\n",
    "    for group_id in group_ids:\n",
    "        headers = {'X-Tableau-Auth': auth_token}\n",
    "        payload = create_workbook_permissions_body(workbook_id, group_id)\n",
    "        url = create_workbook_permissions_url(workbook_id)\n",
    "        r = requests.put(url,\n",
    "                         headers=headers,\n",
    "                         data=payload,\n",
    "                         verify=False)\n",
    "        printy_pretty_xml(r.text)\n",
    "        print(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get groups via users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Maybe we can infer the groups that exist by looking at properties of users?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY_USERS_URL = BASE_URL + \"sites/{site_id}/users\".format(site_id = site_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY_USERS_URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get(QUERY_USERS_URL, headers=headers, verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "untangled_users = untangle.parse(r.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "y = untangled_users.tsResponse.users.user[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "y.get_elements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for user in untangled_users.tsResponse.users.user:\n",
    "    print(user['name'] + \" \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sign out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "BASE_URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SIGN_OUT_URL = BASE_URL + \"auth/signout\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = {'X-Tableau-Auth': auth_token}\n",
    "\n",
    "# r = requests.post(SIGN_OUT_URL, headers=headers, verify=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tableau-server-controller",
   "language": "python",
   "name": "tableau-server-controller"
  },
  "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}