Dashboard Report - Suspicious Logins

data & reporting
, ,
1

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

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