Report number of users that have used each login method

Number of active users per login method

Counts how many users have used each login method in the last 28 days. The time period can be changed via a parameter

--[params]
-- integer :last_seen_days = 28

SELECT provider_name, count(*)
FROM user_associated_accounts uaa
JOIN users u on u.id = uaa.user_id
WHERE u.active AND NOT u.staged
AND u.last_seen_at IS NOT NULL
AND last_used > NOW() - INTERVAL ':last_seen_days days'
GROUP BY provider_name
ORDER BY count(*) DESC
Output

15 Likes

I just updated this query following a change to how we store GitHub login information.

It will now work for all core login methods, and any plugins which use the ‚ÄėManagedAuthenticator‚Äô system (including discourse-oauth2 and discourse-openid-connect)

I also added a last_seen_days parameter, so you can easily customize the query time period.

5 Likes