{ "cells": [ { "cell_type": "code", "execution_count": 116, "id": "67e16058-4ceb-49d4-85ee-32228d0fe9ed", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "from sqlalchemy import create_engine" ] }, { "cell_type": "code", "execution_count": 118, "id": "329ed9dc-1164-458f-8201-e4059193aace", "metadata": {}, "outputs": [], "source": [ "engine = create_engine(\n", " \"postgresql+psycopg2://airflow:airflow@localhost:5433/sdu_logs\"\n", ")" ] }, { "cell_type": "code", "execution_count": 120, "id": "83cda14d-0ad8-4384-81b6-ae970f2608f2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
042573
\n", "
" ], "text/plain": [ " count\n", "0 42573" ] }, "execution_count": 120, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"SELECT COUNT(*) FROM login_logs;\", engine)" ] }, { "cell_type": "code", "execution_count": 121, "id": "479b9ab0-eca3-4dcc-9c3e-5eb970006b07", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_iduser_ipdevice_infolog_datelogin_status
04de7a7b4681ae9f4f25e0660d75c73be72e60537a48601...176.69.24.126Mozilla/5.0 (iPhone; CPU iPhone OS 10_0_2 like...2016-12-201
1983a0b85f9d7dfa4b3ff427dcff095e11f99657255be5d...176.69.65.98Mozilla/5.0 (Linux; Android 6.0.1; SM-A310F Bu...2016-12-201
202acbe369c5065497e2f00765915ec5ad33425ce77e209...2.72.50.32Mozilla/5.0 (iPhone; CPU iPhone OS 9_2 like Ma...2016-12-201
3ae9214a08857802d079b334b898d33a006529dd787f69f...176.69.80.27Mozilla/5.0 (iPhone; CPU iPhone OS 9_3_3 like ...2016-12-221
4513aaff830179cce587900a5a04821039bd0e11c47a54a...176.69.29.159Mozilla/5.0 (Linux; Android 6.0.1; SM-J700H Bu...2016-12-261
\n", "
" ], "text/plain": [ " user_id user_ip \\\n", "0 4de7a7b4681ae9f4f25e0660d75c73be72e60537a48601... 176.69.24.126 \n", "1 983a0b85f9d7dfa4b3ff427dcff095e11f99657255be5d... 176.69.65.98 \n", "2 02acbe369c5065497e2f00765915ec5ad33425ce77e209... 2.72.50.32 \n", "3 ae9214a08857802d079b334b898d33a006529dd787f69f... 176.69.80.27 \n", "4 513aaff830179cce587900a5a04821039bd0e11c47a54a... 176.69.29.159 \n", "\n", " device_info log_date login_status \n", "0 Mozilla/5.0 (iPhone; CPU iPhone OS 10_0_2 like... 2016-12-20 1 \n", "1 Mozilla/5.0 (Linux; Android 6.0.1; SM-A310F Bu... 2016-12-20 1 \n", "2 Mozilla/5.0 (iPhone; CPU iPhone OS 9_2 like Ma... 2016-12-20 1 \n", "3 Mozilla/5.0 (iPhone; CPU iPhone OS 9_3_3 like ... 2016-12-22 1 \n", "4 Mozilla/5.0 (Linux; Android 6.0.1; SM-J700H Bu... 2016-12-26 1 " ] }, "execution_count": 121, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = pd.read_sql(\"SELECT * FROM login_logs;\", engine)\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 123, "id": "ddad5d54-a615-4eb5-a8db-c4e42bed2a52", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(42573, 5)" ] }, "execution_count": 123, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.shape" ] }, { "cell_type": "code", "execution_count": 126, "id": "3f4149c8-1583-4173-a3da-33a37082f8c2", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "user_id 0\n", "user_ip 0\n", "device_info 0\n", "log_date 0\n", "login_status 0\n", "dtype: int64" ] }, "execution_count": 126, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.isna().sum()" ] }, { "cell_type": "code", "execution_count": 128, "id": "c1ed3cd4-5ec9-454b-93b9-cc1a3fca446c", "metadata": {}, "outputs": [], "source": [ "df[\"log_date\"] = pd.to_datetime(df[\"log_date\"])" ] }, { "cell_type": "code", "execution_count": 129, "id": "3071d9c4-8b4f-46f5-9c43-4abaa62ab998", "metadata": {}, "outputs": [], "source": [ "df_clean = df[~(\n", " (df[\"user_id\"].isna() | (df[\"user_id\"] == \"\")) &\n", " (df[\"user_ip\"].isna() | (df[\"user_ip\"] == \"\")) &\n", " (df[\"device_info\"].isna() | (df[\"device_info\"] == \"\"))\n", ")]" ] }, { "cell_type": "code", "execution_count": 132, "id": "929e763e-f75f-4c66-a530-0dd33893fdd1", "metadata": {}, "outputs": [], "source": [ "df_clean = df_clean.drop_duplicates()\n" ] }, { "cell_type": "code", "execution_count": 134, "id": "129653ea-17df-46db-af2a-47910c80984c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(36854, 5)" ] }, "execution_count": 134, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_clean.shape" ] }, { "cell_type": "code", "execution_count": 136, "id": "9622ab18-2c47-420b-9434-ae1d41c83a1c", "metadata": {}, "outputs": [], "source": [ "df_clean[\"date\"] = df_clean[\"log_date\"].dt.date\n", "df_clean[\"hour\"] = df_clean[\"log_date\"].dt.hour\n", "df_clean[\"is_success\"] = df_clean[\"login_status\"] == 1" ] }, { "cell_type": "code", "execution_count": 138, "id": "b137b4d6-73dd-43cb-b5a2-3134612a0a7d", "metadata": {}, "outputs": [], "source": [ "engine = create_engine(\"postgresql+psycopg2://airflow:airflow@localhost:5433/sdu_logs\")" ] }, { "cell_type": "code", "execution_count": 140, "id": "4dec2cb0-b525-45d6-b11f-1766466991c7", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "854" ] }, "execution_count": 140, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_clean.to_sql(\"login_logs_clean\", engine, if_exists=\"replace\", index=False)" ] }, { "cell_type": "code", "execution_count": 141, "id": "1856ff6e-04ff-4ac4-813e-3c05d4aeda33", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
total_rows
036854
\n", "
" ], "text/plain": [ " total_rows\n", "0 36854" ] }, "execution_count": 141, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT COUNT(*) AS total_rows\n", "FROM login_logs_clean;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 142, "id": "56ae0df0-0dd6-4bcd-815b-732039bc0f6c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
min_datemax_date
02016-12-162024-11-25
\n", "
" ], "text/plain": [ " min_date max_date\n", "0 2016-12-16 2024-11-25" ] }, "execution_count": 142, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT MIN(log_date) AS min_date,\n", " MAX(log_date) AS max_date\n", "FROM login_logs_clean;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 143, "id": "fb545a82-aa2f-472c-a9ba-bac41db5131e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
login_statuscnt
0134735
102090
2229
\n", "
" ], "text/plain": [ " login_status cnt\n", "0 1 34735\n", "1 0 2090\n", "2 2 29" ] }, "execution_count": 143, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT login_status, COUNT(*) AS cnt\n", "FROM login_logs_clean\n", "GROUP BY login_status\n", "ORDER BY cnt DESC;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 144, "id": "b2548701-b14c-4e9e-a084-443bb8f3513b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
unique_usersunique_ips
01271822361
\n", "
" ], "text/plain": [ " unique_users unique_ips\n", "0 12718 22361" ] }, "execution_count": 144, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT\n", " COUNT(DISTINCT NULLIF(user_id,'')) AS unique_users,\n", " COUNT(DISTINCT NULLIF(user_ip,'')) AS unique_ips\n", "FROM login_logs_clean;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 149, "id": "09f34764-f54d-4e00-b00a-a575278c2fa9", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_ipattempts
010.100.1.45265
110.100.0.2502124
268.183.65.1101950
394.247.128.1451149
491.200.84.145159
510.100.0.240156
678.40.108.108111
7195.133.146.1482
\n", "
" ], "text/plain": [ " user_ip attempts\n", "0 10.100.1.4 5265\n", "1 10.100.0.250 2124\n", "2 68.183.65.110 1950\n", "3 94.247.128.145 1149\n", "4 91.200.84.145 159\n", "5 10.100.0.240 156\n", "6 78.40.108.108 111\n", "7 195.133.146.14 82" ] }, "execution_count": 149, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT user_ip, COUNT(*) AS attempts\n", "FROM login_logs_clean\n", "WHERE user_ip IS NOT NULL AND user_ip <> ''\n", "GROUP BY user_ip\n", "HAVING COUNT(*) >= 50\n", "ORDER BY attempts DESC;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 152, "id": "e35b39ed-3d92-4f84-8143-35f81973eae8", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_idip_countattempts
004e8d5d22a190ca050ded70b9a85853f55b68e7141b7ac...3335
10fc534d1238e29b96411b076ddf6160884b91218589d89...2021
27f05106197a97841686c736504954540a0eb74133c23a2...1722
3d575ac9720337035fa1c26b95b8c5c6179b668d49e9f65...1630
48fba99bdc59a54e1ecf5b16d16649e83e9711e841c7add...1427
............
1259a8f2158d655ff5484f7bb0e85d1699e40525ca5cb0fcbf...55
1260a9234e35b7626dd0ce6b5435ea9eadcbf1eb554481f508...55
1261a96b8c05c14a7703323968c564689b35bff5e3db078f45...55
1262a9919a40d9d32411a36c30d6791ed20f17ccbbca1698c7...55
1263a9c3cbf59b44200f03c22bb06c66886727e4aa5af0040b...55
\n", "

1264 rows × 3 columns

\n", "
" ], "text/plain": [ " user_id ip_count attempts\n", "0 04e8d5d22a190ca050ded70b9a85853f55b68e7141b7ac... 33 35\n", "1 0fc534d1238e29b96411b076ddf6160884b91218589d89... 20 21\n", "2 7f05106197a97841686c736504954540a0eb74133c23a2... 17 22\n", "3 d575ac9720337035fa1c26b95b8c5c6179b668d49e9f65... 16 30\n", "4 8fba99bdc59a54e1ecf5b16d16649e83e9711e841c7add... 14 27\n", "... ... ... ...\n", "1259 a8f2158d655ff5484f7bb0e85d1699e40525ca5cb0fcbf... 5 5\n", "1260 a9234e35b7626dd0ce6b5435ea9eadcbf1eb554481f508... 5 5\n", "1261 a96b8c05c14a7703323968c564689b35bff5e3db078f45... 5 5\n", "1262 a9919a40d9d32411a36c30d6791ed20f17ccbbca1698c7... 5 5\n", "1263 a9c3cbf59b44200f03c22bb06c66886727e4aa5af0040b... 5 5\n", "\n", "[1264 rows x 3 columns]" ] }, "execution_count": 152, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT user_id,\n", " COUNT(DISTINCT user_ip) AS ip_count,\n", " COUNT(*) AS attempts\n", "FROM login_logs_clean\n", "WHERE user_id IS NOT NULL AND user_id <> ''\n", " AND user_ip IS NOT NULL AND user_ip <> ''\n", "GROUP BY user_id\n", "HAVING COUNT(DISTINCT user_ip) >= 5\n", "ORDER BY ip_count DESC, attempts DESC;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 154, "id": "7208ef7e-4e6e-4329-9d23-9d842e800dce", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
attemptssuccessfailedsuccess_rate_pct
03685434735211994.25
\n", "
" ], "text/plain": [ " attempts success failed success_rate_pct\n", "0 36854 34735 2119 94.25" ] }, "execution_count": 154, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT\n", " COUNT(*) AS attempts,\n", " SUM(CASE WHEN login_status = 1 THEN 1 ELSE 0 END) AS success,\n", " SUM(CASE WHEN login_status <> 1 THEN 1 ELSE 0 END) AS failed,\n", " ROUND(100.0 * SUM(CASE WHEN login_status = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct\n", "FROM login_logs_clean;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 156, "id": "062736ff-8d17-4240-9c55-dcfdfd9bb791", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dow_numweekdayattempts
00.0Sunday3342
11.0Monday6329
22.0Tuesday5928
33.0Wednesday5790
44.0Thursday5826
55.0Friday5638
66.0Saturday4001
\n", "
" ], "text/plain": [ " dow_num weekday attempts\n", "0 0.0 Sunday 3342\n", "1 1.0 Monday 6329\n", "2 2.0 Tuesday 5928\n", "3 3.0 Wednesday 5790\n", "4 4.0 Thursday 5826\n", "5 5.0 Friday 5638\n", "6 6.0 Saturday 4001" ] }, "execution_count": 156, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT\n", " EXTRACT(DOW FROM log_date) AS dow_num,\n", " TO_CHAR(log_date, 'Day') AS weekday,\n", " COUNT(*) AS attempts\n", "FROM login_logs_clean\n", "GROUP BY dow_num, weekday\n", "ORDER BY dow_num;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 158, "id": "1554a73f-e734-4455-97dd-7d80eb7beb95", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dayattemptssuccess_rate_pct
02016-12-166100.00
12016-12-17580.00
22016-12-186100.00
32016-12-191392.31
42016-12-201291.67
............
24792024-11-212487.50
24802024-11-222996.55
24812024-11-2322100.00
24822024-11-2414100.00
24832024-11-2522100.00
\n", "

2484 rows × 3 columns

\n", "
" ], "text/plain": [ " day attempts success_rate_pct\n", "0 2016-12-16 6 100.00\n", "1 2016-12-17 5 80.00\n", "2 2016-12-18 6 100.00\n", "3 2016-12-19 13 92.31\n", "4 2016-12-20 12 91.67\n", "... ... ... ...\n", "2479 2024-11-21 24 87.50\n", "2480 2024-11-22 29 96.55\n", "2481 2024-11-23 22 100.00\n", "2482 2024-11-24 14 100.00\n", "2483 2024-11-25 22 100.00\n", "\n", "[2484 rows x 3 columns]" ] }, "execution_count": 158, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT\n", " DATE(log_date) AS day,\n", " COUNT(*) AS attempts,\n", " ROUND(100.0 * SUM(CASE WHEN login_status = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate_pct\n", "FROM login_logs_clean\n", "GROUP BY day\n", "ORDER BY day;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 160, "id": "e96933d5-c354-4d5a-a572-6b8fb3b0db73", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
hour_bucketattempts
02024-09-09136
12023-12-15118
22023-09-11116
32024-01-29108
42024-08-12105
52024-01-17103
62023-09-1599
72024-05-2091
82019-12-2491
92024-05-2189
102024-05-2289
112024-09-1088
122022-09-1387
132023-12-2986
142023-09-1286
152024-09-1285
162023-12-2884
172024-05-1183
182024-09-1182
192024-09-0381
\n", "
" ], "text/plain": [ " hour_bucket attempts\n", "0 2024-09-09 136\n", "1 2023-12-15 118\n", "2 2023-09-11 116\n", "3 2024-01-29 108\n", "4 2024-08-12 105\n", "5 2024-01-17 103\n", "6 2023-09-15 99\n", "7 2024-05-20 91\n", "8 2019-12-24 91\n", "9 2024-05-21 89\n", "10 2024-05-22 89\n", "11 2024-09-10 88\n", "12 2022-09-13 87\n", "13 2023-12-29 86\n", "14 2023-09-12 86\n", "15 2024-09-12 85\n", "16 2023-12-28 84\n", "17 2024-05-11 83\n", "18 2024-09-11 82\n", "19 2024-09-03 81" ] }, "execution_count": 160, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT\n", " DATE_TRUNC('hour', log_date) AS hour_bucket,\n", " COUNT(*) AS attempts\n", "FROM login_logs_clean\n", "GROUP BY hour_bucket\n", "ORDER BY attempts DESC\n", "LIMIT 20;\n", "\"\"\", engine)" ] }, { "cell_type": "code", "execution_count": 162, "id": "1963ec64-947a-4c34-8e1d-fbacb468e1ee", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
user_ipattemptsfailedfail_rate_pct
010.0.0.225520.00
110.100.0.240156117.05
210.100.1.452653656.93
3195.133.146.148256.10
485.117.96.1292414.17
510.100.0.2502124552.59
678.40.108.10811110.90
794.247.128.145114960.52
868.183.65.110195050.26
991.200.84.14515900.00
1052.23.218.1183700.00
113.90.250.233600.00
123.91.91.753500.00
1352.55.232.2183500.00
143.81.201.1273300.00
\n", "
" ], "text/plain": [ " user_ip attempts failed fail_rate_pct\n", "0 10.0.0.2 25 5 20.00\n", "1 10.100.0.240 156 11 7.05\n", "2 10.100.1.4 5265 365 6.93\n", "3 195.133.146.14 82 5 6.10\n", "4 85.117.96.129 24 1 4.17\n", "5 10.100.0.250 2124 55 2.59\n", "6 78.40.108.108 111 1 0.90\n", "7 94.247.128.145 1149 6 0.52\n", "8 68.183.65.110 1950 5 0.26\n", "9 91.200.84.145 159 0 0.00\n", "10 52.23.218.118 37 0 0.00\n", "11 3.90.250.23 36 0 0.00\n", "12 3.91.91.75 35 0 0.00\n", "13 52.55.232.218 35 0 0.00\n", "14 3.81.201.127 33 0 0.00" ] }, "execution_count": 162, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT\n", " user_ip,\n", " COUNT(*) AS attempts,\n", " SUM(CASE WHEN login_status <> 1 THEN 1 ELSE 0 END) AS failed,\n", " ROUND(100.0 * SUM(CASE WHEN login_status <> 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS fail_rate_pct\n", "FROM login_logs_clean\n", "WHERE user_ip IS NOT NULL AND user_ip <> ''\n", "GROUP BY user_ip\n", "HAVING COUNT(*) >= 20\n", "ORDER BY fail_rate_pct DESC, attempts DESC\n", "LIMIT 15;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": 164, "id": "bd1b2573-915b-44aa-9f04-f53bc6ba3b40", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
night_attemptsnight_share_pct
036854100.0
\n", "
" ], "text/plain": [ " night_attempts night_share_pct\n", "0 36854 100.0" ] }, "execution_count": 164, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql(\"\"\"\n", "SELECT\n", " COUNT(*) AS night_attempts,\n", " ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM login_logs_clean), 2) AS night_share_pct\n", "FROM login_logs_clean\n", "WHERE EXTRACT(HOUR FROM log_date) BETWEEN 0 AND 5;\n", "\"\"\", engine)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "054d721d-06f0-4289-8a3e-1abae4e6665f", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3.11 (NER)", "language": "python", "name": "ner_env" }, "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.14" } }, "nbformat": 4, "nbformat_minor": 5 }