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.
- Auditing should be enabled
- Audit user access should be enabled
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.
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.