Reporte del Dashboard - Conexiones de Admin

[SaraDev (Sara Devlaeminck)]

Esta es una versión SQL del Informe del Panel para inicios de sesión de administradores.

Este informe proporciona una descripción general de la actividad de inicio de sesión de administradores dentro de un rango de fechas especificado y ayuda a rastrear cuándo y desde qué direcciones IP los administradores han iniciado sesión en 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

Explicación de la consulta SQL

La consulta SQL está diseñada para recuperar una lista de eventos de inicio de sesión únicos para administradores. Filtra y muestra los datos según los siguientes criterios:

• Parámetros: La consulta acepta dos parámetros, :start_date y :end_date, para especificar el período para el cual se requieren los datos de inicio de sesión. Ambos parámetros de fecha aceptan el formato de fecha YYYY-MM-DD.
• Inicios de sesión distintos: Utiliza una subconsulta (t1) con SELECT DISTINCT ON para garantizar que cada combinación de client_ip y user_id sea única, lo que efectivamente nos da el evento de inicio de sesión más reciente para cada administrador en cada dirección IP dentro del rango de fechas.
• Filtro de administrador: La consulta busca específicamente usuarios con el indicador admin establecido en TRUE, centrándose únicamente en las cuentas de administrador.
• Unión con usuarios: La subconsulta se une nuevamente a la tabla users para obtener los nombres de usuario correspondientes a los ID de usuario.
• Ordenación: Los resultados se ordenan por la marca de tiempo login_at en orden descendente, mostrando primero los eventos de inicio de sesión de administrador más recientes.

Las columnas de salida son las siguientes:

• user_id: El ID único del administrador que inició sesión.
• username: El nombre de usuario del administrador que inició sesión.
• location: La dirección IP desde la cual inició sesión el administrador.
• login_at: La marca de tiempo de cuándo inició sesión el administrador.

Este informe es valioso para fines de seguridad y auditoría, y garantiza que todos los inicios de sesión de administradores sean monitoreados y registrados.

Resultados de ejemplo

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