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.
- 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.
To run the queries in the Evoq UI's SQL Console:
- Log in to your EVOQ instance with your Super User/Admin account.
- From the Persona Bar, navigate to Settings > SQL Console.
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
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):
- 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"
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.
You should be able to test that this report is correct by going into:
- Manage > Admin Logs
- 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.