How to query active users who accessed Dynamics 365 CRM

Guowei_Xu

407

Like ( 0 )

Report

Introduction

Managing user activity and tracking interactions within D365 CRM is crucial for understanding user engagement and optimizing the system's performance.

This blog post will guide you on how to query and identify active users who have accessed Dynamics 365 CRM.

By following these steps, you will gain insights into user behavior and ensure the effective utilization of your CRM platform.

Prerequisites:

1, The Log Access feature was enabled. Regarding how to enable this feature, please refer to this Link. Enable Dynamics 365 CRM Access Auditing

Step 1: Accessing the Admin Center: PPAC

To begin, log in to the D365 CRM Admin Center using your administrator credentials. This will grant you access to the necessary tools and settings to query user activity.

Step 2: Navigating to Dataverse analytics and Active users

In the Admin Center, locate the Active Users tab. This tab gives insights into all the active users' views.

You can apply a filter to narrow down the displayed user views.

Active Users description

Step 3: Exporting User Data

Once you have filtered the active user list and ensured that only active users are displayed, you can export this data for further analysis.

Click the Download dropdown menu and select the desired report.

Limitations

1, Exports are limited to a maximum of 3000 records.

2, The exported file doesn't have the UPN column, only the user display name.

3, we can only query the last 30 days data.

4, no detailed information in the report.

Based on the mentioned limitations above, we can query detailed data exceeding 30 days using the Audit table.

1, Audit Table

Query all Access details, through the action = 64

2, Query user list who accessed Dynamics 365 CRM

select
    distinct(u.domainname)
from
    audit A
    join systemuser u ON A.objectid = u.systemuserid
where
    action = 64 --User Access via Web
    and
    A.createdon >= '2023/08/25 10:00:01'
    and
    A.createdon <= '2023/08/25 14:00:00'
order by A.createdon desc

By using the above SQL, you can query all users who have accessed the system. However, due to the large amount of data in the Audit table, it's advisable not to query data for a long period of time in a single query.

The End