これは、不審なログインのダッシュボードレポートのSQLバージョンです。
このダッシュボードレポートは、指定された期間内に、以前のログインとは疑わしく異なるユーザーからの新しいログインの詳細を提供します。管理者は、異常なログイン試行を特定することにより、ユーザーアカウントとフォーラムを潜在的なセキュリティ脅威から保護するためにプロアクティブな措置を講じることができます。
-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08
SELECT
u.id user_id,
u.username,
t.client_ip,
t.user_agent,
t.created_at login_time
FROM user_auth_token_logs t
JOIN users u ON u.id = t.user_id
WHERE t.action = 'suspicious'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
ORDER BY t.created_at DESC
SQLクエリの説明
このSQLクエリは、指定された期間内に不審とフラグが付けられたログイン試行のリストを取得することによって機能します。クエリの機能の内訳は次のとおりです。
- パラメータ定義: クエリは、レポートの期間を指定するために、2つのパラメータ、
:start_dateと:end_dateを定義することから始まります。両方の日付パラメータは、YYYY-MM-DD形式を受け入れます。 - データ選択: クエリは、ユーザー認証アクティビティに関する詳細を記録する
user_auth_token_logsテーブルと、ユーザー自体に関する情報を含むusersテーブルから特定のフィールドを選択します。選択されたフィールドは次のとおりです。u.id(user_id): ユーザーの一意の識別子。u.username: ユーザーのユーザー名。t.client_ip: ログイン試行が行われたIPアドレス。t.user_agent: ログイン試行に使用されたブラウザまたはデバイスのユーザーエージェント文字列。t.created_at(login_time): ログイン試行が発生したタイムスタンプ。
- 結合操作: クエリは、
user_auth_token_logsテーブル（t）とusersテーブル（u）を共通のuser_idフィールドで結合して、各ログイン試行を対応するユーザー情報と相関させます。 - フィルタリング:
user_auth_token_logsテーブルのactionフィールドでsuspiciousとマークされているログイン試行のみを含めるようにレコードをフィルタリングします。さらに、指定された期間（:start_dateから:end_date）内に発生したレコードのみを含めるようにレコードをフィルタリングします。 - 並べ替え: 最後に、クエリは
created_atタイムスタンプで結果を降順に並べ替えることにより、最も最近の不審なログイン試行が最初にリストされるようにします。
結果例
| user | username | client_ip | user_agent | login_time |
|---|---|---|---|---|
| user_id_example_1 | username_example | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | Mozilla/5.0 (iPhone; CPU iPhone OS 17_2_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.2 Mobile/15E148 Safari/604.1 | 2024-01-09T08:18:57.535Z |
| … | … | … | … | … |