Earlier this year I wrote a post that showed how to perform sentiment analysis in Dynamics CRM using Microsoft Azure Text Analytics. Azure Text Analytics makes it incredibly easy to use sentiment analysis (with English text only), but the full Azure Machine Learning offering is much more powerful. In today's post I will show how to create a custom predictive web service in Azure ML and make predictions with it in Dynamics CRM.

Why am I doing this?

One of the exciting announcements about Dynamics CRM 2016 is that it includes some sort of integration with Azure ML, so what's the point of this blog post? Actually, here are two reasons:

1. It's not clear (as of late November 2016) what capabilities the standard CRM-Azure ML integration will have. The approach demonstrated here allows for total customization.
2. The approach below can be used right now in any version of CRM (online or on-premise) from CRM 2011 onward.

The demo data set

For this demonstration I am using data from the AdventureWorks data warehouse sample database to build a model to predict whether a contact in CRM is likely to be a bicycle buyer. I created a flat file of contacts that includes several independent variables and a yes/no flag for whether they purchased a bicycle. That sample file is included in my solution source linked at the bottom of this post.

You can also download the full AdventureWorks database from CodePlex here.

Getting started with Azure ML

As I was trying to get the hang of Azure ML, I found the Bluewater SQL blog tremendously helpful. If you're just getting started with Azure ML, I suggest you take a look at both of these posts before continuing. (These two posts also use the AdventureWorks data warehouse sample database, though I had already decided to use it for my demo before I found this site. I guess the AdventureWorks data is an obvious choice for this sort of thing.)

1. https://bluewatersql.wordpress.com/2014/07/31/azure-machine-learning-first-look/
2. https://bluewatersql.wordpress.com/2014/08/01/azure-machine-learning-a-deeper-look/

Creating the predictive web service

Now that the introductory bits are out of the way, here's an overview of how I set up my predictive web service in Azure ML. I've also shared my work in the Cortana Analytics Gallery, so if you just want to see how the experiments are set up without going through all the steps yourself, skip to the end of this post for the gallery links.

1. Export your data set from CRM. I use a simple KingswaySoft SSIS package to do this. It's included in my sample project files.
2. Upload your data set to Azure ML Studio. I am using the Azure ML free tier, which requires me to manually upload the data set for the initial upload and any subsequent updates. If I were using the standard tier, it would be possible to upload the data set to Azure blob storage as part of my SSIS package. This would be useful for periodically retraining the model with updated data in the future.
3. Create and run a new experiment to classify bike buyers. I basically followed the steps outlined in the two Bluewater SQL posts, so take a look at those if you want to see a more detailed explanation.
4. Save the trained model from this experiment to use in your predictive web service.
5. Create a new experiment that will serve as the foundation for the predictive web service
6. Add the following items to the new experiment canvas:
7. Data set
8. Trained model from the earlier experiment
9. Score model component
10. Project columns transformation between the data set and score model component to exclude the lpa_bikerbuyername column
11. Project columns transformation after score model component to only include score labels
12. Run the experiment.
13. Create a predictive web service.
14. Run the experiment.
15. Deploy the web service.
16. Open the web service and copy the API key to use later.
17. Open the request/response page.
18. Copy the post URL to use later. Leave off the "&details=true" at the end.
19. Scroll down to find the JSON request/response samples and copy them for review later.

Making predictions from CRM

Once your predictive web service is set up, using it in Dynamics CRM is as simple as posting a JSON request to the web service and then parsing the JSON response. A few years ago, I wrote a code sample showing an easy way to make JSON requests from CRM custom assemblies that serves as the foundation for the workflow activity I'm using in this demo. There are only a few changes required to that sample:

1. Add an authorization header that includes the bearer API key you copied earlier.
2. Add input/output parameters that match the inputs and outputs for your predictive web service. I am also passing the API key and service endpoint as input parameters instead of hardcoding them.
3. Modify the JSON request/response classes to allow for serialization to/deserialization from the request and response messages. Because of how Azure ML passes the inputs/outputs, these are actually fairly generic, so you can probably use mine with no or minimal changes.
4. Send the correct request parameters and correctly handle the response parameters.

To demonstrate the custom workflow activity, I created a sample CRM dialog.

Here's where I supply the input parameters.

Here's what the dialog looks like when I run it from a contact record.

The code

You can get all the code for this demonstration from my GitHub repository here. The sample code includes the following:

1. CRM solution containing:
2. Contact entity with custom fields added and a system view for data export for Azure ML
3. Sample dialog (you would need to update the parameters with your own API key and endpoint details)
4. Compiled custom workflow activity to call the web service
5. Contact data ready for import to CRM
6. SSIS package solution for data export
7. Custom workflow activity source code

I've also shared my training and prediction experiments in the Cortana Analytics Gallery. Here are the links where you can open and import them into your own workspace:

1. Training experiment
2. Prediction experiment

What do you think about this approach? Does it seem like it would be useful to you in a real-world situation? Let me know in the comments!

Last year Ben Hosking said there was no way to move access teams between Dynamics CRM organizations, so I created a tool to do that. Last month Tanguy Touzard said I should turn my console application into an XrmToolBox plugin, so I did. Soon after that Daniel Cai said I should make something with the KingswaySoft SSIS Integration Toolkit, so I've now written a version of my access team template mover as an SSIS package.

The logic

KingswaySoft's Dynamics CRM SSIS adapter makes basic CRM retrieves and inserts easy, so I could have written a simple package to do a full copy of access team templates from a source to a target in about 10 minutes, but I wanted to include the same functionality as my XrmToolBox plugin to enable access teams on the relevant entities in the target organization if desired.

Unfortunately the KingswaySoft adapter doesn't make working with Dynamics CRM metadata easy, so I had to use an SSIS transformation script task to check whether access teams are enabled for a particular entity and enable them.

After the transformation script task checks whether access teams are enabled for the entities in the target organization and (optionally) enables them, a conditional split transformation passes teamtemplate records for entities that are enabled for access teams to a Dynamics CRM destination task to create or update the teamtemplate records in the target organization.

Here's what the data flow task looks like with all the components on the canvas:

Let's take a closer look at all of them.

The retrieve task

The data flow starts with a Dynamics CRM source task to retrieve teamtemplate records from the source organization. Although you could use an entity source type, I use FetchXML to offer greater flexibility. For example it would be possible to only sync access teams that have names starting with a particular string value.

The transformation script task

KingswaySoft's Dynamics CRM adapter supports the use of its connection managers in SSIS script tasks, but there are a few extra steps you need to take that are described in this blog post.

Because SSIS processes the data in a row-by-row fashion, I needed to use slightly different logic in the SSIS package to check for whether entities have access teams enabled and then enable them. In the PreExecute method, the package establishes source/target connections and then retrieves metadata for all the entities in the target system. Next it stores the objecttypecode values for each enabled entity in a list of strings. This actually took me a while to get right, because KingswaySoft returns an entity's logical name as the objecttypecode attribute instead of the actual numeric CRM objecttypecode value.

As the transformation script task loops through each row, it does the following:

1. Check whether the entity is enabled for access teams in the target. If so, set the value of an "enabled" output column to true.
2. If the entity isn't enabled for access teams in the target organization:
   1. If a package parameter to automatically enable access teams is set to true, update the entity in the target organization to enable access teams and set the "enabled" output column to true. Also add the entity's logical name to the list of enabled entities so the script won't unnecessarily update the target system if it encounters another teamtemplate record for the same entity later.
   2. Otherwise, set the "enabled" output column to false.

The conditional split

The conditional split transformation has two outputs - one for rows with the enabled column set to true and one for rows with the enabled column set to false. My version of the package doesn't actually do anything with the "disabled" output, but you could do something with those rows if you wanted.

The upsert

The package creates or updates teamtemplate records in the target organization with a Dynamics CRM destination upsert task.

All the columns are mapped except for the createdby and modifiedby attributes.

Package parameters

As I mentioned earlier, the package supports optionally enabling entities for access teams in the target system. That's done via a package parameter called "enableTargetAccessTeams." The package also has additional parameters for setting the source and target connection strings and passwords.

Wrapping up

You can get the package from my GitHub repository here. I hope to add more packages to this solution for synchronizing other reference data records like business units, teams and queues in the near future. Stay tuned!

Late last year I created a console application for moving access team templates between Dynamics CRM organizations, and I described it in this blog post. Following up on that, I've created an XrmToolBox plugin to make it even easier to move access team templates (with GUIDs) between CRM organizations. I've also added functionality to automatically enable access teams on the relevant entities in the target organization if desired.

Let's take a look at the tool in action.

Before execution

Here are the access team templates in the source organization.

Here are the access team templates in the target organization before we run the tool.

Note the "Dog" entity does not have access teams enabled.

Running the tool

First open XrmToolBox. Look at that handsome dog!

Next select the Access Team Template Mover plugin and connect to an organization. That organization is set as the source, but you can change it.

Click the select target button to select the target organization.

Once both source and target are set, the copy templates button will turn green and become enabled.

You can also uncheck the option to automatically enable access teams for target organization entities. If you do, you will receive a confirmation prompt.

Executing the copy with the enable access teams for target organization entities option checked copies three records in this scenario. It updates the access team template that already existed in the target and creates two new ones.

After execution

Because the enable access teams for target organization entities option was selected, the "Dog" entity in the target now has access teams enabled.

Finally here is the updated list of access team templates in the target organization.

Getting the plugin

The source code for the plugin is available in my GitHub repository here.

The compiled plugin can be downloaded here.

Last year I created a proof-of-concept solution that showed how to integrate Dynamics CRM with HP Haven OnDemand (then called HP IDOL OnDemand) to perform sentiment analysis and index records to support "find similar" queries. While I was working through the AzureCon challenge a few weeks ago, I thought it would be an interesting exercise to update my sentiment analysis code to work with the Text Analytics offering from the Microsoft Azure Marketplace.

The approach

As with my Haven OnDemand solution, the approach I'm using with Azure relies on a custom workflow activity that does the following:

1. Parse a supplied text input and strip any HTML tags using a helper function.
2. Create a JSON sentiment analysis request and post it to Azure Text Analytics with an HttpWebRequest.
3. Deserialize the JSON respsonse returned by Azure Text Analytics to a custom class object using a DataContractJsonSerializer.
4. Return the sentiment score to the calling process.

There are two main differences with the my Azure Text Analytics solution:

1. The Azure service only returns a sentiment score, so this custom workflow activity doesn't return a positive/negative string value.
2. Instead of embedding an access key in the custom workflow activity code, I've made it a parameter, which means you can take the solution straight from GitHub and start using it in your CRM organization as soon as you sign up for the Text Analytics service.

The solution in action

Here's a sample dialog I've created to demonstrate the use of the custom workflow activity.

Here's how the process works for the sample text "I hate you."

Wrapping it up on a more positive note, here's the same dialog with "I love you" instead.

As you can see, the score for "I hate you" is about .06, and the score for "I love you" is about .91, which makes sense as scores closer to one are more positive, and scores closer to zero are more negative.

The code

You can download all the custom code and a CRM solution extract from my Crm-Sample-Code repository on GitHub. Let me know what you think in the comments!

Last week I shared an approach for implementing next case functionality for Dynamics CRM so that users can get the "next" case to work from a queue just by clicking a button. In today's post I will show an easy way to add the same functionality to Unified Service Desk (USD), but as an added bonus the case will also open in a new session tab.

Before talking through the code and configuration elements in detail, let's take a look at exactly how the get next case button works in my USD demo instance.

I'm displaying the get next button in the left panel, but it can be displayed in any visible panel.

Once I click the button, a new session is opened if a "next" case is found in the queue.

If no "next" case can be found, an alert message is displayed.

There are three elements to make this work:

1. The custom workflow activity and custom action from my previous blog post.
2. A USD-specific web resource to expose the button and open the "next" case record.
3. USD configuration elements (hosted controls, action calls, etc.) to load the get next case web resource and handle its output.

Because there are no changes to the existing custom workflow activity or custom action, I'm not going to discuss those further in this post, but let's take a closer look at the other elements.

The web resource

In my previous post, clicking the "get next case" button searched a queue for a case and then generated a link for a user to manually click to open the case. To streamline the functionality in USD, I decided to modify the web resource to automatically open the case if one is found. If no case can be found in the queue, an alert message is displayed.

Because USD allows you to modify JavaScript logic as described in this MSDN post, I considered reusing my existing get next web resource from before, and adjusting the USD configuration to pop open the case record. Ultimately I decided it would be easier to just add a new "USD" version of the web resource, which is included in the CRM solution in the sample code at the bottom of this post. That web resource is almost identical to my previous version except for this section of code:

    //if a case is found
    if(caseId != '00000000-0000-0000-0000-000000000000') {
		var caseUrl = _server + "/main.aspx?etc=112&extraqs=&pagetype=entityrecord&id=%7b" + caseId + "%7d";
		////display the case number as a hyperlink
		//$("#outputdiv").append("Case: " + caseNumber + "");
        //automatically open case record
		window.open(caseUrl);
	}
	else {
		//otherwise display a message that no "next" case could be found
		//$("#outputdiv").append("No next case found");
		alert("No next case found");
	}

The USD configuration

Once the web resource is ready, the following USD configuration records need to be created:

1. Create a CRM page hosted control to display the get next button web resource. As mentioned above, I am displaying the web resource in the left panel, but it will work in any visible panel. 
2. Create a CRM page hosted control to display the case session. 
3. Create a session name session information record to display the case number on the session tab. This isn't strictly necessary, but it looks better than "new session." 
4. Create an action call to load the button. As in my previous post, the queue id and assign/remove from queue flags are supplied as encoded query string parameters. I've hardcoded the queue id, but you could use some sort of logic to dynamically set it. Also, you'll note the image here shows the assign and remove from queue flags set to false. 
5. Add the action call to the global manager DesktopReady event. 
6. Create a window navigation rule to display case (incident) records opened from the get next case button hosted control in the case session tab. 
7. (Optionally) Update any agent scripting configuration to handle cases being loaded in session tabs. In my USD configuration I already had agent scripting configured for accounts per this USD walkthrough, so I created agent scripting for case sessions, too.   
8. Finally you need to add all of these elements to the USD configuration record.

Wrapping up

You can download a CRM solution with the custom assembly, custom CRM action and web resources from my Crm-Sample-Code repository on GitHub, but you'll have to do the USD configuration on your own.

What do think about this approach? Could you see yourself using it in your CRM projects? Let me know in the comments!

Dynamics CRM offers sophisticated tools for working with cases and service queues, but sometimes users just want a quick and simple way to get the next case to work. In today's post, I'll share an easy way to implement this functionality in your Dynamics CRM organization.

There are three components to my approach:

1. First, there is a custom workflow activity that searches a queue for unassigned cases and assigns the one that entered the queue earliest to a user. This activity returns the assigned case id and case number as output parameters.
2. Second, there is a CRM action that that acts as a wrapper for the custom workflow activity.
3. Finally, there is a web resource that displays a button to call the CRM action and generate a link to the assigned case (or a message if there are no cases in the queue to assign).

Let's look at each of these pieces in more detail. If you'd rather get right to the code, scroll to the bottom of this post for a link.

The custom workflow activity

The custom workflow activity has the following parameters:

1. Queue - input - Reference to the queue that contains the case
2. User - input - Reference to the user who will be assigned the case
3. Assign - input - Flag for whether to assign the case or just return the case details
4. Remove from queue - input - Flag for whether to completely remove the case from the queue when it is assigned
5. Case id - output - Case GUID as a string (string because it makes working with non-existing cases easier in the custom action)
6. Case number - output - Case ticket number value
7. Case found - output - Flag for whether a case was found
8. Case title - output - Case title value

The activity then executes a FetchXML query to retrieve unassigned cases in ascending order of the date they entered the queue. If the "assign" flag is set to true, the first case is assigned to the user, and then the details are returned as output parameters. If the flag is set to false, the case details are returned as output parameters, but the case is not updated.

The CRM action

The action is the simplest part of the solution. It just takes input parameters to pass to the custom activity and then returns the output parameters.

The web resource

Finally the web resource uses JavaScript to call the action and display a link to the assigned case. If no "next" case can be found, then a message is displayed to the user. In my solution, the queue id and assign/remove from queue flags are supplied as encoded query string parameters.

The image below shows the output after the get next button is clicked:

The web resource approach allows you to embed the button in a dashboard or CRM form through an iframe, but you could also trigger the action from a ribbon button using similar JavaScript.

The code

You can download all the custom code and a CRM solution extract from my Crm-Sample-Code repository on GitHub. Happy case management!

This the final post in my five-part series on creating loosely coupled data interfaces for Dynamics CRM using RabbitMQ. In part 3 and part 4 I showed two approaches for building a Dynamics CRM plug-in that publishes notification messages to a RabbitMQ exchange. In today’s post I will show how to create a Windows console application that reads messages from a queue and writes the data to Dynamics CRM. The code for this application is available on GitHub in the LeadWriterSample project under the LucasCrmMessageQueueTools solution.

The approach

This application is extraordinarily simple. On startup it prompts the user to supply connection information for the RabbitMQ queue that it will monitor as well as a Dynamics CRM connection string. It then monitors the queue for new JSON-formatted messages. When new messages arrive, it attempts to deserialize them into a lightweight "leadtype" object, and then it creates new lead records in CRM. Once a message is successfully processed and a lead is created, the application then sends a confirmation back to RabbitMQ so that the message can be removed from the queue.

The following code shows what happens after a connection to the RabbitMQ is established:

//wait for some messages

var consumer = new QueueingBasicConsumer(channel);

channel.BasicConsume(_queue, false, consumer);

 

Console.WriteLine(" [*] Waiting for messages. To exit press CTRL+C");

 

//instantiate crm org service

using (OrganizationService service = new OrganizationService(_targetConn))

{

   while (true)

   {

     //get the message from the queue

     var ea = (BasicDeliverEventArgs)consumer.Queue.Dequeue();

 

     var body = ea.Body;

     var message = Encoding.UTF8.GetString(body);

 

     try

     {

       //deserialize message json to object

       LeadType lead = JsonConvert.DeserializeObject<LeadType>(message);

 

       try

       {

         //create record in crm

         Entity entity = new Entity("lead");

         entity["firstname"] = lead.FirstName;

         entity["lastname"] = lead.LastName;

         entity["subject"] = lead.Topic;

         entity["companyname"] = lead.Company;

         service.Create(entity);

 

         //write success message to cli

         Console.WriteLine("Created lead: {0} {1}", lead.FirstName, lead.LastName);

 

         //IMPORTANT - tell the queue the message was processed successfully so it doesn't get requeued

         channel.BasicAck(ea.DeliveryTag, false);

       }

       catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> ex)

       {

         //return error - note no confirmation is sent to the queue, so the message will be requeued

         Console.WriteLine("Could not create lead: {0} {1}", lead.FirstName, lead.LastName);

         Console.WriteLine("Error: {0}", ex.Message);

       }

     }

     catch(Exception ex)

     {

       //return error - note no confirmation is sent to the queue, so the message will be requeued

       Console.WriteLine("Could not process message from queue");

       Console.WriteLine("Error: {0}", ex.Message);

     }

   }

}

If this were to be used in production, I would have created a Windows service instead of a console application, but I wanted to make it easy to try out different connection parameters.

Verifying the application

The queuewriter.js application in the node-app directory in the GitHub repository contains a sample web page that can be used to publish lead data to the CRM-Leads queue. If the application is running, you can access the web page at http://<YOUR_SERVER_NAME>:3000/leadform. When the form’s submit button is clicked, an AJAX call posts a JSON object to the Node.js POST endpoint I showed in my previous post. If the LeadWriterSample console application is running, it will take the message from the queue and you will see a new lead record created in CRM. The screenshots below show each piece working.

The lead has been submitted via the web form, and a success message has been received from the Node.js endpoint.

The lead has landed in the CRM-Leads queue and is ready to be retrieved.

The console application has retrieved and processed the submitted lead message.

The lead record has been created in CRM.

One caveat about the demo lead form is that it has the RabbitMQ credentials embedded in the HTML source, so this code should not be used in production. My approach was originally formulated with the thought that a server-side process would build the JSON message to post to Node.js, so sensitive information would not be exposed. If you decide to use an AJAX post operation like is shown here, you would want to modify the queuewriter.js application to contain the credentials so they do not need to be passed from the end user’s web browser.

Wrapping up

That does it for this series, but I’ve just barely explored the capabilities of RabbitMQ. There’s so much more you can do with it than what I’ve shown here, and I hope I’ve piqued your interest about how you can use RabbitMQ or any other message broker in your Dynamics CRM projects. If you have any questions or want to continue the discussion, please share your thoughts in the comments.

A version of this post was originally published on the HP Enterprise Services Application Services blog.

Last week I decided to finally take a look at using OAuth2 as an authentication protocol with Dynamics CRM. I wanted to understand how it could enable non-Windows clients to consume CRM data. As it turns out, I was unable to find any documentation or comprehensive code samples for non-Windows clients, so I put together my own Node.js client, and I've added the code to my Crm-Sample-Code repository on GitHub here: https://github.com/lucasalexander/Crm-Sample-Code/tree/master/NodeClientDemo. Having endured a lot of frustration in getting this to work, I'd like to share some additional notes that might be helpful if you decide to start using OAuth2 with CRM.

If you're not already familiar with OAuth2, I suggest you take a look at this post on the Microsoft Dynamics CRM blog that explains how CRM uses OAuth at a high level: http://blogs.msdn.com/b/crm/archive/2013/12/12/use-oauth-to-authenticate-with-the-crm-service.aspx. Although there's no code in that post, it will help you understand how OAuth authentication works.

Infrastructure and environment prep

I am running Dynamics CRM 2015 and Active Directory Federation Services (AD FS) on a single Windows Server 2012 R2 Azure VM. CRM and AD FS are configured for IFD. The CRM website is running on port 443. AD FS is running on port 444.

Before writing any code, I completed all the prep work outlined in this CRM SDK walkthrough - https://msdn.microsoft.com/en-us/library/dn531010.aspx. Specifically I enabled OAuth2 for CRM, and I registered an OAuth2 client application in AD FS. Although this was all done as part of an on-premise Dynamics CRM deployment, I don't see any reason that it won't work with CRM Online.

The code

Now, let's take a look at the Node.js application. I chose to write a Node.js client instead of something in C# for two main reasons. First, Node.js made it easy for me to inspect and modify all the HTTP headers that are required for getting OAuth2 to work. Second, Node.js is basically server-side JavaScript, so most of the work I've done can be easily ported to a client-side JavaScript implementation.

My Node.js application is written using the Express web framework, and it serves four web pages via routes. They are:

2. / - This is the web application index page. It displays links to the login page and contacts display page.
3. /auth/login - This page handles redirection of the browser to the AD FS login page.
4. /auth/callback - This is the page to which AD FS redirects the user's browser after a successful login.
5. /authenticated/contacts - This page queries the CRM OrganizationData service for contacts using an OAuth token for authentication.

The basic flow of the application is:

2. A user starts on the index page. The page checks for an OAuth token in a session variable. If no token is present, a link to the login page is shown. If a token is present, a link to the contact display page is shown.
3. When a user navigates to the login page, it makes a request to the CRM OrganizationData service to request the correct URL to use for authentication and redirects the browser to that page. The client id, resource name and redirect uri are all included in the query string of the request to AD FS. (See https://github.com/nordvall/TokenClient/wiki/OAuth-2-Authorization-Code-grant-in-ADFS for more information on how this works.)
4. The user authenticates with AD FS, and then AD FS redirects the user to the callback page with an authorization code in the query string.
5. The callback page parses the authorization code from the query string and sends it to AD FS to request a token. The token is stored in a session cookie, and then the user is redirected to the index page, which should now show a link to the contact display page.
6. The contact display page reads the token from the session cookie and makes an Odata request to CRM with the token supplied as the authorization header. The results are then parsed and displayed.

A few caveats:

2. OAuth2 tokens eventually expire. The default AD FS OAuth2 token expiration value is 3600 seconds (one hour). It is possible to request a new token using a refresh token that is provided at the same time as the authorization token. Using the refresh token allows for reauthorization without needing to supply credentials again. My code sample does not demonstrate use of a refresh token.
3. My sample application doesn't have much in the way of error handling, and it has not been extensively tested. If you plan to use OAuth with CRM, I highly recommend you don't just deploy my code in production without any further testing.

Welcome back to my five-part series on creating loosely coupled data interfaces for Dynamics CRM using RabbitMQ. In my last post I showed how to build a Dynamics CRM plug-in that publishes notification messages to a RabbitMQ exchange using the official RabbitMQ .Net client library. Unfortunately, that plug-in can’t successfully communicate with a RabbitMQ server if it’s executed inside the Dynamics CRM sandbox, so in today’s post I will show how to achieve the same results with a sandboxed plug-in. The code for this plug-in is available on GitHub in the MessageQueueSandboxPlugin project under the LucasCrmMessageQueueTools solution.

The approach

As I mentioned in my previous post, last month I wrote a series of blog posts about how to create a near real-time streaming API using plug-ins and Node.js. That plug-in worked fine in the Dynamics CRM sandbox, and Node.js can easily publish messages to a RabbitMQ exchange, so today’s plug-in will post a JSON-formatted message to a Node.js application, and then that Node.js application will do the actual publishing to RabbitMQ. As a result, I only need to make a couple of minor modifications to my earlier Node.js message-posting plug-in so that it can pass the RabbitMQ connection parameters to my Node.js application. Additionally, the Node.js application that I described in my earlier series only needs a few changes to publish the message to a RabbitMQ exchange instead of sending it to Socket.IO clients.

The plug-in

The plug-in is registered for an operation (create, update, delete, etc.) with a FetchXML query in its unsecure configuration. When the plug-in step is triggered, its associated FetchXML query is executed, and then the resulting fields are serialized into a JSON object, which is then sent to a Node.js application called queuewriter.js via an HTTP POST request. The JSON object also needs to contain RabbitMQ connection details, so I pass them as part of the plug-in step’s unsecure configuration. Here’s the configuration XML fragment to enable case notifications:

<nodeendpoint>http://lucas-ajax.cloudapp.net:3000/rabbit_post_endpoint</nodeendpoint>
<endpoint>lucas-ajax.cloudapp.net</endpoint>
<exchange>CRM</exchange>
<routingkey>Case</routingkey>
<user>rabbituser</user>
<password>PASSWORDHERE</password>
<query><![CDATA[
<fetch mapping='logical'>
<entity name='incident'>
 <attribute name='ownerid'/>
 <attribute name='modifiedby'/>
 <attribute name='createdby'/>
 <attribute name='title'/>
 <attribute name='incidentid'/>
 <attribute name='ticketnumber'/>
 <attribute name='createdon'/>
 <attribute name='modifiedon'/>
 <filter type='and'>
  <condition attribute='incidentid' operator='eq' value='{0}' />
 </filter>
</entity>
</fetch>
]]>
</query>
</config>

Just like in my earlier Node.js plug-in, the FetchXML is extracted from the configuration XML, and the query is executed against Dynamics CRM. The results are then serialized to JSON using Json.NET just like before, except the serialized CRM data is included as a "message" object that is part of a parent JSON object that includes the RabbitMQ connection parameters. Here’s an example of the structure:

{

   "endpoint":"lucas-ajax.cloudapp.net",

   "username":"rabbituser",

   "password":"XXXXXXXX",

   "exchange":"CRM",

   "routingkey":"Lead",

   "message":{

     "property1":"value 1",

     "property2":"value 2",

     "property3":"value 3"

   }

}

Because this plug-in uses the Json.NET client library, it has to be merged with the plug-in assembly before registering it in Dynamics CRM. I’ve included a batch script called ilmerge.bat in the project directory on GitHub.

The Node.js application

The Node.js application (queuewriter.js) waits to receive JSON messages via HTTP POST from a client. When it receives a POST request, it checks whether the message is valid JSON. If it is, the RabbitMQ connection parameters are extracted and then the notification "message" object is published to the RabbitMQ exchange. If everything is successful, it sends "success" back as a response to the client. If any errors are encountered, it sends back a descriptive error message. I am using the node-amqp library for communicating with the RabbitMQ server, but the behavior isn’t that different from a .Net client. Here’s an extract with the relevant code:

if (request.method == 'POST') {

   request.on('data', function(chunk) {

     //check if received data is valid json

     if(IsJsonString(chunk.toString())){

       //convert message to json object

       var requestobject = JSON.parse(chunk.toString());

      

       //connect to rabbitmq

       var connection = amqp.createConnection({ host: requestobject.endpoint

       , port: 5672 //assumes default port

       , login: requestobject.username

       , password: requestobject.password

       , connectionTimeout: 0

       , authMechanism: 'AMQPLAIN'

       , vhost: '/' //assumes default vhost

       });

      

       //when connection is ready

       connection.on('ready', function () {

          //get the "message" property of the supplied request

          var message = JSON.stringify(requestobject.message);

         

          //post it to the exchange with the supplied routing key

          connection.exchange = connection.exchange(requestobject.exchange, {passive: true, confirm: true }, function(exchange) {

            exchange.publish(requestobject.routingkey, message, {mandatory: true, deliveryMode: 2}, function () {

              //if successful, write message to console

              console.log('Message published: ' + message);

             

              //send "success" back in response

              response.write('success');

             

              //close the rabbitmq connection and end the response

              connection.end();

              response.end();

            });

          });

       });

      

       //if an error occurs with rabbitmq

       connection.on('error', function () {

          //send error message back in response and end it

          response.write('failure writing message to exchange');

          response.end();

       });

     }

     else {

       //if request contains invalid json

       //send error message back in response and end it

       response.write("invalid JSON");

       response.end();

     }

   });

}

The complete queuewriter.js application is contained in the node-app directory in the GitHub repository.

Wrapping up

In addition to registering the plugin and registering a step to publish a notification message to RabbitMQ, you need to deploy and start the queuewriter.js application to publish messages. Once that’s done, you can verify everything is working as expected either by looking at the Queues tab in the RabbitMQ management web UI or running the CliConsumer sample application I showed in part 2.

Obviously using queuewriter.js as message proxy adds an extra layer of complexity, and you have to make sure that the application is up and running in order to process message, but it also offers a couple of advantages. First, by using queuewriter.js instead of a direct connection, you can easily use this same plug-in with different message brokers like Apache ActiveMQ and Microsoft’s Azure Service Bus. Second, the queuewriter.js application isn’t limited to just handling messages outbound from Dynamics CRM. You can also use it to process inbound messages without any changes. You just have to configure a client application to read messages from the queue and process them accordingly. A good example of this would be writing data submitted through a web form to Dynamics CRM via a RabbitMQ queue, and I will show that exact scenario in my next post!

A version of this post was originally published on the HP Enterprise Services Application Services blog.

This is the third post of a five-part series on creating loosely coupled data interfaces for Dynamics CRM using RabbitMQ.
Last time I showed how to install and configure a RabbitMQ server to support passing messages to and from Dynamics CRM. Today I will show how to build a Dynamics CRM plug-in that publishes notification messages to a RabbitMQ exchange using the official RabbitMQ .Net client library. The code for this plug-in is available on GitHub in the MessageQueuePlugin project under the LucasCrmMessageQueueTools solution.

Before going any further, let’s get some bad news out of the way. Plug-ins that execute in the Dynamics CRM sandbox cannot use RabbitMQ .Net client library to publish messages to a RabbitMQ server, so you can’t use today’s plug-in approach from a CRM Online organization. In my next post, I will be showing an alternate mechanism for publishing messages that you can use from a sandboxed plug-in, but today I want to focus on the most direct integration method. Now that we’re clear on the limitations of this approach, let’s get started!

The approach

Last month I wrote a series of blog posts about how to create a near real-time streaming API using plug-ins and Node.js. For this plug-in I’m going to basically copy the logic I used for the plug-in in that series.

This post outlines the approach in detail, but if you don’t want to read the entire thing, the basic idea was to create a plug-in that is registered for an operation (create, update, delete, etc.) with a FetchXML query in its unsecure configuration. When the plug-in step is triggered, its associated FetchXML query is executed, and then the resulting fields are serialized into a JSON object, which is then sent to the Node.js application via an HTTP POST request. Today’s plug-in operates in the exact same way, except instead of sending the JSON object to a Node.js endpoint, the JSON object will be published as a message to a RabbitMQ exchange.

Configuring the plug-in

To make the plug-in easily useable in any organization without needing to be recompiled, all the RabbitMQ connection parameters are stored in the unsecure configuration along with the FetchXML query for the data to retrieve. Here’s the configuration XML fragment to enable case notifications:

<config>
<endpoint>lucas-ajax.cloudapp.net</endpoint>
<exchange>CRM</exchange>
<routingkey>Case</routingkey>
<user>rabbituser</user>
<password>PASSWORDHERE</password>
<query><![CDATA[
<fetch mapping='logical'>
<entity name='incident'>
 <attribute name='ownerid'/>
 <attribute name='modifiedby'/>
 <attribute name='createdby'/>
 <attribute name='title'/>
 <attribute name='incidentid'/>
 <attribute name='ticketnumber'/>
 <attribute name='createdon'/>
 <attribute name='modifiedon'/>
 <filter type='and'>
  <condition attribute='incidentid' operator='eq' value='{0}' />
 </filter>
</entity>
</fetch>
]]>
</query>
</config>

Generating the notification message

Just like in my Node.js plug-in, the FetchXML is extracted from the configuration XML, and the query is executed against Dynamics CRM. The results are then serialized to JSON using Json.NET.

Publishing the message

The endpoint, exchange name, RabbitMQ user, RabbitMQ password and routing key values from the configuration XML are then used to establish a connection to RabbitMQ and publish the notification message to the exchange like so:

try
{
     //connect to rabbitmq
     var factory = new ConnectionFactory();
     factory.UserName = \_brokerUser;
     factory.Password = \_brokerPassword;
     factory.VirtualHost = "/";
     factory.Protocol = Protocols.DefaultProtocol;
     factory.HostName = \_brokerEndpoint;
     factory.Port = AmqpTcpEndpoint.UseDefaultPort;
     IConnection conn = factory.CreateConnection();
     using (var connection = factory.CreateConnection())
     {
         using (var channel = connection.CreateModel())
         {
             //tell rabbitmq to send confirmation when messages are successfully published
             channel.ConfirmSelect();
             channel.WaitForConfirmsOrDie();
            
             //prepare message to write to queue
             var body = Encoding.UTF8.GetBytes(jsonMsg);
 
             var properties = channel.CreateBasicProperties();
             properties.SetPersistent(true);
            
             //publish the message to the exchange with the supplied routing key
             channel.BasicPublish(_exchange, _routingKey, properties, body);
         }
     }
}
catch (Exception e)
{
     tracingService.Trace("Exception: {0}", e.ToString());
     throw;
}

If any errors are encountered, the message is captured via the tracing service, and then an exception is thrown.

Because this plug-in uses both the RabbitMQ .Net and Json.NET client libraries, they have to be merged with the plug-in assembly before registering it in Dynamics CRM. I’ve included a batch script called ilmerge.bat in the project directory on GitHub.

Wrapping up

After you register the plugin and register a step to publish a notification message to RabbitMQ, you can verify everything is working as expected either by looking at the Queues tab in the RabbitMQ management web UI or running the CliConsumer sample application I showed in
part 2.

A version of this post was originally published on the HP Enterprise Services Application Services blog.

Welcome back to this five-part series on creating loosely coupled data interfaces for Dynamics CRM using RabbitMQ. In my last post I discussed why you would want to incorporate a message broker into your Dynamics CRM data interfaces, and today I will show how to install and configure RabbitMQ to support the examples I’ll be presenting in the rest of the series.

Installation

First, you’ll need to download the installation files from here: http://www.rabbitmq.com/download.html. The RabbitMQ server runs on Windows, Linux, UNIX and Mac OS X, and there are installation guides for each supported platform. Because RabbitMQ is written in Erlang, you will need to install an Erlang VM before you can install RabbitMQ, but there is a download link provided in the installation guide. I set up my RabbitMQ server on a Windows 2012 server, and I was up and running in less than 10 minutes.

Once you’ve installed RabbitMQ and started the server, the easiest way to manage it is via the web-based management interface that’s included with the server distribution. You can enable the management interface with the rabbitmq-plugins tool. Run the following command to enable it: rabbitmq-plugins enable rabbitmq_management.

After the management plugin is enabled, you can access the web management UI from your server at http://localhost:15672. The default username is "guest" with "guest" as the password.

You’ll also need to configure any firewall rules necessary to allow access to your RabbitMQ server if it’s running on a server separate from your Dynamics CRM server. The default port is 5672, but that can be changed if you like. This page discusses RabbitMQ configuration in great detail.

Setting up users, queues and exchanges

The first thing you should do after the install is complete is change your default guest user password via the management UI. Then you can add additional users as necessary. For the examples in the rest of this series, you’ll need a user with full permissions on the default "/" virtual host. Here is what my "rabbituser" user account looks like:

Next you need to create the entities required to broker the messages between publishers and consumers. Before continuing, I recommend you take a moment to skim this Advanced Message Queuing Protocol (AMQP) overview document. If nothing else, at least read through the "hello, world" example section because it’s a great introduction to concepts that will be important throughout the rest of this series.

Queues
In the management UI, navigate to the Queues tab, and create two new durable queues named CRM-Cases and CRM-Leads. (You can create any queues you want, but my examples in the rest of this series use queues with those names.) The screenshot below shows the queues in my system.

Exchanges
After your queues are created, you can create an exchange and bindings to your queues so messages get routed correctly. Navigate to the Exchanges tab and create a new, durable exchange named CRM. After your CRM exchange is created, you should see something like the screenshot below.

Next, click on the name of the CRM exchange to open its edit screen. Scroll to the "add binding" section toward the bottom of the page and add a binding to the CRM-Cases queue for a routing key value of "Case" live in the following picture and click "bind."

Do the same for the CRM-Leads queue with "Lead" as the routing key. You should then see the two queues bound to the exchange.

Checking the configuration

At this point you should have everything in place to start publishing and consuming messages. You can verify your configuration works with the CliProvider and CliConsumer sample applications included in my GitHub repository as part of the LucasCrmMessageQueueTools solution.

First, build and run the CliProvider application. You will be prompted to supply basic connection details, and then you can type a message to publish to your RabbitMQ server.

Once the message has been published, you can verify there’s a message waiting in the correct queue on the Queues tab of the management UI.

Next, build and run the CliConsumer application. Once it connects to the CRM-Cases queue, the message will be retrieved and displayed.

When the CliConsumer application processes a message, it sends a confirmation back to queue that triggers removal of the message from the queue. You can check the Queues tab in the management UI to verify that the CRM-Cases queue is empty.

Wrapping up

That’s it for today. Your RabbitMQ server is now fully configured and ready for use with the examples in the rest of this series. Next time I will show how to send messages to a RabbitMQ exchange from a plug-in using the RabbitMQ .Net client library. See you then!

A version of this post was originally published on the HP Enterprise Services Application Services blog.

One of the things I love about Dynamics CRM is how easy it is to create data interfaces to enable integration with other systems. If you’ve worked with Dynamics CRM for any length of time, you’ve probably seen multiple web service integrations that enable interoperability with other line-of-business and legacy systems. A typical pair of inbound and outbound integrations might look like the picture below.

Using a tightly coupled connection between the source and target systems is usually the easiest (thus the quickest and cheapest) way to establish an integration, but this is often a bad idea. Consider the inbound scenario in which an external application is sending data to Dynamics CRM. What happens if the calling application misbehaves and starts sending thousands of requests per second? This has the potential to overload your CRM server and make it completely unusable. Now consider the outbound scenario in which a CRM plug-in calls an external web service. If the destination application’s web service is offline for a few minutes, the update from the CRM plug-in will not get received unless there’s some sort of error handling and retry logic built into the plug-in

An alternate approach

For these reasons, and lots of others (logging, security, scalability, just to name a few), it’s considered a best practice to create loosely coupled integrations that rely on a message broker that sits between the source and destination systems. Though the formal definition is more complicated, for our purposes a message broker can be thought of as a collection of queues that hold messages. Publishers write messages to queues, and then consumers pick up the messages and process them appropriately. Additionally, the message broker can be configured to keep messages in their queues until the consumers provide confirmation of successful processing.

Here’s an example of what the integrations I showed earlier would look like with a message broker.

For the outbound call from the CRM plug-in, the plug-in writes the message to a broker. The message is routed to a queue where it waits to be processed. A separate processing service application retrieves the message from the queue and sends it to the destination application. For the inbound call to CRM, the process works exactly the same, except the source and destination applications are reversed.

Why is a message broker better?

In the inbound call scenario, an effective message broker would typically be expected to handle a larger volume of inbound messages than Dynamics CRM because all it’s doing is receiving and routing the data without any additional processing. The processing service can then process the messages in the queue at a speed that doesn’t overload the Dynamics CRM server. In the case of the outbound call, the combination of a message broker and processing service can enable complex retry logic and custom logging without having to store it in the plugin layer. As an added bonus to either scenario, a message broker can provide a guarantee that messages don’t get lost between the source and destination systems as long as the message is successfully published to the broker.

Where do we go from here?

Over the course of my next four blog posts, I will show how to use RabbitMQ as a message broker in your Dynamics CRM data interfaces. I chose RabbitMQ for this series for several reasons:

1. It’s open source.
2. It runs on multiple platforms.
3. It’s easy to install and configure.
4. It’s fast at processing messages.

If you already have a different message broker in place in your organization or you would like to try a different message broker like Apache ActiveMQ or Microsoft’s Azure Service Bus, most of the approaches and a lot of the code I’m going to show in this series will still be applicable, with the notable exception of the post that discusses how to install and configure RabbitMQ.

Here’s the roadmap for the rest of the series:

• Part 2 – basic installation and configuration of a RabbitMQ
• Part 3 – creating a Dynamics CRM plug-in that publishes messages using the RabbitMQ .Net client library
• Part 4 – creating a sandboxed Dynamics CRM plug-in that publishes messages to RabbitMQ via Node.js
• Part 5 – reading messages from a queue and writing them to Dynamics CRM

If you just can’t wait to dig into the code, I’ve already posted everything to my repository on GitHub, so you can go ahead and take a look.

See you next time!

A version of this post was originally published on the HP Enterprise Services Application Services blog.