Skip to main content

In todays post, HSD’s Dynamics Technical Consultant – Ash Dason demonstrates how to create reports from user access audit data in Dynamics 365.

A common request from clients is to have the ability to generate a report with user session details and last logged in times. This is very important for organisations when they want to check adoptation and usage patterns of a system. This can be achieved through fetchXML reports using the Audit table.

Pre-Conditions

  1. Auditing should be enabled
  2. Audit user access should be enabled

Implementation

Use the below fetchXML to create a SSRS report and then upload the same into CRM.

<fetch mapping="logical" aggregate="true" version="1.0" >
    <entity name="systemuser" >
        <attribute name="fullname" alias="Name" groupby="true" />
        <attribute name="domainname" alias="Username" groupby="true" />
        <attribute name="isdisabled" alias="Disabled" groupby="true" />
        <link-entity name="audit" from="objectid" to="systemuserid" link-type="outer" >
            <attribute name="createdon" alias="LastLoginDate" aggregate="max" />
            <filter type="and" >
                <condition attribute="operation" operator="eq" value="4" />
            </filter>
        </link-entity>
    </entity>
</fetch>

Audit table is filtered using the “operation” attribute for User Access (value = 4). The other operation values possible in the Audit table are Create(1), Update(2) and Delete(3). The objectid is the ID of the user or entity record id that the operation was performed on.

The output of the fetchXML will be similar to the example below.

NameUsernameDisabledLastLoginDate
John Doexxx\john.doeYes1/04/2021
Darcy Shawxxx\darcy.shawNo20/01/2020

This report can be further extended by doing date difference calculations on the LastLoginDate to find out how many days it has been since a user has logged in.

You can also refer Microsoft documentation for further details. You can use the operation and the action attribute and develop audit reports to meet various requirements.


Be sure to keep up to date with all of HSD’s news and content here and on our LinkedIn.