Dashboard-Bericht - Verdächtige Anmeldungen

Dies ist eine SQL-Version des Dashboard-Berichts für verdächtige Anmeldungen.

Dieser Dashboard-Bericht liefert Details zu neuen Anmeldungen von Benutzern, die sich verdächtig von früheren Anmeldungen unterscheiden, über einen bestimmten Zeitraum. Durch die Identifizierung ungewöhnlicher Anmeldeversuche können Administratoren proaktive Maßnahmen ergreifen, um Benutzerkonten und das Forum vor potenziellen Sicherheitsbedrohungen zu schützen.

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

Erklärung der SQL-Abfrage

Die SQL-Abfrage ruft eine Liste von Anmeldeversuchen ab, die innerhalb eines bestimmten Zeitraums als verdächtig gekennzeichnet wurden. Hier ist eine Aufschlüsselung, wie die Abfrage funktioniert:

• Parameterdefinition: Die Abfrage beginnt mit der Definition von zwei Parametern, :start_date und :end_date, um den Berichtszeitraum festzulegen. Beide Datumsparameter akzeptieren das Format JJJJ-MM-TT.
• Datenauswahl: Die Abfrage wählt bestimmte Felder aus der Tabelle user_auth_token_logs, die Details zu Benutzerauthentifizierungsaktivitäten protokolliert, und der Tabelle users, die Informationen über die Benutzer selbst enthält. Die ausgewählten Felder umfassen:
  • u.id (user_id): Die eindeutige Kennung des Benutzers.
  • u.username: Der Benutzername des Benutzers.
  • t.client_ip: Die IP-Adresse, von der der Anmeldeversuch unternommen wurde.
  • t.user_agent: Der User-Agent-String des Browsers oder Geräts, das für den Anmeldeversuch verwendet wurde.
  • t.created_at (login_time): Der Zeitstempel, zu dem der Anmeldeversuch stattgefunden hat.
• Join-Operation: Die Abfrage verknüpft die Tabelle user_auth_token_logs (t) mit der Tabelle users (u) über ihr gemeinsames Feld user_id, um jeden Anmeldeversuch mit den entsprechenden Benutzerinformationen zu korrelieren.
• Filterung: Sie filtert die Datensätze, um nur die Anmeldeversuche einzuschließen, die im Feld action der Tabelle user_auth_token_logs als suspicious markiert sind. Zusätzlich filtert sie die Datensätze, um nur diejenigen einzuschließen, die innerhalb des angegebenen Zeitraums (von :start_date bis :end_date) aufgetreten sind.
• Sortierung: Schließlich sortiert die Abfrage die Ergebnisse absteigend nach dem Zeitstempel created_at, um sicherzustellen, dass die neuesten verdächtigen Anmeldeversuche zuerst aufgelistet werden.

Beispielergebnisse