devops-pipelines/ja.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Jobagent Investigation\n",
    "1. Run all cells.\n",
    "1. View report at the bottom."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "inputHidden": false,
    "outputHidden": false,
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "su = \"tfs-wcus-0\"\n",
    "start = \"2019-08-08T23:50:00.0000000Z\"\n",
    "end = \"2019-08-09T00:24:36.0000000Z\"\n",
    "url = \"https://notebooksv2.azure.com/yaananth/projects/06OasuNRs6rK/delays.ipynb\"\n",
    "baseUrl = \"https://notebooksv2.azure.com/yaananth/projects/06OasuNRs6rK\"\n",
    "service = \"tfs\"\n",
    "hub = \"Build\"\n",
    "locationName = \"tfsprodwcus0\"\n",
    "mdmAccount = \"VSO-TFS\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "inputHidden": false,
    "outputHidden": false
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "!pip install --upgrade nimport azure-kusto-notebooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "inputHidden": false,
    "outputHidden": false
   },
   "outputs": [],
   "source": [
    "# Import the things we use\n",
    "\n",
    "# Note you can also use kql https://docs.microsoft.com/en-us/azure/data-explorer/kqlmagic\n",
    "# %kql is single line magic\n",
    "# %%kql is cell magic\n",
    "\n",
    "# https://nbviewer.jupyter.org/github/ipython/ipython/blob/4.0.x/examples/IPython%20Kernel/Rich%20Output.ipynb#HTML\n",
    "# https://ipython.readthedocs.io/en/stable/inte/magics.html\n",
    "from IPython.display import display, HTML, Markdown, Javascript, clear_output\n",
    "\n",
    "# http://pandas-docs.github.io/pandas-docs-travis/user_guide/reshaping.html\n",
    "import pandas as pd\n",
    "pd.options.display.html.table_schema = True\n",
    "from pandas import Series, DataFrame\n",
    "from datetime import datetime, timedelta, timezone\n",
    "from urllib.parse import urlencode, quote_plus\n",
    "from requests.utils import requote_uri\n",
    "import time\n",
    "import numpy as np\n",
    "from matplotlib import pyplot as plt\n",
    "from nimport.utils import tokenize, open_nb\n",
    "import json\n",
    "import os\n",
    "import calendar as cal\n",
    "import concurrent.futures\n",
    "from azure.kusto.notebooks import utils as akn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "inputHidden": false,
    "outputHidden": false
   },
   "outputs": [],
   "source": [
    "params = {\n",
    "    \"su\": su,\n",
    "    \"start\": start,\n",
    "    \"end\": end,\n",
    "    \"url\": url,\n",
    "    \"baseUrl\": baseUrl,\n",
    "    \"service\": service\n",
    "}\n",
    "root = 'devops-pipelines' if os.path.basename(os.getcwd()) != 'devops-pipelines' else ''\n",
    "queryPath = os.path.join(root, 'queries')   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "inputHidden": false,
    "outputHidden": false
   },
   "outputs": [],
   "source": [
    "# authenticate kusto client\n",
    "# you will need to copy the token into a browser window for AAD auth. \n",
    "client = akn.get_client('https://vso.kusto.windows.net')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "inputHidden": false,
    "outputHidden": false
   },
   "outputs": [],
   "source": [
    "q_slow = os.path.join(queryPath, \"SlowActivities.csl\")\n",
    "q_sqlSlow = os.path.join(queryPath, \"SlowSql.csl\")\n",
    "\n",
    "jaPath = os.path.join(queryPath, 'ja')\n",
    "q_jobSql = os.path.join(jaPath, \"JASqlTime.csl\")\n",
    "with concurrent.futures.ThreadPoolExecutor() as executor:\n",
    "    p1 = executor.submit(akn.execute_file, client, 'VSO', q_slow, params)\n",
    "    p2 = executor.submit(akn.execute_file, client, 'VSO', q_sqlSlow, params)\n",
    "    p3 = executor.submit(akn.execute_file, client, 'VSO', q_jobSql, params)\n",
    "\n",
    "q_slowResult_df = akn.to_dataframe_from_future(p1)\n",
    "\n",
    "q_sqlSlowResult_df = akn.to_dataframe_from_future(p2)\n",
    "\n",
    "q_jobSqlResult_df = akn.to_dataframe_from_future(p3)    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "inputHidden": false,
    "outputHidden": false
   },
   "outputs": [],
   "source": [
    "print('=' * 50)\n",
    "print('Report!')\n",
    "print('=' * 50, '\\n\\n')\n",
    "\n",
    "# jarvis params\n",
    "jarvisParams = {\n",
    "  'su': su, \n",
    "  'start': akn.get_time(start, -10), \n",
    "  'end': akn.get_time(end, 10), \n",
    "  'service': service,\n",
    "  'location': locationName,\n",
    "  'account': \"mdmAccount\"\n",
    "}\n",
    "\n",
    "jaJarvisLink = \"\"\"https://jarvis-west.dc.ad.msft.net/dashboard/VSO-ServiceInsights/PlatformViews/Compute-JA\"\"\" \\\n",
    "    \"\"\"?overrides=[{\"query\":\"//*[id='Service']\",\"key\":\"value\",\"replacement\":\"%(service)s\"},\"\"\" \\\n",
    "    \"\"\"{\"query\":\"//*[id='RoleInstance']\",\"key\":\"value\",\"replacement\":\"\"},\"\"\" \\\n",
    "    \"\"\"{\"query\":\"//*[id='LocationName']\",\"key\":\"value\",\"replacement\":\"%(location)s\"},\"\"\" \\\n",
    "    \"\"\"{\"query\":\"//dataSources\",\"key\":\"namespace\",\"replacement\":\"%(su)s\"},\"\"\" \\\n",
    "    \"\"\"{\"query\":\"//dataSources\",\"key\":\"account\",\"replacement\":\"%(account)s\"},\"\"\" \\\n",
    "    \"\"\"{\"query\":\"//*[id='ApplicationEndpoint']\",\"key\":\"regex\",\"replacement\":\"*%(location)s*\"},\"\"\" \\\n",
    "    \"\"\"{\"query\":\"//*[id='ScaleUnit']\",\"key\":\"value\",\"replacement\":\"%(su)s\"}]\"\"\" \\\n",
    "    \"\"\"&globalStartTime=%(start)s&globalEndTime=%(end)s&pinGlobalTimeRange=true\"\"\" % jarvisParams;\n",
    "print('Jarvis dashboard link for job agents:\\n', requote_uri(jaJarvisLink), '\\n')\n",
    "\n",
    "print('Top slow activities:')\n",
    "display(q_slowResult_df)\n",
    "\n",
    "print('Top sql slow activities:')\n",
    "display(q_sqlSlowResult_df)\n",
    "\n",
    "print('Top sql executime times from jobs:')\n",
    "display(q_jobSqlResult_df)"
   ]
  }
 ],
 "metadata": {
  "kernel_info": {
   "name": "python3"
  },
  "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.7.4"
  },
  "nteract": {
   "version": "0.15.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}