Rapport du tableau de bord - Connexions administrateur

[SaraDev (Sara Devlaeminck)]

Ceci est une version SQL du rapport de tableau de bord pour les connexions d’administrateur.

Ce rapport fournit un aperçu de l’activité de connexion des administrateurs dans une plage de dates spécifiée et aide à suivre quand et à partir de quelles adresses IP les administrateurs se sont connectés à 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

Explication de la requête SQL

La requête SQL est conçue pour récupérer une liste d’événements de connexion uniques pour les administrateurs. Elle filtre et affiche les données selon les critères suivants :

• Paramètres :: La requête accepte deux paramètres, :start_date et :end_date, pour spécifier la période pour laquelle les données de connexion sont requises. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.
• Connexions distinctes : Elle utilise une sous-requête (t1) avec SELECT DISTINCT ON pour garantir que chaque combinaison de client_ip et user_id est unique, nous donnant ainsi l’événement de connexion le plus récent pour chaque administrateur à chaque adresse IP dans la plage de dates.
• Filtre administrateur : La requête recherche spécifiquement les utilisateurs dont le drapeau admin est défini sur TRUE, se concentrant uniquement sur les comptes d’administrateur.
• Jointure avec les utilisateurs : La sous-requête est jointe à la table users pour récupérer les noms d’utilisateur correspondants aux identifiants d’utilisateur.
• Tri : Les résultats sont triés par l’horodatage login_at dans l’ordre décroissant, affichant d’abord les événements de connexion d’administrateur les plus récents.

Les colonnes de sortie sont les suivantes :

• user_id : L’identifiant unique de l’administrateur qui s’est connecté.
• username : Le nom d’utilisateur de l’administrateur qui s’est connecté.
• location : L’adresse IP depuis laquelle l’administrateur s’est connecté.
• login_at : L’horodatage de la connexion de l’administrateur.

Ce rapport est précieux à des fins de sécurité et d’audit, garantissant que toutes les connexions d’administrateur sont surveillées et enregistrées.

Résultats d’exemple

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