Overview

At times, you may need to retrieve the information on who is logging into your EVOQ site. While some of this information can be found in the Admin Logs, that tool lacks a bulk export function like the SQL Console does, and cannot be searched with or filtered like SQL queries can. Finding specific users, exporting large amounts of data, or limiting your search to a particular date range is best accomplished with SQL.

This article provides the steps to run two reports that contain summary and detailed views of user logins. The first query outlined in Step 3 doesn't rely on Admin Logs, so it will still return results even after admin logs have been cleared.

Prerequisites

• You must have either:
  • Access to a Super User account.
  • SQL access to the DNN Evoq database (to run the queries directly in SQL Server Management Studio)
• Basic SQL knowledge is helpful but not required.

Solution

To run the queries in the Evoq UI's SQL Console:

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

3. 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
     
     

   SELECT 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

   
   These results should look like this (note the columns):
   
   

   
   
   

4. For a more historical report including login failures and successes, run the following query. This may return a large number of results. Limit the date range to reduce the size of the results. If the Admin Logs have been cleared previously (see Testing below), the query in step 2 may include logins that are not included in this more detailed query.
   

   SELECT LogTypeKey AS "Event"
   ,LogUserName AS UserName
   ,LogPortalName AS Portal
   ,LogCreateDate AS "Date"
   FROM eventlog
   WHERE 1=1
   AND logconfigid IN (97, 98, 99, 100, 101) --REMOVE THIS LINE IF YOU WANT ALL LOGS
   AND eventlog.LogCreateDate >= '2020-01-01' --ENTER YOUR START DATE HERE
   AND eventlog.LogCreateDate < '2020-12-31' --ENTER YOUR END DATE HERE

NOTE: If either query does not complete in the Evoq UI, there may be a large number of results. You can run these queries in SQL Server Management Studio to get around UI issues.

Testing

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

1. Manage > Admin Logs
2. Compare entries for similar dates to make sure they match. If the Admin Logs have been cleared previously, the summary report in step 3 may include logins that are not present in the Admin Logs.
   

Back to top