Dashboard Report - Admin Logins

data & reporting
, ,
1

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

This report provides an overview of admin login activity within a specified date range, and helps to track when and from which IP addresses admins have logged into Discourse.

--[params]
--date :start_date
--date :end_date

SELECT
  u.id user_id,
  u.username username,
  t1.client_ip as location,
  t1.created_at as login_at
FROM (
  SELECT DISTINCT ON (t.client_ip, t.user_id) t.client_ip, t.user_id, t.created_at
    FROM user_auth_token_logs t
    INNER JOIN users u ON u.id = t.user_id
    WHERE u.admin = TRUE
       AND t.created_at >= :start_date
       AND t.created_at <= :end_date
  ORDER BY t.client_ip, t.user_id, t.created_at DESC
  ) t1
  JOIN users u ON u.id = t1.user_id
  ORDER BY login_at DESC

SQL Query Explanation

The SQL query is designed to retrieve a list of unique login events for administrators. It filters and displays the data based on the following criteria:

  • Parameters :: The query accepts two parameters, :start_date and :end_date, to specify the period for which the login data is required. Both date parameters accept the date format of YYYY-MM-DD .
  • Distinct Logins: It uses a subquery (t1) with SELECT DISTINCT ON to ensure that each combination of client_ip and user_id is unique, effectively giving us the most recent login event for each admin at each IP address within the date range.
  • Admin Filter: The query specifically looks for users with the admin flag set to TRUE, focusing solely on administrator accounts.
  • Join with Users: The subquery is joined back to the users table to fetch the corresponding usernames for the user IDs.
  • Ordering: The results are ordered by the login_at timestamp in descending order, showing the most recent admin login events first.

The output columns are as follows:

  • user_id: The unique ID of the admin who logged in.
  • username: The username of the admin who logged in.
  • location: The IP address from which the admin logged in.
  • login_at: The timestamp of when the admin logged in.

This report is valuable for security and auditing purposes, ensuring that all admin logins are monitored and recorded.

Example Results

user username location login_at
1 admin_user_1 123.45.67.89 2023-12-13T23:59:55.733Z
2 admin_user_2 123.45.67.89 2023-12-14T23:59:45.685Z
3 admin_user_3 123.45.67.89 2023-12-15T23:59:43.033Z
… … … …
4 Likes