How to Find Your CRM Online IP Address

I was recently asked to build a CRM extension to submit Opportunity records to an external API. We decided to build this as an asynchronous custom workflow activity so it would trigger upon Create, but also could be called manually. Behind the scenes I use C# to make an HTTPWebRequest, using values from the Opportunity record.  So, the user would create an Opportunity record, then run the workflow which gathers data from the Opportunity and sends it the the external API for processing.

Making a web call from CRM is generally straightforward, but this case was a little different - the external API required us to register our IP address with them for authentication, and then use that IP address in all subsequent calls.  At first I though finding CRM's IP address would be easy - I'd just use one of the many IP lookup sites out there and plug in the web url (e.g., "https://myorg.crm.dynamics.com"), and we'd be done.  But it's not that straightforward (it never is).  

CRM uses separate servers for asynchronous processes (asynchronous workflows included).  So while your front end application server ("https://myorg.crm.dynamics.com") has one IP address, the custom workflow activity will come from a different one.  How do you find the IP address of your asynchronous server?  After trying many methods, I used the code below to do this.  This code makes a WebRequest call to a site called "ipify", which tells you your IP address from programmatic calls.  It then reads the response (the IP Address) into an Output argument which the CRM workflow puts into a field on the Opportunity record.  

There may be easier ways to find your IP address, but I did it this way because of the limited access we have to server level information in the cloud.  I tried several DNS property calls and got security errors.  So the last way I could think was to record on the receiving end of the call (thanks https://www.ipify.org/ !!!).  

Disclaimer: CRM Online uses several IP ranges which you can find here.  While this code will tell your current IP address at the time of the call, your IP address can and will change unexpectedly.  That said, this will tell you want range you're in, and I hope this code is helpful to someone out there because I spent many hours before finally figuring this out.  

string tmaurl = "https://api.ipify.org";

WebRequest request = WebRequest.Create(tmaurl);                                    

WebResponse response = request.GetResponse();

WebHeaderCollection header = response.Headers;

using (var reader = new System.IO.StreamReader(response.GetResponseStream()))

{

    string responseText = reader.ReadToEnd();

    this.ServerMessage.Set(executionContext, responseText);

}

[Output("Server Message Output")]

public OutArgument<string> ServerMessage { get; set; }

Dynamics CRM Performance Test with PowerShell

This is a little script I made as a sort of performance test for CRM.  Basically, how it works is it queries 1000 Contact records from CRM and tracks the time it takes to loop through them and make an update to each one.  It's designed to update a single field (in this case, 'fax') with a dummy value, then change it back.  So, to not overwrite valid data it queries only records where the field to be updated is null, makes the update, then it changes it back to null.

Furthermore, it writes the time it takes to make all the updates to a csv file of your choosing and then restarts itself to run after a certain amount of time (currently 1 hour).  Over time it creates a log of these durations which you can build charts and reports off of.

One thing to point out is that the list of 1000 contact records is queried through the SQL database, so as written this script only works for on premise deployments.  In order to make it work for CRM Online instances you'll need to change the SQL query to a CRM query call to the API.  A few of my other posts should show you how to accomplish this.

Please remember, this is a performance test.  Speedy, efficient code is not the goal here, but rather understanding the time it takes to run.  You may notice some unnecessary steps being performed.  I'm just trying to hit as many areas of the system as possible to get as much of a complete read as possible.

This script might be useful to measure differences in speed throughout the day, or to compare the system before and after large changes.  In my own case, I built this script to get some kind of measurement of the system prior to us enabling CRM Auditing.  There were concerns that this feature would slow down CRM.  And for those interested, there was no noticeable difference shown to us.

I hope this is useful to someone out there.

#Add SDK references
    Add-Type -Path "[mySDKPath]\Bin\Microsoft.Xrm.Sdk.dll";
    Add-Type -Path "[mySDKPath]\Bin\Microsoft.Xrm.Client.dll";
    Add-Type -Path "[mySDKPath]\Bin\Microsoft.Crm.Sdk.Proxy.dll";

#CRM Connection
    $CRMUrl = "https://[myCRMPath]";
    $CRMLogin = "[myCRMAccount]";
    $CRMPwd = Read-Host "Enter password for account $CRMLogin"
    $CRMConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$CRMUrl; Username=$CRMLogin; Password=$CRMPwd");
    $CRMService = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $CRMConnection;

#SQL Connections
    $sqlUser = "[mySQLAccount]"
    $sqlPwd = Read-Host "Enter password for SQL account $sqlUser" -AsSecureString
   
#Source datasource
    $sourceDatabaseServer = "[mySQLDatabaseServer]"
    $sourceDatabase = "[mySQLDatabase]"
    $sourceConnectionString = "Server=$sourceDatabaseServer;uid=$sqlUser; pwd=$sqlPwd;Database=$sourceDatabase;Integrated Security=true;"
    $sourceConnection = New-Object System.Data.SqlClient.SqlConnection
    $sourceConnection.ConnectionString = $sourceConnectionString

function PerformanceTest{
#SQL Query
    #Source Database Query
    $top = "1000"
    $sourceConnection.Open()
    $sourceQuery = “SELECT TOP $top contactid, fax
                    FROM [mySQLTable]
                    WHERE fax is null”
    $sourceCommand = $sourceConnection.CreateCommand()
    $sourceCommand.CommandText = $sourceQuery
    $sourceResult = $sourceCommand.ExecuteReader()
    $Global:sourceTable = New-Object “System.Data.DataTable”
    $sourceTable.Load($sourceResult)
    $sourceConnection.Close()

    #Looping Through Records
    $count = 0
    $startTime = Get-Date
    foreach($record in $sourceTable){
        $count ++;
        #Query Record
        $CRMQuery = New-Object -TypeName Microsoft.Xrm.Sdk.Query.QueryExpression -ArgumentList "contact";
        $CRMQuery.ColumnSet.AddColumn("firstname");
        $CRMQuery.ColumnSet.AddColumn("lastname");
        $CRMQuery.ColumnSet.AddColumn("telephone1");
        $CRMQuery.ColumnSet.AddColumn("telephone2");
        $CRMQuery.ColumnSet.AddColumn("gendercode");
        $CRMQuery.ColumnSet.AddColumn("address1_country");
        $CRMQuery.ColumnSet.AddColumn("ownerid");
        $CRMQuery.ColumnSet.AddColumn("address2_country");
        $CRMQuery.ColumnSet.AddColumn("address1_line1");
        $CRMQuery.ColumnSet.AddColumn("address2_line1");
        $CRMQuery.ColumnSet.AddColumn("emailaddress1");
        $CRMQuery.ColumnSet.AddColumn("emailaddress2");
        $CRMQuery.Criteria.AddCondition("contactid", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, $record.contactid.Guid);
        $CRMQuery.Criteria.AddCondition("fax", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Null);
        $CRMQueryResults = $CRMService.RetrieveMultiple($CRMQuery);
        $CRMRecords = $CRMQueryResults.Entities

        #Create Entity Object and identify
        $targetRecord = New-Object -TypeName Microsoft.Xrm.Sdk.Entity -ArgumentList "contact";
        $targetRecord.Id = $CRMRecords[0].Id.Guid;
       
        $targetRecord["fax"] = "test data";
        $CRMService.Update($targetRecord);

        $targetRecord["fax"] = $null;
        $CRMService.Update($targetRecord);

        Write-Host "Record"$record.contactid": $count/$top"
    }
    $endTime = Get-Date
    Write-Host "`n`nStarted: "$startTime
    Write-Host "Ended:   "$endTime
    $duration = New-TimeSpan $startTime $endTime
    Write-Host "`nDuration: $duration"
    Add-Content -Path "[myPath]\PerformanceTest.csv" "$startTime,$duration,$top,PROD" -Force

    Write-Host "Timing out for 1 hours"
    Start-Sleep -Seconds 3600

    PerformanceTest
}
PerformanceTest

CRM Queries Using PowerShell

This is a continuation of several posts about interacting with the Microsoft Dynamics CRM API using PowerShell and the SDK.  Please see my earlier posts for more information on the structure of these scripts.

For this post I will show how to query a list of records using the SDK through PowerShell using the CRM query expression object.  As you read you'll notice that it resembles the Advanced Find more that regular SQL, so if you have a more complex query then SQL would be better.  I may show how to use SQL against the CRM database through PowerShell in a later post.

When the tools in the user interface aren't enough, this is the easiest way to get a list of records to process in CRM (that I know of).  It will return records as an object that includes the GUID and any other fields you specify.  You can then lead the records into a variable and loop through them for processing (updates, deletes, appends, etc.).

Section 1 below is the standard load of the sdk dll's so we can call the sdk.

Section 2 below is the usual CRM connection setup.

Section 3 contains the CRM query.
-----------------------------------------------------------------------------------
#1 Add SDK references
Add-Type -Path "C:\[mySDKLocation]\Bin\Microsoft.Xrm.Sdk.dll";
Add-Type -Path "C:\[mySDKLocation]\Bin\Microsoft.Xrm.Client.dll";
Add-Type -Path "C:\[mySDKLocation]\Bin\Microsoft.Crm.Sdk.Proxy.dll";

#2 CRM Connection
$CRMUrl = "https://mycrm.com";
$CRMLogin = "myUserAccount";
$CRMPwd = Read-Host "Enter password for account $CRMLogin" 
$CRMConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$CRMUrl; Username=$CRMLogin; Password=$CRMPwd");
$CRMService = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $CRMConnection;

#3 CRM Query
$CRMQuery = New-Object -TypeName Microsoft.Xrm.Sdk.Query.QueryExpression -ArgumentList "myEntityType";
$CRMQuery.ColumnSet.AddColumn("myFieldName");
$CRMQuery.Criteria.AddCondition("myFieldName", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, "myValue");
$CRMQueryResults = $CRMService.RetrieveMultiple($CRMQuery);
$CRMRecords = $CRMQueryResults.Entities

#Add code to loop through and process each record, or whatever
--------------------------------------------------------------------------------------------------------------

Section 3 Explained: 
This block of code creates the Query Expression object and defines which entity will be queried.  The AddColumn command lets you add fields on the entity as attributes to the results.  The AddCondition method lets you add a Condition Operator to use (Equal, Contains, Begins With, etc.) to filter your results.  After that's set up, we use the CRM Service connection to execute the query and load the results into a variable.  And lastly, we load the entities from the results into another variable to isolate just the records.  At this point the $CRMRecords variable is a list of records that you can loop through using a "foreach" block.

Additional Condition Operators:
The example above uses the "Equals" condition operator, but this can be replaced with a variety of others much like the Advanced Find through the CRM user interface.  You could rely on intellisense to show you the list, or you could look at all the filter options displayed in the Advanced Find.  They are largely the same.

Delete CRM Records Using PowerShell

There are times when I receive a request to delete a large list of specific records from CRM.  There's always the Bulk Delete option that CRM provides out of the box, but that relies on the Advanced Find to generate your list.  And as good as the Advanced Find is, it also has its limitations when compared to regular SQL.  So when the list of records cannot be generated through the Advanced Find I turn to a PowerShell call to the API instead.

In this example I have the list of CRM records in a CSV file that gets imported as an array of objects.  The only requirement here is to have the record ID (GUID) of the record you want to delete.  Deleting records is a little easier because you don't need to create an entity object.  You just provide the record id and entity logical name ('salesorder' in this example), and call the Delete command.

As always, this script follows the same template I showed in my first post.  Please refer to that for more details.


#Add SDK references
Add-Type -Path "MySDKPath\Microsoft.Xrm.Sdk.dll";
Add-Type -Path "MySDKPath\Microsoft.Xrm.Client.dll";
Add-Type -Path "MySDKPath\Microsoft.Crm.Sdk.Proxy.dll";

# Configure CRM connection
$CRMUrl = "http://MyCRMURL";
$CRMLogin = "MyDomain\MyUserID";
$CRMPwd = Read-Host "Enter password for account $CRMLogin"
$CRMConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$CRMUrl; Username=$CRMLogin; Password=$CRMPwd");
$CRMService = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $CRMConnection;

#Import List of Records to Process
$sourceRecords = Import-Csv "C:\MyListOfRecords.csv"

#Optional Counter to show progress
$count = 0

#Process each record in the csv file.  
foreach($record in $sourceRecords){
    $count ++;
    Write-Host "Deleting record $count of"$sourceRecords.Count".  Record ID = "$record.salesorderid
    $CRMService.Delete("salesorder", $record.salesorderid.Guid);
    }
}

Associating Related CRM Records Via PowerShell

Hello.  This is the third post in a series about working with the Microsoft Dynamics CRM API using PowerShell.  If you haven't read the earlier posts, please do as the examples here rely on the code template in this post.

Here, I plan to show how to update lookup fields and append child records in CRM using PowerShell and the CRM API.  I felt this required a separate post because there are two ways to do it and the calls are different depending on which method you use.

As usual, I'm using Microsoft Dynamics CRM 2013 on premise at the time of this post.  However this script should work for all version 2011 and beyond.

Option 1
This option is probably the simplest and can be best thought of as simply populating a lookup field. In CRM lookup fields are objects, and require two attributes to properly populate: Entity logical name and record ID (a GUID).  Another way to think of it is appending a child record to a parent.  In this example, the code needs to be run from the child record (i.e., the record with the lookup field).

To populate a lookup field, you need to create a new EntityReference object.  Then you'll set the entity name as the LogicalName attribute and the record GUID as the Id attribute.  Once that's set then you can simply set the field value and use the Update call as we've seen in my prior posts:

$lookupObject = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference;
$lookupObject.LogicalName = "account";
$lookupObject.Id = "[myGUID]";
$targetRecord["mylookupfield"] = [Microsoft.Xrm.Sdk.EntityReference] $lookupObject;

$service.Update($recordToUpdate);


Option 2
This way is basically the opposite of the previous.  Instead of populating the lookup field on the child record, this second option is run from the parent record and will append child records to it.

#Create an EntityReferenceCollection object which will contain one or more child entities, stored in one or more Entity Reference objects.  
$relatedEntities = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReferenceCollection;
$childEntity1 = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference;
$childEntity2 = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference;

#Add the child entity(ies) to the EntityReferenceCollection variable.  
$relatedEntities.Add($childEntity1);
$relatedEntities.Add($childEntity2);

#Assign the Id and LogicalName of each child record to identify which record we're appending to the parent.  
$childEntity1.Id = "[myGUID]";
$childEntity1.LogicalName = "account";

$childEntity2.Id = "[myGUID]";
$childEntity2.LogicalName = "account";

#to associate the records we need to know which relationship in CRM to use.  Save this in a Relationship object as follows.  
$CompanyRelationship = New-Object -TypeName Microsoft.Xrm.Sdk.Relationship -ArgumentList "account_parent_account"
$CompanyRelationship.PrimaryEntityRole = "Referenced";

#Use the Associate command to append the records.  Arguments required for the Associate command are 1) Parent entity logical name, 2) Parent GUID, 3) Relationship object, and 4) the child entity collection.  
$service.Associate("account", "myParentRecordGUID", $CompanyRelationship, $relatedEntities); 

Clearly, the simplest way to append records is Option 1 above as it's basically populating a lookup field.  But if the case arises where you need to trigger the command from the parent record, Option 2 might be the way to go.

Thanks for reading.