camp2023.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb73216d-ab7b-4fdd-bd68-8750297f7dc5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import altair as alt\n",
    "\n",
    "from tqdm import tqdm\n",
    "\n",
    "tqdm.pandas()\n",
    "pd.options.display.max_columns = None\n",
    "pd.options.display.max_rows = 100\n",
    "alt.data_transformers.disable_max_rows()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a8b35819-ad4b-4e14-a7d9-5c3966a8c336",
   "metadata": {},
   "outputs": [],
   "source": [
    "from clickhouse_driver import Client as Clickhouse\n",
    "\n",
    "def click_query(q, **kw):\n",
    "    click = Clickhouse(user=\"ccc\", password='happycamper', host=\"oonidata.ooni.org\", port=9000)\n",
    "    return click.query_dataframe(q, params=kw)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "660f0e36-7018-42d2-bde2-c8461e7b92a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>default_type</th>\n",
       "      <th>default_expression</th>\n",
       "      <th>comment</th>\n",
       "      <th>codec_expression</th>\n",
       "      <th>ttl_expression</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>measurement_uid</td>\n",
       "      <td>String</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>input</td>\n",
       "      <td>Nullable(String)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>report_id</td>\n",
       "      <td>String</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>measurement_start_time</td>\n",
       "      <td>DateTime64(6)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>software_name</td>\n",
       "      <td>String</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>pp_http_response_matches_false_positive</td>\n",
       "      <td>Int8</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>pp_http_response_body_title</td>\n",
       "      <td>Nullable(String)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>pp_http_response_body_meta_title</td>\n",
       "      <td>Nullable(String)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>pp_dns_fingerprint_id</td>\n",
       "      <td>Nullable(String)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>pp_dns_fingerprint_country_consistent</td>\n",
       "      <td>Nullable(Int8)</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       name              type default_type  \\\n",
       "0                           measurement_uid            String                \n",
       "1                                     input  Nullable(String)                \n",
       "2                                 report_id            String                \n",
       "3                    measurement_start_time     DateTime64(6)                \n",
       "4                             software_name            String                \n",
       "..                                      ...               ...          ...   \n",
       "95  pp_http_response_matches_false_positive              Int8                \n",
       "96              pp_http_response_body_title  Nullable(String)                \n",
       "97         pp_http_response_body_meta_title  Nullable(String)                \n",
       "98                    pp_dns_fingerprint_id  Nullable(String)                \n",
       "99    pp_dns_fingerprint_country_consistent    Nullable(Int8)                \n",
       "\n",
       "   default_expression comment codec_expression ttl_expression  \n",
       "0                                                              \n",
       "1                                                              \n",
       "2                                                              \n",
       "3                                                              \n",
       "4                                                              \n",
       "..                ...     ...              ...            ...  \n",
       "95                                                             \n",
       "96                                                             \n",
       "97                                                             \n",
       "98                                                             \n",
       "99                                                             \n",
       "\n",
       "[100 rows x 7 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "click_query(\"DESCRIBE TABLE obs_web\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1433e0d-ed54-425d-8837-67192cb4fcee",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "target_domains = [\n",
    "    \"ooni.org\",\n",
    "    \"ooni.io\",\n",
    "    \"explorer.ooni.org\",\n",
    "    \"explorer.ooni.io\",\n",
    "    \"ooni.torproject.org\"\n",
    "]\n",
    "df_full = click_query(\"\"\"\n",
    "SELECT *\n",
    "FROM obs_web \n",
    "WHERE probe_cc = 'CN' \n",
    "AND measurement_start_time > '2023-07-01'\n",
    "AND hostname IN %(target_domains)s\n",
    "\"\"\", target_domains=target_domains)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e5f2aa4-7988-4598-a8d9-83f5dfac5069",
   "metadata": {},
   "outputs": [],
   "source": [
    "MEASUREMENT_START_DAY = '2023-01-01'\n",
    "MEASUREMENT_END_DAY = '2023-07-01'\n",
    "ANALYSIS_COUNTRY_CODES = [\n",
    "    \"CM\",\n",
    "    \"HK\",\n",
    "    \"ID\",\n",
    "    \"MY\",\n",
    "    \"MM\",\n",
    "    \"PH\",\n",
    "    \"TH\",\n",
    "    \"VN\"\n",
    "]\n",
    "ANALYSIS_DOMAINS = [\n",
    "    \"www.pornhub.com\",\n",
    "    \"xhamster.com\",\n",
    "    \"www.betfair.com\",\n",
    "    \"www.slotland.com\",\n",
    "    \"www.torproject.org\",\n",
    "    \"ilga.org\",\n",
    "    \"vimeo.com\",\n",
    "    \"imgur.com\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30cf014e-8b06-48fc-aa19-305e6c4b9dbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df_with_failure = click_query(\"\"\"\n",
    "WITH multiIf(\n",
    "    dns_failure IS NOT NULL, tuple('dns', dns_failure),\n",
    "    tcp_failure IS NOT NULL, tuple('tcp', tcp_failure),\n",
    "    tls_failure IS NOT NULL, tuple('tls', tls_failure),\n",
    "    http_failure IS NOT NULL, tuple('https', http_failure),\n",
    "    tuple('ok', '')\n",
    ") as failure\n",
    "SELECT \n",
    "report_id,\n",
    "input,\n",
    "measurement_uid,\n",
    "probe_cc,\n",
    "probe_asn,\n",
    "probe_as_org_name,\n",
    "probe_as_cc,\n",
    "network_type,\n",
    "measurement_start_time,\n",
    "hostname,\n",
    "ip,\n",
    "port,\n",
    "ip_asn,\n",
    "ip_as_org_name,\n",
    "resolver_ip,\n",
    "resolver_cc,\n",
    "resolver_asn,\n",
    "resolver_as_org_name,\n",
    "resolver_as_cc,\n",
    "dns_engine,\n",
    "dns_failure,\n",
    "dns_answer,\n",
    "tcp_success,\n",
    "tcp_failure,\n",
    "tls_handshake_time,\n",
    "tls_handshake_read_count,\n",
    "tls_handshake_write_count,\n",
    "tls_handshake_read_bytes,\n",
    "tls_handshake_write_bytes,\n",
    "tls_handshake_last_operation,\n",
    "tls_cipher_suite IS NOT NULL as tls_success,\n",
    "tls_is_certificate_valid,\n",
    "tls_end_entity_certificate_subject,\n",
    "tls_end_entity_certificate_subject_common_name,\n",
    "tls_end_entity_certificate_issuer,\n",
    "tls_end_entity_certificate_issuer_common_name,\n",
    "tls_end_entity_certificate_san_list,\n",
    "tls_end_entity_certificate_not_valid_after,\n",
    "tls_end_entity_certificate_not_valid_before,\n",
    "tls_certificate_chain_length,\n",
    "tls_failure,\n",
    "http_request_url,\n",
    "http_failure,\n",
    "http_runtime,\n",
    "failure.1 as failure_class,\n",
    "IF(failure_class = 'ok', 'ok', concat(failure_class, '.', failure_str)) as failure_str_full,\n",
    "IF(startsWith(failure.2, 'unknown_failure'), 'unknown_failure', failure.2) as failure_str,\n",
    "failure.2 as failure_str_raw\n",
    "FROM obs_web\n",
    "WHERE measurement_start_time > %(measurement_start_day)s\n",
    "AND measurement_start_time < %(measurement_end_day)s\n",
    "AND hostname IN %(domain_list)s\n",
    "AND probe_cc IN %(cc_list)s\n",
    "\"\"\", **{\n",
    "    \"measurement_start_day\": MEASUREMENT_START_DAY,\n",
    "    \"measurement_end_day\": MEASUREMENT_END_DAY,\n",
    "    \"domain_list\": ANALYSIS_DOMAINS,\n",
    "    \"cc_list\": ANALYSIS_COUNTRY_CODES,\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "673b48ec-e69c-48e4-8c4c-1c4035e1332f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# see: https://learn.microsoft.com/en-us/windows/win32/winsock/windows-sockets-error-codes-2\n",
    "unknown_failure_map = {\n",
    "    ': server misbehaving': 'dns_server_misbehaving',\n",
    "    ': read: connection refused': 'connection_refused',\n",
    "    ': connect: network is unreachable': 'network_unreachable',\n",
    "    'tls: first record does not look like a TLS handshake': 'tls_bad_first_record',\n",
    "    'remote error: tls: handshake failure': 'tls_handshake_failure',\n",
    "    'remote error: tls: illegal parameter': 'tls_illegal_parameter',\n",
    "    'connectex: No connection could be made because the target machine actively refused it': 'connection_refused',\n",
    "    'read: connection refused': 'connection_refused',\n",
    "    'remote error: tls: access denied': 'tls_access_denied',\n",
    "    'remote error: tls: internal error': 'tls_internal_error',\n",
    "    'HTTP/1.x transport connection broken: malformed HTTP version': 'http_malformed_response',\n",
    "    'net/http: timeout awaiting response headers': 'http_timeout',\n",
    "    'read: operation timed out': 'timed_out',\n",
    "    'connect: operation timed out': 'timed_out',\n",
    "    ': No address associated with hostname': 'dns_nxdomain_error',\n",
    "    ': connect: bad file descriptor': 'bad_file_descriptor',\n",
    "    'stream error: stream ID': 'http_stream_error',\n",
    "    \n",
    "    # This looks more like a golang-bug: https://github.com/golang/go/issues/31259\n",
    "    'readLoopPeekFailLocked: <nil>': 'http_golang_bug',\n",
    "\n",
    "    ': connect: no route to host': 'host_unreachable',\n",
    "    ': connect: cannot assign requested address': 'address_not_available',\n",
    "    'getaddrinfow: The requested name is valid, but no data of the requested type was found.': 'dns_no_answer',\n",
    "    'wsarecv: Se ha forzado la interrupción de una conexión existente por el host remoto.': 'connection_reset',\n",
    "    'wsarecv: An existing connection was forcibly closed by the remote host.': 'connection_reset',\n",
    "    'wsarecv: Connessione in corso interrotta forzatamente dall\\'host remoto.': 'connection_reset',\n",
    "    'wsarecv: Uma ligação existente foi forçada a fechar pelo anfitrião remoto': 'connection_reset',\n",
    "    \n",
    "    'getaddrinfow: Ceci est habituellement une erreur temporaire qui se produit durant la résolution du nom d’hôte et qui signifie que le serveur local n’a pas reçu de réponse d’un serveur faisant autorité': 'dns_temporary_failure',\n",
    "    'getaddrinfow: Dies ist normalerweise ein zeitweiliger Fehler bei der Auflösung von Hostnamen. Grund ist, dass der lokale Server keine Rückmeldung vom autorisierenden Server erhalten hat.': 'dns_temporary_failure',\n",
    "    'getaddrinfow: Este é geralmente um erro temporário durante a resolução de nomes de anfitrião e significa que o servidor local não recebeu uma resposta de um servidor autoritário': 'dns_temporary_failure',\n",
    "    'getaddrinfow: Éste es normalmente un error temporal durante la resolución de nombres de host y significa que el servidor local no recibió una respuesta de un servidor autoritativo': 'dns_temporary_failure',\n",
    "}\n",
    "def map_unknown_failure(failure_str):\n",
    "    if not failure_str.startswith(\"unknown_failure\"):\n",
    "        return failure_str\n",
    "    for substring, clean_failure in unknown_failure_map.items():\n",
    "        if substring in failure_str:\n",
    "            return clean_failure\n",
    "    return \"unknown_failure\"\n",
    "\n",
    "def simplify_failure(failure_str):\n",
    "    if failure_str in ['timed_out', 'generic_timeout_error', 'deferred_timeout_error']:\n",
    "        return 'timeout'\n",
    "    \n",
    "    if failure_str in ['android_dns_cache_no_data', 'dns_nxdomain_error']:\n",
    "        return 'nxdomain'\n",
    "    \n",
    "    if failure_str in ['connection_refused', 'connection_refused_error']:\n",
    "        return 'connection_refused'\n",
    "        \n",
    "    return failure_str\n",
    "\n",
    "ipv6_failures = ['address_not_available', 'address_family_not_supported', 'network_unreachable', 'host_unreachable']\n",
    "def compute_analysis(row):\n",
    "    failure_str = map_unknown_failure(row['failure_str_raw'])\n",
    "\n",
    "    if not pd.isnull(row['dns_answer']) and row['dns_answer'] in known_block_ips:\n",
    "        return 'dns.confirmed'\n",
    "        \n",
    "    if row['tls_is_certificate_valid'] == True:\n",
    "        return 'ok'\n",
    "\n",
    "    if row['failure_class'] == 'ok':\n",
    "        return 'ok'\n",
    "\n",
    "    if row['dns_consistency'] == 'inconsistent':\n",
    "        return 'dns.inconsistent'\n",
    "    \n",
    "    if row['ip_as_org_name'] == 'Bogon':\n",
    "        return 'dns.bogon'\n",
    "    \n",
    "    #if row['dns_blocking_scope'] not in ('u', 'n'):\n",
    "    #    return f\"dns.{row['dns_blocking_scope']}\"\n",
    "    \n",
    "    if row['tls_is_certificate_valid'] == False:\n",
    "        return 'tls.bad_cert'\n",
    "    \n",
    "    simple_failure = simplify_failure(failure_str)\n",
    "    if simple_failure in ipv6_failures and row['ip'] and ':' in row['ip']:\n",
    "        return 'ipv6_error'\n",
    "\n",
    "    if simple_failure.startswith(\"ssl_\"):\n",
    "        simple_failure = 'bad_cert'\n",
    "    \n",
    "    prefix = row['failure_class']\n",
    "    if prefix == 'https' and simple_failure.startswith('dns_') or simple_failure == 'nxdomain':\n",
    "        prefix = 'dns'\n",
    "\n",
    "    return f'{prefix}.{simple_failure}'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f852af5d-2f24-4e4c-9d00-b016f90e7850",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_failure['analysis'] = df_with_failure.progress_apply(compute_analysis, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c572112f-82c1-4761-87ca-85817d5d769f",
   "metadata": {},
   "outputs": [],
   "source": [
    "alt.Chart(\n",
    "    df_with_failure[\n",
    "        (df_with_failure['probe_cc'] == 'TH')\n",
    "        & (df_with_failure['analysis'] != 'ipv6_error')\n",
    "        #& (df_with_failure['analysis'] == 'dns.inconsistent')\n",
    "    ]\n",
    ").mark_bar().encode(\n",
    "    x=alt.X('yearmonthdate(measurement_start_time)'),\n",
    "    y=alt.Y('count()'),\n",
    "    color=alt.Color('analysis', title='analysis'),\n",
    "    row='probe_asn',\n",
    "    column='hostname',\n",
    "    tooltip=['count()', 'probe_asn', 'measurement_start_time', 'hostname', 'analysis']\n",
    ").properties(\n",
    "    height=100\n",
    ").resolve_scale(\n",
    "    y='independent'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "353fd355-1873-4fb3-87c7-adbf6b4fb613",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fp_dns = pd.read_csv('https://raw.githubusercontent.com/ooni/blocking-fingerprints/main/fingerprints_dns.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79472c21-3bc3-4416-9980-5573d409e167",
   "metadata": {},
   "outputs": [],
   "source": [
    "known_block_ips = list(df_fp_dns['pattern'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed1c5da1-edd3-4075-85ac-f7cf2fea7b48",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "df_ctrl_dns_obs_web_ctrl = click_query(\"\"\"\n",
    "SELECT \n",
    "groupUniqArray(ip) as uniq_ip,\n",
    "groupUniqArray(ip_asn) as uniq_asn,\n",
    "groupUniqArray(ip_as_org_name) as uniq_as_org_name,\n",
    "hostname\n",
    "FROM obs_web_ctrl\n",
    "WHERE measurement_start_time > %(measurement_start_day)s\n",
    "AND hostname IN %(domain_list)s\n",
    "AND tls_success = 1 \n",
    "GROUP BY hostname\n",
    "\"\"\", **{\n",
    "    \"measurement_start_day\": MEASUREMENT_START_DAY,\n",
    "    \"domain_list\": ANALYSIS_DOMAINS,\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddd3513f-1a74-4a3d-92e1-41553ba798ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ctrl_dns_obs_web = click_query(\"\"\"\n",
    "SELECT \n",
    "groupUniqArray(ip) as uniq_ip,\n",
    "groupUniqArray(ip_asn) as uniq_asn,\n",
    "groupUniqArray(ip_as_org_name) as uniq_as_org_name,\n",
    "hostname\n",
    "FROM obs_web\n",
    "WHERE measurement_start_time > %(measurement_start_day)s\n",
    "AND hostname IN %(domain_list)s\n",
    "AND probe_cc NOT IN %(cc_list)s\n",
    "AND tls_is_certificate_valid = 1 \n",
    "GROUP BY hostname\n",
    "\"\"\", **{\n",
    "    \"measurement_start_day\": MEASUREMENT_START_DAY,\n",
    "    \"domain_list\": ANALYSIS_DOMAINS,\n",
    "    \"cc_list\": ANALYSIS_COUNTRY_CODES,\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "461c3d01-6b6b-49c7-971d-76e5861dd4a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "ctrl_map = defaultdict(dict)\n",
    "for _, row in df_ctrl_dns_obs_web_ctrl.iterrows():\n",
    "    ctrl_map[row['hostname']]['uniq_ip_ctrl'] = set(row['uniq_ip'])\n",
    "    ctrl_map[row['hostname']]['uniq_asn_ctrl'] = set(row['uniq_asn'])\n",
    "    ctrl_map[row['hostname']]['uniq_as_org_name_ctrl'] = set(row['uniq_as_org_name'])\n",
    "    \n",
    "for _, row in df_ctrl_dns_obs_web.iterrows():\n",
    "    ctrl_map[row['hostname']]['uniq_ip_web'] = set(row['uniq_ip'])\n",
    "    ctrl_map[row['hostname']]['uniq_asn_web'] = set(row['uniq_asn'])\n",
    "    ctrl_map[row['hostname']]['uniq_as_org_name_web'] = set(row['uniq_as_org_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "641c7d09-a4e0-44cf-92aa-702b3401a736",
   "metadata": {},
   "outputs": [],
   "source": [
    "def compute_dns_consistency(row):\n",
    "    if pd.isnull(row['ip']):\n",
    "        return None\n",
    "    ctrl = ctrl_map[row['hostname']]\n",
    "    if row['ip'] in ctrl['uniq_ip_ctrl']:\n",
    "        return 'consistent.ip_ctrl'\n",
    "    if row['ip'] in ctrl['uniq_ip_web']:\n",
    "        return 'consistent.ip_web'\n",
    "    if row['ip_asn'] in ctrl['uniq_asn_ctrl']:\n",
    "        return 'consistent.asn_ctrl'\n",
    "    if row['ip_asn'] in ctrl['uniq_asn_web']:\n",
    "        return 'consistent.asn_web'\n",
    "    if row['ip_as_org_name'] in ctrl['uniq_as_org_name_ctrl']:\n",
    "        return 'consistent.as_org_name_ctrl'\n",
    "    if row['ip_as_org_name'] in ctrl['uniq_as_org_name_web']:\n",
    "        return 'consistent.as_org_name_web'\n",
    "    return 'inconsistent'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05019ec1-0143-4ebc-9542-9d3e2ce4752a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_failure['dns_consistency'] = df_with_failure.progress_apply(compute_dns_consistency, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17caa9ad-317a-4b19-a110-5281ffb8b975",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_my_agg = df_my[[\n",
    "    'measurement_uid',\n",
    "    'measurement_start_time',\n",
    "    'analysis',\n",
    "    'probe_asn',\n",
    "    'hostname',\n",
    "    'probe_as_org_name'\n",
    "]].groupby(\n",
    "    [\n",
    "        pd.Grouper(freq='d', key='measurement_start_time'),\n",
    "        'analysis',\n",
    "        'probe_asn',\n",
    "        'hostname',\n",
    "        'probe_as_org_name'\n",
    "    ]\n",
    ").count().reset_index().rename(columns={'measurement_uid': 'msmt_count'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b848e8f6-02b0-437a-ab6f-4c3cb779d599",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_my_agg['network_name'] = df_my_agg.apply(lambda row: f\"{row['probe_as_org_name']} (AS{row['probe_asn']})\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95481304-77bf-4833-96af-e8b8b18680ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "color_scale = alt.Scale(\n",
    "domain=[\n",
    "    'ok',\n",
    "    'tcp.connection_aborted',\n",
    "    'tcp.timeout',\n",
    "    'tls.timeout', \n",
    "    'tls.connection_aborted',\n",
    "    'tls.connection_reset'\n",
    "],\n",
    "range=[\n",
    "    '#40c057',\n",
    "    '#3bc9db',\n",
    "    '#c92a2a',\n",
    "    '#e8590c',\n",
    "    '#ffd43b',\n",
    "    '#be4bdb'\n",
    "])\n",
    "\n",
    "alt.Chart(df_my_agg[\n",
    "    df_my_agg['analysis'] != 'ipv6_error'\n",
    "]).mark_bar().encode(\n",
    "    x='measurement_start_time',\n",
    "    y='msmt_count',\n",
    "    color=alt.Color('analysis', scale=alt.Scale(scheme='category20')),\n",
    "    column='network_name',\n",
    "    row='hostname',\n",
    "    tooltip=['msmt_count', 'network_name', 'measurement_start_time', 'hostname', 'analysis']\n",
    ").properties(\n",
    "    height=100,\n",
    "    width=400,\n",
    "    title='Domain reachabiliy in Malaysia by ISP'\n",
    ").resolve_scale(\n",
    "    y='independent'\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.17"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}