Reporting on user's access from DNN database

Overview

A customer may want to know where to look for information to check specific users' activity using the database information to find which users accessed which portal pages on which dates. 

Step by Step Guide

1. Go to Control Bar > Host > Advanced Settings > SQL, connected as a SuperUser account

2. Edit the following query, replacing 'Administrators' with the actual Role name from your site:

SELECT distinct Users.DisplayName, Users.Email, Users.LastIPAddress, aspnet_Membership.LastLoginDate, 
aspnet_Users.LastActivityDate, PortalLocalization.PortalName, Tabs.TabName
FROM (((((((Users INNER JOIN aspnet_Users ON Users.Username = aspnet_Users.Username)
  INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId)
  INNER JOIN Analytics_Fact_Users ON Analytics_Fact_Users.UserId = Users.UserId)
 INNER JOIN PortalLocalization ON Analytics_Fact_Users.PortalID = PortalLocalization.PortalID)
INNER JOIN Tabs ON Tabs.TabID = Analytics_Fact_Users.PageId)
  INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID)
  INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID)
WHERE Roles.RoleName = 'Administrators'

3. Run the query against your database, to show:

  • User's Display Name (First name, Last name)
  • Their email address
  • The last IP Address they logged in from
  • The last Login Date from each user
  • The last Activity Date on the site
  • Which Portal Name they visited
  • The Page(s) they visited

Content Author: Radu Vaduva

Comments

0 comments

Please sign in to leave a comment.