Отчет дашборда - Подозрительные входы

Создание сообщества Данные и отчетность
, ,
1

Это SQL-версия отчёта панели управления по подозрительным входам.

Этот отчёт панели управления содержит сведения о новых входах пользователей, которые подозрительно отличаются от предыдущих входов, за указанный диапазон дат. Выявляя необычные попытки входа, администраторы могут предпринять превентивные меры для защиты учётных записей пользователей и форума от потенциальных угроз безопасности.

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

Объяснение SQL-запроса

SQL-запрос работает путём извлечения списка попыток входа, помеченных как подозрительные, в указанном диапазоне дат. Вот подробное описание работы запроса:

  • Определение параметров: Запрос начинается с определения двух параметров :start_date и :end_date для указания периода времени отчёта. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
  • Выбор данных: Запрос выбирает конкретные поля из таблицы user_auth_token_logs, которая регистрирует сведения о действиях аутентификации пользователей, и из таблицы users, содержащей информацию о самих пользователях. Выбранные поля включают:
    • u.id (user_id): Уникальный идентификатор пользователя.
    • u.username: Имя пользователя.
    • t.client_ip: IP-адрес, с которого была предпринята попытка входа.
    • t.user_agent: Строка пользовательского агента браузера или устройства, использованного для попытки входа.
    • t.created_at (login_time): Временная метка момента попытки входа.
  • Операция соединения: Запрос соединяет таблицу user_auth_token_logs (t) с таблицей users (u) по общему полю user_id, чтобы сопоставить каждую попытку входа с соответствующей информацией о пользователе.
  • Фильтрация: Запрос фильтрует записи, включая только те попытки входа, которые помечены как suspicious в поле action таблицы user_auth_token_logs. Кроме того, он фильтрует записи, включая только те, которые произошли в указанном диапазоне дат (:start_date – :end_date).
  • Сортировка: Наконец, запрос сортирует результаты по убыванию временной метки created_at, обеспечивая, чтобы самые последние подозрительные попытки входа были перечислены первыми.

Пример результатов

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
… … … … …
4 лайка
Admin dashboard report reference guide