Dashboard-Bericht - Admin-Anmeldungen

[SaraDev (Sara Devlaeminck)]

Dies ist eine SQL-Version des Dashboard-Berichts für Admin-Logins.

Dieser Bericht bietet einen Überblick über die Anmeldeaktivitäten von Administratoren innerhalb eines bestimmten Datumsbereichs und hilft dabei, zu verfolgen, wann und von welchen IP-Adressen sich Administratoren bei Discourse angemeldet haben.

--[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

Erklärung der SQL-Abfrage

Die SQL-Abfrage ist so konzipiert, dass eine Liste eindeutiger Anmeldeereignisse für Administratoren abgerufen wird. Sie filtert und zeigt die Daten basierend auf den folgenden Kriterien an:

• Parameter: Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, um den Zeitraum anzugeben, für den die Anmeldedaten benötigt werden. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.
• Eindeutige Anmeldungen: Sie verwendet eine Unterabfrage (t1) mit SELECT DISTINCT ON, um sicherzustellen, dass jede Kombination aus client_ip und user_id eindeutig ist, was uns effektiv das aktuellste Anmeldeereignis für jeden Administrator an jeder IP-Adresse innerhalb des Datumsbereichs liefert.
• Admin-Filter: Die Abfrage sucht speziell nach Benutzern mit dem gesetzten admin-Flag auf TRUE und konzentriert sich ausschließlich auf Administratorkonten.
• Join mit Benutzern: Die Unterabfrage wird wieder mit der users-Tabelle verknüpft, um die entsprechenden Benutzernamen für die Benutzer-IDs abzurufen.
• Sortierung: Die Ergebnisse werden nach dem login_at-Zeitstempel in absteigender Reihenfolge sortiert, wobei die aktuellsten Admin-Anmeldeereignisse zuerst angezeigt werden.

Die Ausgabespalten sind wie folgt:

• user_id: Die eindeutige ID des angemeldeten Administrators.
• username: Der Benutzername des angemeldeten Administrators.
• location: Die IP-Adresse, von der sich der Administrator angemeldet hat.
• login_at: Der Zeitstempel, wann sich der Administrator angemeldet hat.

Dieser Bericht ist wertvoll für Sicherheits- und Prüfungszwecke und stellt sicher, dass alle Admin-Anmeldungen überwacht und aufgezeichnet werden.

Beispielergebnisse

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
…	…	…	…