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.
t1.client_ip as location,
t1.created_at as login_at
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
JOIN users u ON u.id = t1.user_id
ORDER BY login_at DESC
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,
:end_date, to specify the period for which the login data is required. Both date parameters accept the date format of
- Distinct Logins: It uses a subquery (
SELECT DISTINCT ONto ensure that each combination of
user_idis 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
adminflag set to
TRUE, focusing solely on administrator accounts.
- Join with Users: The subquery is joined back to the
userstable to fetch the corresponding usernames for the user IDs.
- Ordering: The results are ordered by the
login_attimestamp 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.