Brightspace login history beyond 30 days: logins per user

Choose where to run it

• In Rowseta: open Reports > New report and paste the query into the SQL editor on the Query tab. Sync the data sets listed below first.
• In your own setup: load your Data Hub extracts into your own SQL tool, one table per data set, named without spaces (User Enrollments becomes UserEnrollments), after applying differentials to the fulls. Another SQL tool may need small changes to these queries.

A free Rowseta Desktop app is coming soon.

This query needs User Logins and Users. The reference links explain the columns and keys in each data set.

Run the login history query

Change the first day on line 2 and last day on line 3. Both are included. Login times are in UTC (Coordinated Universal Time), so an evening login in your time zone can fall on the next day.

WITH Settings AS (
    SELECT DATE '2025-01-01' AS FirstDay,
           DATE '2025-12-31' AS LastDay
), Attempts AS (
    SELECT UserLogins.*,
           CASE WHEN StatusType = 'Success' AND ImpersonatingUserId IS NULL THEN 1 ELSE 0 END AS OwnLogin
    FROM UserLogins
    CROSS JOIN Settings
    WHERE CAST(AttemptDate AS DATE) BETWEEN Settings.FirstDay AND Settings.LastDay
), Counts AS (
    SELECT UserId,
           COUNT(DISTINCT CASE WHEN OwnLogin = 1 THEN LoginAttemptId END) AS Logins,
           COUNT(DISTINCT CASE WHEN OwnLogin = 1 THEN CAST(AttemptDate AS DATE) END) AS LoginDays,
           MIN(CASE WHEN OwnLogin = 1 THEN AttemptDate END) AS FirstLogin,
           MAX(CASE WHEN OwnLogin = 1 THEN AttemptDate END) AS LastLogin,
           COUNT(DISTINCT CASE WHEN ImpersonatingUserId IS NULL
               AND (StatusType IS NULL OR StatusType NOT IN ('Success', 'User Impersonation'))
               THEN LoginAttemptId END) AS FailedAttempts
    FROM Attempts
    GROUP BY UserId
)
SELECT Users.UserId, Users.FirstName, Users.LastName,
       COALESCE(Counts.Logins, 0) AS Logins, COALESCE(Counts.LoginDays, 0) AS LoginDays,
       Counts.FirstLogin, Counts.LastLogin, COALESCE(Counts.FailedAttempts, 0) AS FailedAttempts
FROM Users
LEFT JOIN Counts ON Counts.UserId = Users.UserId
WHERE Users.UserId <> 0 AND Users.DateDeleted IS NULL
ORDER BY Users.UserId;

Only Success with no ImpersonatingUserId counts as a login. Impersonation means an administrator signed in as the person. Those sign-ins are excluded. User Impersonation never counts as a failed attempt, even without an administrator ID. Other statuses, including NULL and unknown values, count as failed or unknown attempts when no administrator ID is set. Examples are Invalid Password and Locked Out.

Each login attempt counts once, even if the same attempt appears in more than one loaded file. LoginDays counts the UTC days with a successful, non-impersonated login. FirstLogin and LastLogin show the earliest and latest of those logins within your date range. System user 0 and deleted users are excluded. Inactive accounts stay in the list.

Read the login history per user

Fictional sample data: all 8 rows.

A zero in Logins means the loaded data has no successful, non-impersonated login for that person within your date range, and null dates mean the same. UserId separates the same-name people. Login counts do not measure learning or effort.

Other data sets with login history

For D2L login history, choose a data set:

• User Logins records every login attempt, successful or not, from 1 January 2021.
• Session History records browser sessions with start and end times, from 1 January 2021. It excludes app use. Both data sets have a default system limit of 150 million rows of the most recent data. Large organizations may hold less history.
• System Access Log records each visit, including the Pulse mobile app, from September 2020.