Dashboard Report - Suspicious Logins

This is an SQL version of the Dashboard Report for Suspicious Logins.

This dashboard report provides details of new logins from users that differ suspiciously from previous logins, over a specified date range. By identifying unusual login attempts, admins can take proactive measures to secure user accounts and the forum against potential security threats.

-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08

    u.id user_id, 
    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 Query Explanation

The SQL query operates by retrieving a list of login attempts that have been flagged as suspicious within a specified date range. Here’s a breakdown of how the query functions:

  • Parameter Definition: The query starts by defining two parameters, :start_date and :end_date, to specify the time period for the report. Both date parameters accept the format of YYYY-MM-DD.
  • Data Selection: The query selects specific fields from the user_auth_token_logs table, which logs details about user authentication activities, and the users table, which contains information about the users themselves. The fields selected include:
    • u.id (user_id): The unique identifier of the user.
    • u.username: The username of the user.
    • t.client_ip: The IP address from which the login attempt was made.
    • t.user_agent: The user agent string of the browser or device used for the login attempt.
    • t.created_at (login_time): The timestamp when the login attempt occurred.
  • Join Operation: The query joins the user_auth_token_logs table (t) with the users table (u) on their common user_id field to correlate each login attempt with the corresponding user information.
  • Filtering: It filters the records to include only those login attempts that are marked as suspicious in the action field of the user_auth_token_logs table. Additionally, it filters the records to include only those that occurred within the specified date range (:start_date to :end_date).
  • Ordering: Finally, the query orders the results in descending order by the created_at timestamp, ensuring that the most recent suspicious login attempts are listed first.

Example Results

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