これは、管理者ログインのダッシュボードレポートのSQLバージョンです。
このレポートは、指定された期間内の管理者ログインアクティビティの概要を提供し、管理者がいつ、どのIPアドレスからDiscourseにログインしたかを追跡するのに役立ちます。
--[params]
--date :start_date
--date :end_date
SELECT
u.id user_id,
u.username username,
t1.client_ip as location,
t1.created_at as login_at
FROM (
SELECT DISTINCT ON (t.client_ip, t.user_id) t.client_ip, t.user_id, t.created_at
FROM user_auth_token_logs t
INNER JOIN users u ON u.id = t.user_id
WHERE u.admin = TRUE
AND t.created_at >= :start_date
AND t.created_at <= :end_date
ORDER BY t.client_ip, t.user_id, t.created_at DESC
) t1
JOIN users u ON u.id = t1.user_id
ORDER BY login_at DESC
SQLクエリの説明
SQLクエリは、管理者のユニークなログインイベントのリストを取得するように設計されています。次の基準に基づいてデータをフィルタリングおよび表示します。
- パラメータ :: クエリは、ログインデータが必要な期間を指定するために、
:start_dateと:end_dateの2つのパラメータを受け入れます。両方の日付パラメータは、YYYY-MM-DDの日付形式を受け入れます。 - ユニークログイン:
SELECT DISTINCT ONを使用してサブクエリ（t1）を使用し、client_ipとuser_idの各組み合わせが一意であることを保証します。これにより、日付範囲内で各管理者ごとの各IPアドレスからの最新のログインイベントが効果的に取得されます。 - 管理者フィルター: クエリは、
adminフラグがTRUEに設定されているユーザーを具体的に検索し、管理者アカウントのみに焦点を当てます。 - ユーザーとの結合: サブクエリは
usersテーブルに結合され、対応するユーザーIDのユーザー名を取得します。 - 順序付け: 結果は
login_atタイムスタンプで降順に並べられ、最も最近の管理者ログインイベントが最初に表示されます。
出力列は次のとおりです。
user_id: ログインした管理者のユニークID。username: ログインした管理者のユーザー名。location: 管理者がログインしたIPアドレス。login_at: 管理者がログインしたタイムスタンプ。
このレポートは、セキュリティおよび監査目的で価値があり、すべての管理者ログインが監視および記録されることを保証します。
結果例
| user | username | location | login_at |
|---|---|---|---|
| 1 | admin_user_1 | 123.45.67.89 | 2023-12-13T23:59:55.733Z |
| 2 | admin_user_2 | 123.45.67.89 | 2023-12-14T23:59:45.685Z |
| 3 | admin_user_3 | 123.45.67.89 | 2023-12-15T23:59:43.033Z |
| … | … | … | … |