Rapport Dashboard - Connexions suspectes

Ceci est une version SQL du rapport de tableau de bord pour les connexions suspectes.

Ce rapport de tableau de bord fournit des détails sur les nouvelles connexions d’utilisateurs qui diffèrent de manière suspecte des connexions précédentes, sur une période spécifiée. En identifiant les tentatives de connexion inhabituelles, les administrateurs peuvent prendre des mesures proactives pour sécuriser les comptes d’utilisateurs et le forum contre les menaces de sécurité potentielles.

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

Explication de la requête SQL

La requête SQL fonctionne en récupérant une liste de tentatives de connexion signalées comme suspectes dans une plage de dates spécifiée. Voici une ventilation du fonctionnement de la requête :

• Définition des paramètres : La requête commence par définir deux paramètres, :start_date et :end_date, pour spécifier la période du rapport. Les deux paramètres de date acceptent le format AAAA-MM-JJ.
• Sélection des données : La requête sélectionne des champs spécifiques de la table user_auth_token_logs, qui enregistre les détails des activités d’authentification des utilisateurs, et de la table users, qui contient des informations sur les utilisateurs eux-mêmes. Les champs sélectionnés comprennent :
  • u.id (user_id) : L’identifiant unique de l’utilisateur.
  • u.username : Le nom d’utilisateur de l’utilisateur.
  • t.client_ip : L’adresse IP à partir de laquelle la tentative de connexion a été effectuée.
  • t.user_agent : La chaîne d’agent utilisateur du navigateur ou de l’appareil utilisé pour la tentative de connexion.
  • t.created_at (login_time) : L’horodatage auquel la tentative de connexion s’est produite.
• Opération de jointure : La requête joint la table user_auth_token_logs (t) avec la table users (u) sur leur champ commun user_id pour corréler chaque tentative de connexion avec les informations utilisateur correspondantes.
• Filtrage : Elle filtre les enregistrements pour inclure uniquement les tentatives de connexion marquées comme suspicious dans le champ action de la table user_auth_token_logs. De plus, elle filtre les enregistrements pour inclure uniquement ceux qui se sont produits dans la plage de dates spécifiée (:start_date à :end\_date).
• Tri : Enfin, la requête trie les résultats par ordre décroissant en fonction de l’horodatage created_at, garantissant que les tentatives de connexion suspectes les plus récentes sont répertoriées en premier.

Exemple de résultats