Many organisations, particularly non-profits, have reporting requirements for ROI on the internal systems they implement. You spent 100K+ to build CRM. But do users use the system? Surely we can run a simple report to view this information! Yes, and No.
It sounds like a simple requirement, but it’s actually not as simple as it should be. It never is 🙂
There are multiple tools available to retrieve the data. It will likely be a combination of records fetched from different data sources.
Let me give you an example.
If you have the required level of access (you must be assigned the Audit Logs or View-Only Audit Logs roles in the Microsoft Purview portal to search the audit log), you can track Power Apps and Dynamics 365 activities in the Microsoft Purview compliance portal. Log in to Microsoft Purview Portal. Select Search -> Audit log search.
What if you only have access to your environments as a System Administrator?
We can use Dataverse audit to build the report. It can be done at the environmental level, not at the application level, but it should be sufficient for reporting on the system’s usage and adoption.
First, make sure the correct Auditing settings are enabled.
It counts a user login as one entry every time the user logs in multiple times within 4 hours. One count of login per user per 4 hours.
Now, from the Power Platform Environment Settings -> Auditing -> Audit Summary View.
We will filter by Event = User Access via Web.
We will export the result view to Excel.
With the Excel file downloaded, you can filter out service users, group records by user, and count to get an idea of how often each user accesses the system.
Let’s open the file we downloaded. Now we will use Power Query to create the report.
In Excel -> Data tab. From the top ribbon Get Data.
Let’s select the table or cell range for the data we exported.
In the editor, let’s only keep records that don’t belong to system accounts, the ones that represent staff members. You can also exclude any other user records that don’t make sense to include in the report, as shown in the screenshot below, where I added ‘And‘.
Select a Record (User) column then Group By from the top ribbon.
You are going to get something like this. Which will be your user login count.
As audit logs are stored for the period of time, you will always have a limited dataset. With a regular exports you will get the reports you need.