Generating a User Access Report in DNN EVOQ

Overview


At times, you may need to retrieve the information on who is logging into your EVOQ site and what they are doing once logged in. This article provides the steps to run two reports that contain this data.

Important
Please note that the queries below can retrieve a lot of data depending on how many users and roles are active on your site. These queries may run for a long time, and it can temporarily affect the site's performance.


Process

  1. Log in to your EVOQ instance with your SuperUser/Admin account.
  2. From the Persona Bar, navigate to Settings > SQL Console.

    sqlconsole.jpg

    • Copy and paste the following query into the console and click Run Script to retrieve the following data:
      • User's display name (first and last name)
      • Email address
      • Last IP address they logged in from
      • Last login date
      • Which portal they visited
      • Page(s) that they visited

      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)


      The results should look like this:

      sqllong.jpg

       

    • If you are looking for a more concise report, copy and paste the following query into the console and then click Run Script to retrieve the following data:

      • User's display name (first and last name)
      • Email address
      • Last IP address they logged in from
      • Last login date

      SELECT distinct Users.DisplayName, Users.Email, Users.LastIPAddress, 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)
        INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID)
        INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID)


      These results should look like this:

      mceclip0.png

Back to top

Comments

0 comments

Please sign in to leave a comment.