Logic Apps Example: Identify Missing Records in Dynamics 365 (CRM)

Use an Azure Logic App to identify Dynamics 365 users who didn't perform a required action in the past day. Send email with user names.

This example of using Azure Logic Apps with Dynamics 365/CRM (D365) demonstrates one way to meet the following requirement:

Each day, identify D365 users who have not created at least one task record in the previous day. Send an email with the list of those users.

Someone asked about how to meet this requirement on the D365 forum. Dynamics 365 itself does not provide a no-code way of meeting this requirement, but with Azure Logic Apps it's possible to meet this requirement by connecting built-in Logic Apps functionality along with formulas using the Workflow Definition Language. 

Note: According to Microsoft's pricing for Logic Apps, it will cost less than a penny a day to run this Logic App. However, it's important to periodically monitor Microsoft's billing for your Azure services to be sure you are keeping your costs to a minimum.

The next section provides the Logic App workflow diagram, with most details expanded. The section below the diagram provides further details on how to build this workflow.

Logic App workflow diagram

Logic App Details

The table below lists each of the Logic App components and a description and details for each component.

Component Name	Notes
Recurrence	Run the Logic App once per day. Use the advanced options to run the app at a particular time of day based on your requirements.
Initialize string variable	Initialize a string variable to store the Dynamics 365 users who meet the criteria in the app
List D365 users	Query Dynamics 365 for active users. Provide the following as the Filter Query: isdisabled eq false
For each	A looping component to loop through the Dynamics 365 users returned from "List D365 users"
Compose	The Compose action is used to formulate a Dynamics 365 ODATA query for the next action, "List tasks for user". The Compose action's expression is the following: @concat('_createdby_value eq ', items('For_each')?['systemuserid'], ' and createdon gt ', adddays(utcnow(), -1) ) The adddays function uses utcnow with negative one to formulate a date one day ago. Adjust this date criteria to meet your specific requirements.
List tasks for user	Query Dynamics 365 for Task records that meet the filter criteria as formulated in the previous Compose action. Set the Filter Query field of the D365 query to the results of the Compose action.
Check for any tasks	This Condition block checks whether the query for D365 tasks returned any records for the system user. @equals(empty(body('List_tasks_for_user')['value']), true) If the action "List tasks for user" did not return any records then the equals function results in a true value.
If true: Add D365 user's name	The "If true" block includes a Compose block named "Add D365 user's name to string variable". This action adds the D365 user's first name, last name and email address to the string variable defined earlier.
Check whether any D365 users have zero tasks	The Condition "Check whether any D365 users have zero tasks" checks the length of the string variable using the following expression: @equals(length(variables('UsersWithZeroTasks')), 0) This is necessary to avoid sending an email unnecessarily.
If false, Send an email	This action uses Office 365 to send an email. The body of the email is set to the string variable, which contains a list of one or more D365 users.

Other ways to meet this requirement includes:

• In Dynamics 365: Custom Action and Dynamics 365 plug-in. Create a custom action then create and register a plug-in that runs when the action is called. Invoke the action using an Azure scheduled job (e.g., Azure Function) or Windows scheduled task.
• Azure Function: Use an Azure Function to provide all of the functionality. Schedule the function to run once per day.

Create an Azure Function to interact with Dynamics 365 Online (Customer Engagement)

Overview

This post provides details for creating an Azure Function (C# Script; .csx) that connects to Dynamics 365 Online (Customer Engagement). This approach uses a combination of an Azure AD application and Application User in Dynamics 365 for server-to-server authentication.

There are several benefits to using Azure Functions with Dynamics 365. Some of these benefits include:

• Perform D365 CRUD operations, run custom actions, etc. using the IOrganizationService interface
• Run the function on a schedule (e.g., run each day at 1 AM)
• Trigger the function via HTTP, new file in Azure storage, message queue, etc.
• "Serverless": No Windows machines to maintain
• Use functions from Azure Logic Apps, which has 100+ connectors to other services

Prerequisites

• Rights to create resources in an Azure subscription
• Admin account for a Dynamics 365 instance (v8.2 or newer)
• Azure and Dynamics 365 are in the same tenant

Create an Azure AD App Registration

In the D365 connectivity approach described in this article, the Azure Function(s) you create will obtain an authentication access token from Azure AD. An access token is used to allow your Function to access Dynamics 365 without the need to provide credentials. This section, then, provides the steps for creating an Azure AD app registration for the purpose of obtaining an access token.

You can utilize the same Azure AD app registration for multiple Azure Functions. Thus, when providing the name for the Azure AD application registration, you may want to provide a generic name such as "AzureFunctionsD365ClientApp".

Follow these steps to create an Azure Active Directory app registration and collect the information needed by the Azure Function.

1. Open a new notepad (or other) file to paste values that you'll collect below.
2. Sign in to the Azure Portal
3. Click Azure Active Directory in the far-left navigation menu
4. Click "App registrations"
5. Click Endpoints (at the top)
6. Copy the Federation Metadata Document URL to a text editor file. In particular, you will need the GUID that follows the ".com" in the URL. This is the Azure AD Tenant ID (Also known as Directory ID).
7. Close the Endpoint blade and click "New application registration"
1. For the app registration name (e.g., "AzureFunctionsD365ClientApp")
2. Select "Web app / API" as the application type.
3. Enter https://localhost or any valid URL in this field. (Note: Since this article describes how to connect to Dynamics 365 from an Azure Function in the same tenant, the URL in this field will not be used.)
4. Click the Create button
8. Open the Application Registration that you just created. Copy the Application ID and paste to the text file.
9. Click Settings, then click Keys. In the "Passwords" section, create a new key by entering "NeverExpires" in the description field and selection "Never expires" for the duration. Click Save and then copy the key value to the text editor
10. You may now close the Azure AD screens (blades)

Next, you will create an Application User record in Dynamics 365 and link that record to the Azure AD registration created above.

Create a Dynamics 365 Application User

This section provides the steps for creating an Application User account in Dynamics 365. This account will be connected to the Azure AD app registration to provide server-to-server connectivity from Azure to Dynamics 365.

1. Sign in (as an administrator) to the Dynamics 365 instance that your Azure Functions will connect to.
2. Navigate to Settings >> Security >> Users
3. Change the view to Application Users
4. Click the New button
5. Create the new Application User
1. Make sure the form name is "User: Application User"
2. User Name: Provide a user name that describes the main purpose for the Application User. For example, if you intend to use the Application User for general purpose use in Dynamics 365 (e.g., from Azure Functions or other Azure applications), then enter a name such as "AzureDynamics@yourdomain.com".
3. Application ID = Provide the Application ID (GUID) that you copied to a text file when creating the Azure AD registration
1. Note: Dynamics 365 allows only one Application User per Application ID
4. First Name = Enter "Azure Dynamics" or other name to identify the user record
5. Last Name = Enter “Application” (recommended) or any other value
6. Primary Email = You can enter the same value as entered for the User Name, or enter any other valid email address.
7. Click Save
6. Assign a custom security role for the Application User.

Create an Azure Function

This section provides the steps and source code for an Azure Function that connects to Dynamics 365 Online (Customer Engagement).

1. Sign in to the Azure Portal
2. (Optional) Create a Resource Group for this and future Azure Function apps
3. Create a Function App following the steps in the linked article
1. Note: A function app is the container that hosts individual functions. Thus, when providing a name for the function app, enter a name that describes the purpose for the individual functions. For example, if the functions in the function app will send email messages on a schedule from data in Dynamics 365, then enter a name such as "D365ScheduledEmailsFunctionApp".
4. In the Function App, create a function using the HTTP trigger template (C#).
1. Function name example: D365HappyBirthdayEmail
5. Within the function, click the "View files" tab. Then click the Add button and enter "project.json" as the file name. Copy and paste the project.json content provided below and then click Save. (Use Notepad++ to remove blank lines if they appear when you paste the text.)
6. Click run.csx to open the Azure function C# code in the edit window. Copy and paste the code provided in the run.csx section below. Click Save to save the function.
7. Provide actual values from Dynamics 365 and Azure in the constants set up toward the beginning of the function.
8. Save and test the function. The expected result is a number that represents the number of accounts created in the Dynamics 365 instance in the past two years.

project.json

{
  "frameworks": {
    "net46":{
      "dependencies": {
        "Microsoft.CrmSdk.CoreAssemblies": "8.2.0.2",
        "Microsoft.IdentityModel.Clients.ActiveDirectory": "2.22.302111727"
      }
    }
   }
}

run.csx

using Microsoft.IdentityModel.Clients.ActiveDirectory; 
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query; 
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.WebServiceClient; 
using System;
using System.Collections.Generic; 
using System.Linq; 
using System.Net.Http; 
using System.Net;
using System.Text; 

private const string DYNAMICS_365_URL = "https://contoso.crm.dynamics.com"; 
private const string AZURE_TENANT_ID = "YOUR TENANT'S ID (GUID) GOES HERE"; 
private const string AZURE_AD_APPLICATION_ID = "YOUR AZURE AD APP ID (GUID) GOES HERE";
private const string AZURE_AD_APPLICATION_KEY = "YOUR AZURE AD APP KEY GOES HERE"; 
private const string AZURE_AD_AUTHORITY_URL = "https://login.microsoftonline.com/"; 

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info("C# HTTP trigger function processed a request.");

    var requestedToken = GetD365AccessToken(); 
    string value = string.Empty;

    try 
    { 
        using (var crmService = new OrganizationWebProxyClient(GetServiceUrl(), false)) 
        { 
            crmService.HeaderToken = requestedToken;

            // Example: Retrieve an aggregate value from a FetchXML query.
            string fetchXml = GetFetchXmlQuery();
            EntityCollection queryResults = crmService.RetrieveMultiple(new FetchExpression(fetchXml));
            if (queryResults != null)
            {
                Entity record = queryResults.Entities.FirstOrDefault();
                value = ((AliasedValue)record["RecordCount"]).Value.ToString();
                log.Info("value = " + value);
            }
        } 
    } 
    catch (Exception ex) 
    { 
        log.Error(ex.Message); 
        var result = new HttpResponseMessage(HttpStatusCode.InternalServerError); 
        result.Content = new StringContent(ex.Message); 
        return result; 
    } 

    return req.CreateResponse(HttpStatusCode.OK, value);

}

// Get an authorization token from Azure AD
private static string GetD365AccessToken() 
{ 
    var clientCredential = new ClientCredential(AZURE_AD_APPLICATION_ID, AZURE_AD_APPLICATION_KEY); 
    var authenticationContext = new AuthenticationContext(AZURE_AD_AUTHORITY_URL + AZURE_TENANT_ID); 
    var authenticationResult = authenticationContext.AcquireTokenAsync(DYNAMICS_365_URL, clientCredential); 
    var requestedToken = authenticationResult.Result.AccessToken; 
    return requestedToken; 
} 

private static Uri GetServiceUrl() 
{ 
    return new Uri(DYNAMICS_365_URL + "/xrmservices/2011/organization.svc/web?SdkClientVersion=8.2"); 
} 

private static string GetFetchXmlQuery()
{
    return @"<fetch aggregate='true' >
      <entity name='account' >
        <attribute name='name' alias='RecordCount' aggregate='count' />
        <filter type='and' >
          <condition attribute='createdon' operator='last-x-years' value='2' />
        </filter>
      </entity>
    </fetch>";
}

Related Articles

Accessing Azure App Services using Azure AD Bearer token

Build web applications using Server-to-Server (S2S) authentication

Use Azure Logic App with Dynamics 365 CE to Report on Aggregate Values

Overview

This solution uses Azure Logic Apps and Functions to build an HTML table of Dynamics 365 aggregated values (e.g., the sum of a numeric value across multiple records) and sends an e-mail with the HTML content. The application runs on a set schedule.

Business Uses

Send an e-mail on a fixed schedule with the following information (examples):

• Sales totals by region
• Number of leads created across territories
• Average case resolution time

Building the Solution

Prerequisites

Azure Storage Account: This Logic App will run multiple Dynamics 365 FetchXML queries in order to obtain the aggregate values to send in an e-mail. In this example, the Logic App will retrieve FetchXML

Follow the steps in blog article "Create an Azure Function to interact with Dynamics 365 Online (Customer Engagement)" to create the Azure AD app registration, D365 Application User and the base Azure Function.

Steps

Create a Logic App

Start with the "Recurrence Trigger" template

For the Recurrence action, set the interval and frequency to the desired values

Create Azure Storage Account
Create a general-purpose Azure storage account

.