DotNetNuke User Report Queries

Below you will find a few generic SQL queries for generating reports on DNN Users.

Last Activity:
SELECT     Users.Username, Users.FirstName, Users.LastName, aspnet_Users.LastActivityDate
FROM         aspnet_Users INNER JOIN
                      Users ON aspnet_Users.UserName = Users.Username
order by aspnet_Users.LastActivityDate DESC

Last Login:
SELECT   Users.Username, Users.FirstName, Users.LastName, aspnet_Membership.LastLoginDate
FROM         Users INNER JOIN
                      aspnet_Users ON Users.Username = aspnet_Users.UserName INNER JOIN
                      aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId
ORDER BY aspnet_Membership.LastLoginDate DESC

New Users:
SELECT   Users.Username, Users.FirstName, Users.LastName, aspnet_Membership.CreateDate
FROM         Users INNER JOIN
                      aspnet_Users ON Users.Username = aspnet_Users.UserName INNER JOIN
                      aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId
ORDER BY aspnet_Membership.CreateDate DESC

Unverified Users:
select USers.FirstName, Users.LastName, aspnet_Membership.Email, aspnet_Users.UserName from aspnet_Membership, aspnet_Users, Users where aspnet_Membership.IsApproved = 0 AND aspnet_Membership.UserID = aspnet_Users.UserId AND aspnet_Users.UserName = Users.UserName

Users in Role:
SELECT Roles.RoleName AS [Role Name], COUNT(UserRoles.RoleID) AS [Number in Role] FROM Roles INNER JOIN UserRoles ON UserRoles.RoleID = Roles.RoleID GROUP BY UserRoles.RoleID, Roles.RoleName ORDER BY RoleName