Relatório do Painel - Logins Suspeitos

[SaraDev (Sara Devlaeminck)]

Esta é uma versão SQL do Relatório de Painel para Logins Suspeitos.

Este relatório de painel fornece detalhes de novos logins de usuários que diferem suspeitamente de logins anteriores, dentro de um intervalo de datas especificado. Ao identificar tentativas de login incomuns, os administradores podem tomar medidas proativas para proteger as contas de usuário e o fórum contra possíveis ameaças de segurança.

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

Explicação da Consulta SQL

A consulta SQL opera recuperando uma lista de tentativas de login que foram sinalizadas como suspeitas dentro de um intervalo de datas especificado. Aqui está um detalhamento de como a consulta funciona:

• Definição de Parâmetros: A consulta começa definindo dois parâmetros, :start_date e :end_date, para especificar o período de tempo para o relatório. Ambos os parâmetros de data aceitam o formato AAAA-MM-DD.
• Seleção de Dados: A consulta seleciona campos específicos da tabela user_auth_token_logs, que registra detalhes sobre atividades de autenticação do usuário, e da tabela users, que contém informações sobre os próprios usuários. Os campos selecionados incluem:
  • u.id (user_id): O identificador exclusivo do usuário.
  • u.username: O nome de usuário do usuário.
  • t.client_ip: O endereço IP de onde a tentativa de login foi feita.
  • t.user_agent: A string do agente do usuário do navegador ou dispositivo usado para a tentativa de login.
  • t.created_at (login_time): O timestamp de quando a tentativa de login ocorreu.
• Operação de Junção: A consulta une a tabela user_auth_token_logs (t) com a tabela users (u) em seu campo comum user_id para correlacionar cada tentativa de login com as informações correspondentes do usuário.
• Filtragem: Ela filtra os registros para incluir apenas as tentativas de login que são marcadas como suspicious no campo action da tabela user_auth_token_logs. Além disso, ela filtra os registros para incluir apenas aqueles que ocorreram dentro do intervalo de datas especificado (:start_date a :end_date).
• Ordenação: Finalmente, a consulta ordena os resultados em ordem decrescente pelo timestamp created_at, garantindo que as tentativas de login suspeitas mais recentes sejam listadas primeiro.

Exemplo de Resultados

user	username	client_ip	user_agent	login_time
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
…	…	…	…	…