Informe del panel - Inicios de sesión sospechosos

[SaraDev (Sara Devlaeminck)]

Esta es una versión SQL del Informe del Panel de Control de Inicios de Sesión Sospechosos.

Este informe del panel de control proporciona detalles de nuevos inicios de sesión de usuarios que difieren sospechosamente de inicios de sesión anteriores, durante un rango de fechas especificado. Al identificar intentos de inicio de sesión inusuales, los administradores pueden tomar medidas proactivas para proteger las cuentas de usuario y el foro contra posibles amenazas de seguridad.

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

Explicación de la consulta SQL

La consulta SQL opera recuperando una lista de intentos de inicio de sesión que han sido marcados como sospechosos dentro de un rango de fechas especificado. Aquí hay un desglose de cómo funciona la consulta:

• Definición de parámetros: La consulta comienza definiendo dos parámetros, :start_date y :end_date, para especificar el período de tiempo para el informe. Ambos parámetros de fecha aceptan el formato YYYY-MM-DD.
• Selección de datos: La consulta selecciona campos específicos de la tabla user_auth_token_logs, que registra detalles sobre las actividades de autenticación del usuario, y la tabla users, que contiene información sobre los propios usuarios. Los campos seleccionados incluyen:
  • u.id (user_id): El identificador único del usuario.
  • u.username: El nombre de usuario del usuario.
  • t.client_ip: La dirección IP desde la cual se realizó el intento de inicio de sesión.
  • t.user_agent: La cadena del agente de usuario del navegador o dispositivo utilizado para el intento de inicio de sesión.
  • t.created_at (login_time): La marca de tiempo cuando ocurrió el intento de inicio de sesión.
• Operación de unión: La consulta une la tabla user_auth_token_logs (t) con la tabla users (u) en su campo común user_id para correlacionar cada intento de inicio de sesión con la información del usuario correspondiente.
• Filtrado: Filtra los registros para incluir solo aquellos intentos de inicio de sesión que están marcados como suspicious en el campo action de la tabla user_auth_token_logs. Además, filtra los registros para incluir solo aquellos que ocurrieron dentro del rango de fechas especificado (:start_date a :end_date).
• Ordenación: Finalmente, la consulta ordena los resultados en orden descendente por la marca de tiempo created_at, asegurando que los intentos de inicio de sesión sospechosos más recientes se listen primero.

Resultados de ejemplo

usuario	nombre de usuario	dirección IP	agente de usuario	hora de inicio de sesión
user_id_example_1	username_example	2001:0db8:85a3:0000:0000:8a2e:0370:7334	Mozilla/5.0 (iPhone; CPU iPhone OS 17_2_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.2 Mobile/15E148 Safari/604.1	2024-01-09T08:18:57.535Z
…	…	…	…	…