m365_user_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a711da59d7e14e5aa9cb255e3659d561",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Dropdown(description='Top N stats', options=('10', '20', '30', '40', '50'), style=DescriptionStyle(description…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "186adc1c50284005a302deb4dc08a4c5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "ToggleButtons(button_style='info', description='Data type:', index=2, layout=Layout(width='520px'), options=('…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6f968db0d6b8452988460a04863b0c98",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "IntRangeSlider(value=(0, 168), description='Time range in hours (max 1 week from the first event)', layout=Lay…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "50a929ce3ba44b7aaa4b7e1280d00d4b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Button(button_style='success', description='Apply changes', style=ButtonStyle(), tooltip='Apply changes')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e398adb51c394ef6993bafd66216903a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(JupyterChart(spec={'config': {'view': {'continuousWidth': 300, 'continuousHeight': 300}}, 'data…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import clear_output\n",
    "\n",
    "import panel as pn\n",
    "import ipywidgets as widgets\n",
    "import pandas as pd\n",
    "import altair as alt\n",
    "import pathlib\n",
    "import datetime\n",
    "from vega_datasets import data\n",
    "import os\n",
    "import pycountry\n",
    "\n",
    "LOCATION = \"Country\"\n",
    "TIMESTAMP = \"Creation Time\"\n",
    "COUNT = \"COUNT\"\n",
    "IP_ADDR = \"Client IP Address\"\n",
    "\n",
    "TIME_BIN = \"Time since first flow\"\n",
    "\n",
    "#User options\n",
    "LOG_MAP = \"Log in map\"\n",
    "LOG_CHART = \"Log in chart\"\n",
    "SINGIN_ATTEMPTS = \"Sign in attempts\"\n",
    "\n",
    "    \n",
    "    \n",
    "prefix_file_path = os.path.join(\"Task 2\", \"536535_data\", \"536535_data\")\n",
    "events_file_path = os.path.join(prefix_file_path, \"m365_user_sessions\", \"events.csv\")\n",
    "sign_ins_file_path = os.path.join(prefix_file_path, \"ms365_user_analysis\", \"sign_ins.csv\")\n",
    "dir_path = pathlib.Path().resolve()\n",
    "\n",
    "sign_in_data: pd.DataFrame = pd.DataFrame(pd.read_csv(dir_path / sign_ins_file_path))\n",
    "\n",
    "pn.extension('vega')\n",
    "\n",
    "style = {'description_width': 'initial'}\n",
    "\n",
    "top_n_widget = widgets.Dropdown(\n",
    "    options=['10', '20', '30', '40', '50'],\n",
    "    value='10',\n",
    "    description='Top N stats',\n",
    "    disabled=False,\n",
    "    style = style,\n",
    ")\n",
    "\n",
    "data_type_widget = widgets.ToggleButtons(\n",
    "    options=[LOG_MAP, LOG_CHART, \n",
    "             SINGIN_ATTEMPTS],\n",
    "    description='Data type:',\n",
    "    disabled=False,\n",
    "    button_style='info', # 'success', 'info', 'warning', 'danger' or ''\n",
    "    style = {'button_width':'200px'}, layout={'width': '520px'},\n",
    ")\n",
    "\n",
    "apply_changes_widget = widgets.Button(\n",
    "    description='Apply changes',\n",
    "    tooltip='Apply changes',\n",
    "    disabled=False,\n",
    "    button_style='success', # 'success', 'info', 'warning', 'danger' or ''\n",
    "    style = style,\n",
    ")\n",
    "period_widget = widgets.IntRangeSlider(\n",
    "    min=0,\n",
    "    max=168,\n",
    "    value=[0, 168],\n",
    "    step=1,\n",
    "    description=\"Time range in hours (max 1 week from the first event)\",\n",
    "    disabled=False,\n",
    "    style = style,\n",
    "    layout={'width': '520px'}\n",
    ")\n",
    "\n",
    "table_widget = widgets.HTML(value=sign_in_data.iloc[:0].to_html())\n",
    "\n",
    "ui_widgets = [top_n_widget, data_type_widget, \n",
    "              period_widget, apply_changes_widget,\n",
    "              ]\n",
    "\n",
    "def get_first_time_flow_string():\n",
    "    return sign_in_data[TIMESTAMP].min()\n",
    "\n",
    "def get_last_time_flow_string():\n",
    "    return sign_in_data[TIMESTAMP].max()\n",
    "\n",
    "def get_first_time_flow():\n",
    "    return datetime.datetime.fromisoformat(get_first_time_flow_string())\n",
    "\n",
    "def get_last_time_flow():\n",
    "    return datetime.datetime.fromisoformat(get_last_time_flow_string())\n",
    "\n",
    "def to_datetime(x):\n",
    "    return datetime.datetime.fromisoformat(x)\n",
    "\n",
    "FIRST_TIMESTAMP = get_first_time_flow()\n",
    "FIRST_TIMESTAMP_ROUNDED_DOWN = FIRST_TIMESTAMP.replace(second=0, minute=0)\n",
    "LAST_TIMESTAMP = get_last_time_flow()\n",
    "\n",
    "def to_hours_since_start(x):\n",
    "    curr_time = to_datetime(x) - FIRST_TIMESTAMP_ROUNDED_DOWN\n",
    "    return curr_time.total_seconds() // 3600\n",
    "\n",
    "def plus_start_time(x):\n",
    "    return FIRST_TIMESTAMP + datetime.timedelta(hours=to_hours_since_start(x))\n",
    "\n",
    "def display_widgets():\n",
    "    for ui_widget in ui_widgets:\n",
    "        display(ui_widget)\n",
    "\n",
    "def process_world_map_location(current_data: pd.DataFrame):\n",
    "    if current_data.empty:\n",
    "        return current_data\n",
    "\n",
    "    world_map_data = pd.DataFrame(current_data)\n",
    "\n",
    "    world_map_data['country_iso_numeric'] = world_map_data[LOCATION].apply(lambda x : pycountry.countries.get(alpha_2 = x).numeric)\n",
    "    world_map_data = world_map_data.assign(country_count=world_map_data[LOCATION] + \" - \" + world_map_data[COUNT].astype(str))\n",
    "    return world_map_data\n",
    "    \n",
    "def process_location(current_data: pd.DataFrame):\n",
    "    if current_data.empty:\n",
    "        return current_data\n",
    "    return current_data.groupby(LOCATION)[LOCATION].size().reset_index(name=COUNT)\n",
    "\n",
    "def process_by_top_n(current_data, top_n):\n",
    "    if current_data.empty:\n",
    "        return current_data\n",
    "    return current_data.nlargest(top_n, columns=COUNT)\n",
    "\n",
    "def process_data_geo(current_data, top_n, time_period, projection_type):\n",
    "    if current_data.empty:\n",
    "        return current_data\n",
    "\n",
    "    geo_data = pd.DataFrame(current_data)\n",
    "\n",
    "    geo_data = process_by_time_range(geo_data, time_period)\n",
    "    geo_data = process_location(geo_data)\n",
    "\n",
    "    if projection_type == \"map\":\n",
    "        geo_data = process_world_map_location(geo_data)\n",
    "\n",
    "    geo_data = process_by_top_n(geo_data, top_n)\n",
    "    return geo_data\n",
    "\n",
    "def process_by_time_range(current_data, time_period):\n",
    "    if current_data.empty:\n",
    "        return current_data\n",
    "    \n",
    "    start_offset, end_offset = time_period\n",
    "\n",
    "\n",
    "    start = get_first_time_flow() + datetime.timedelta(hours=start_offset)\n",
    "    end = get_first_time_flow() + datetime.timedelta(hours=end_offset)\n",
    "\n",
    "    return current_data[(current_data[TIMESTAMP].apply(to_datetime) >= start) &\n",
    "                      ((current_data[TIMESTAMP].apply(to_datetime)) <= end)]\n",
    "\n",
    "\n",
    "def process_to_time_plot(current_data):\n",
    "    time_plot_data = pd.DataFrame(current_data)\n",
    "\n",
    "    return time_plot_data.groupby([TIMESTAMP, IP_ADDR]).size().reset_index(name=COUNT)\n",
    "    \n",
    "def process_data_sign(current_data, top_n, time_period):\n",
    "    if current_data.empty:\n",
    "        return current_data\n",
    "\n",
    "    processed_data = process_by_time_range(current_data, time_period)\n",
    "    processed_data[TIME_BIN] = processed_data[TIMESTAMP].apply(plus_start_time)\n",
    "\n",
    "    return processed_data\n",
    "    \n",
    "\n",
    "def make_world_map(world_map_data):\n",
    "    source = alt.topo_feature(data.world_110m.url, \"countries\")\n",
    "    brush = alt.selection_interval(\n",
    "        encodings=[\"longitude\", \"latitude\"],\n",
    "        empty=False,\n",
    "        value={\"longitude\": [-50, -110],\n",
    "               \"longidute\": [-50, -110]}\n",
    "    )\n",
    "    background = alt.Chart(source).mark_geoshape(fill=\"white\", \n",
    "                                                 stroke=\"lightgrey\").add_params(brush)\n",
    "    selection = alt.selection_point(fields=[COUNT], bind='legend')\n",
    "    foreground = (\n",
    "        alt.Chart(source)\n",
    "        .mark_geoshape(stroke=\"black\", strokeWidth=0.15)\n",
    "        .transform_lookup(\n",
    "            lookup=\"id\",\n",
    "            from_=alt.LookupData(world_map_data, \"country_iso_numeric\", [COUNT, LOCATION]),\n",
    "        )\n",
    "        .transform_calculate(\n",
    "            legend_label=\"datum.Country + ' - ' + datum.COUNT\",\n",
    "            lon=\"datum.geometry.coordinates[0]\",\n",
    "            lat=\"datum.geometry.coordinates[1]\",\n",
    "        )\n",
    "        .encode(\n",
    "            color=alt.Color(\n",
    "                field=COUNT,\n",
    "                scale=alt.Scale(scheme=\"lightgreyred\"),\n",
    "                legend=alt.Legend(\n",
    "                    title=\"Country - Log in Attempts\",\n",
    "                    labelExpr=\"datum.label\",\n",
    "                ),\n",
    "            ),\n",
    "            opacity=alt.when(selection).then(alt.value(1)).otherwise(alt.value(0.1)),\n",
    "            longitude=\"Longitude:Q\",\n",
    "            latitude=\"Latitude:Q\",\n",
    "            tooltip=[\n",
    "                alt.Tooltip(f\"{LOCATION}:N\", title=\"Country\"),\n",
    "                alt.Tooltip(f\"{COUNT}:Q\", title=\"Log in attempts\"),\n",
    "            ],            \n",
    "            \n",
    "        ).add_params(\n",
    "            selection,\n",
    "            brush\n",
    "        )\n",
    "    )\n",
    "\n",
    "    final_map = (\n",
    "        (background + foreground)\n",
    "        .configure_view(strokeWidth=0)\n",
    "        .properties(width=800, height=500)\n",
    "        .project(\"naturalEarth1\")\n",
    "        \n",
    "    )\n",
    "    final_map.add_params(brush)\n",
    "    \n",
    "    return alt.JupyterChart(final_map)\n",
    "    \n",
    "def make_location_graph(graph_data, top_n):\n",
    "    tooltips = [COUNT]\n",
    "\n",
    "    brush = alt.selection_interval(name=\"brush\")\n",
    "\n",
    "    graph = alt.Chart(graph_data).mark_bar().encode(\n",
    "        x=graph_data.columns[1],\n",
    "        y=alt.Y(graph_data.columns[0], sort='-x'),\n",
    "        tooltip=tooltips,\n",
    "        color=alt.Color(graph_data.columns[1],\n",
    "                   scale=alt.Scale(range=['lightgreen', 'green']))\n",
    "    ).properties(\n",
    "        width=600,\n",
    "        height=1000 if top_n >= 30 else 600,\n",
    "        autosize=alt.AutoSizeParams(\n",
    "            type='fit',\n",
    "            contains='padding'\n",
    "        ),\n",
    "    ).add_params(brush)\n",
    "    \n",
    "    return alt.JupyterChart(graph)\n",
    "\n",
    "def make_time_plot(processed_data, top_n):\n",
    "    brush = alt.selection_point(name=\"brush\")\n",
    "    time_plot = alt.Chart(processed_data).mark_line(point=True).encode(\n",
    "        alt.X(f\"{TIME_BIN}:T\", bin=alt.Bin(maxbins=168, binned=True), timeUnit=\"yearmonthdatehours\"),\n",
    "        y='count()',\n",
    "        tooltip=[alt.Tooltip(f\"count()\", title=\"Sign in attempts\"),\n",
    "                 alt.Tooltip(f\"{TIME_BIN}\", title=\"Time\", timeUnit=\"yearmonthdatehours\")],\n",
    "        color=alt.Color(\"count()\",\n",
    "                   scale=alt.Scale(range=['lightgreen', 'green'])),\n",
    "    ).properties(\n",
    "        title=f\"Distribution of sign in attempts in time\",    \n",
    "        width=800, \n",
    "        height=500,\n",
    "    ).add_params(brush)\n",
    "    return alt.JupyterChart(time_plot)\n",
    "\n",
    "def on_select_country(change):\n",
    "    sel = change.new.value\n",
    "    if sel is None or LOCATION not in sel:\n",
    "        filtered = sign_in_data.iloc[:0]\n",
    "    else:\n",
    "        filtered = sign_in_data[sign_in_data[LOCATION].isin(sel[LOCATION])]\n",
    "\n",
    "    table_widget.value = filtered.to_html()\n",
    "\n",
    "def on_select_time_range(change):\n",
    "    sel = change.new.value\n",
    "    if sel is None or \"yearmonthdatehours_Time since first flow\" not in sel:\n",
    "        filtered = sign_in_data.iloc[:0]\n",
    "    else:\n",
    "        start = datetime.datetime.fromtimestamp(sel[\"yearmonthdatehours_Time since first flow\"][0] / 1000, datetime.timezone.utc) - FIRST_TIMESTAMP_ROUNDED_DOWN \n",
    "        end =  datetime.datetime.fromtimestamp(sel[\"yearmonthdatehours_Time since first flow\"][1] / 1000, datetime.timezone.utc) - FIRST_TIMESTAMP_ROUNDED_DOWN \n",
    "        \n",
    "        time_range = (start.total_seconds() // 3600, end.total_seconds() // 3600)\n",
    "        filtered = process_by_time_range(sign_in_data, time_range)\n",
    "\n",
    "    table_widget.value = filtered.to_html()\n",
    "\n",
    "def on_change(v):\n",
    "    clear_output(wait=True)\n",
    "    top_n = int(top_n_widget.value)\n",
    "    display_widgets()\n",
    "\n",
    "    if data_type_widget.value in [LOG_MAP, LOG_CHART]:\n",
    "        processed_data = process_data_geo(sign_in_data, top_n, period_widget.value, \"map\")\n",
    "\n",
    "    if data_type_widget.value == SINGIN_ATTEMPTS:\n",
    "        processed_data = process_data_sign(sign_in_data, top_n, period_widget.value)\n",
    "    \n",
    "    if processed_data.empty:\n",
    "        print(\"Log: The data set is empty\")\n",
    "        return\n",
    "\n",
    "    if (data_type_widget.value == LOG_MAP):\n",
    "        plot: alt.JupyterChart = make_world_map(processed_data)\n",
    "        plot.selections.observe(on_select_country, [\"brush\"])\n",
    "\n",
    "    elif data_type_widget.value == LOG_CHART:\n",
    "        plot: alt.JupyterChart = make_location_graph(processed_data, top_n)\n",
    "        plot.selections.observe(on_select_country, [\"brush\"])\n",
    "\n",
    "    elif data_type_widget.value == SINGIN_ATTEMPTS:\n",
    "        plot: alt.JupyterChart = make_time_plot(processed_data, top_n)\n",
    "        plot.selections.observe(on_select_time_range, [\"brush\"])\n",
    "\n",
    "    \n",
    "    display(widgets.VBox([plot, table_widget]))\n",
    "\n",
    "\n",
    "display_widgets()\n",
    "apply_changes_widget.on_click(on_change)\n",
    " "
   ]
  }
 ],
 "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}