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.

 

Prerequisites

 

Solution

  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 (Note the Columns):

      sqllong.jpg

      NOTE: This query will only work in pre Evoq 9 installations. 

  3. 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 (Note the Columns):

    mceclip0.png

If the query does not complete in the DNN UI, there may be a large number of users. Run these queries in SQL Server Management Studio.

 

Testing

You should be able to test that this report is correct by going into:

  1. Manage > Users > Search for a specific User
  2. Click User Activity
  3. Compare the activity with the report you generated above.

    2020-05-18_1053.png

Back to top

Comments

0 comments

Please sign in to leave a comment.