Query Dataverse audit logs from Azure Function

Question

I'm very new to this so forgive me

We have a requirement Every X days, query changes made in tables/records in dataverse and send email with these changes

From what I've worked out, this could be done with Power Automate, so every 7 days run a HTTP trigger that triggers a C# function which queries the audit logs and sends an email with a table of things that have changed.

I have audit logging working in my test environment and it shows changes made to a table, it's just the rest I'm unsure about.

The main bit I'm stuck with is how do I query the dataverse audit logs from a C# Azure Function app?

score 0 · Accepted Answer · answered Jun 24 '21 at 22:12

Using an Azure Function

You could perform a HTTP request to the Dataverse web API using the audits entity set name. The request would look something like:

GET /api/data/v9.2/audits HTTP/1.1
Host: {organizationUniqueName}.{region}.dynamics.com
Authorization: Bearer eyJ0eXAi...

Note, you need to provide a bearer authentication token in the request (See Use OAuth authentication with Microsoft Dataverse for details on how to do that. See Query data using the Web API for a comprehensive guide on querying the Dataverse web API.

Alternatively, you could use either one of the below NuGet packages to query Dataverse using C#.

Microsoft.CrmSdk.CoreAssemblies (Only works with Azure Function runtime 1.x)
Microsoft.PowerPlatform.Dataverse.Client (Public preview. Works with runtime 2.x+)

Regardles of the package you choose to use, the C# code will look more or less the same with the exception of ServiceClient being applicable to the second NuGet package, and CrmServiceClient applying to the first package.

var client = new CrmServiceClient(connectionString);

var auditLogsQuery =
    @"<fetch top='50' >
        <entity name='audit' >
            <filter>
            <condition attribute='operation' operator='eq' value='1' />
            <condition attribute='createdon' operator='last-x-hours' value='1' />
            </filter>
        </entity>
    </fetch>";

EntityCollection auditLogs = client.RetrieveMultiple(new FetchExpression(auditLogsQuery));

Using Power Automate

Seeing as you have mentioned the use of Power Automate, I suggest you consider using it to meet your requirements. Querying data in Dataverse is native to Power Automate through the built-in Dataverse connector.

Lets say you have the below Fetch XML query to pull back all audit logs where the operation was Create (1) and the operations were performed in the last hour.

Tip: You can use the FetchXML Builder plugin in the XrmToolBox to author these queries. Querying the audit logs in the advanced find is not supported.

<fetch top="50" >
  <entity name="audit" >
    <filter>
      <condition attribute="operation" operator="eq" value="1" />
      <condition attribute="createdon" operator="last-x-hours" value="1" />
    </filter>
  </entity>
</fetch>

Using the Dataverse List rows action, you can specify you want to query the Audits table and provide the above Fetch XML query in the Fetch Xml Query property of the action. See below for an example:

The results of this query are now available throughout the Power Automate cloud flow. For example, here I am using the Compose action to write out the Operation, Record Type, Record ID, and Changed Field for the returned audit logs.

Query Dataverse audit logs from Azure Function

1 Answers1

Using an Azure Function

Using Power Automate