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.  

Updating Different CRM Data Types with PowerShell

In my introductory post on updating Dynamics CRM records with PowerShell here I walked through the simplest example of how to update a text field.  If you're new here please refer to that post as it also contains other important building blocks for accessing the CRM API with PowerShell.  Please get familiar with the structure of the script there.  In this post I'll show how to update additional field types.  CRM treats certain field as simple data types, but others are entered as objects and require some additional coding.

Again, I'm using Microsoft Dynamics CRM 2013 and PowerShell version 3, but this code should work on most, if not all versions.

Using this post as a template, the code in each of the following sections should go in SECTION 6 of the script.  Each field you want to update on the CRM record will need one of these lines configured for your specific field.

Text Fields
# Nothing special here.  Just set the value with a string.  
$recordToUpdate["mytextfield"] = "My New Text"

Numeric Fields
# Similar to text, but sometimes I've needed to precede the value with the data type.  This may or may not be necessary depending on where the data is coming from.  
$recordToUpdate["mynumberfield"] = [decimal]25

Boolean (Two Options)
# Set the value of a boolean field with system variables ($true or $false) or system values (1 or 2)
$recordToUpdate["myboolfield"] = $true

Date/Time fields
# CRM accepts date fields as a string as long as it's formatted correctly.  I'm in the US, so for me it's [MM/DD/YYYY HH:MM:SS].  
$recordToUpdate["mydatefield"] = "01/01/1900 00:00:00"

Currency 
# Store a new Money object in a variable and add the value in the ArgumentList parameter.
$priceCurrency = New-Object -TypeName Microsoft.Xrm.Sdk.Money -ArgumentList 25

# Set the field value to the Money variable as follows
$recordToUpdate["mycurrencyfield"] = [Microsoft.Xrm.Sdk.Money] $priceCurrency

Option Sets 
# Store a new OptionSeValue object in a variable and add the value in the ArgumentList parameter.
$optionSetValue = New-Object -TypeName  Microsoft.Xrm.Sdk.OptionSetValue -ArgumentList 100000

# Set the field value to the OptionSetValue variable as follows
$recordToUpdate["myoptionsetfield"]= [Microsoft.Xrm.Sdk.OptionSetValue] $optionSetValue 

In a future post I will show how to update lookup fields and append records through the API with PowerShell.

CRM Data Updates with PowerShell - Introduction

This will be the first post in a series on how to update large groups of Dynamics CRM records using PowerShell.  I use this whenever there's a request to update roughly 1000 or more records, otherwise I'd use the Bulk Edit or Export/Re-import from Excel, or maybe a workflow.  Unfortunately CRM limits you to 250 records per page, so large updates have forced me to look to other methods.

Why PowerShell?
The reason I like to use PowerShell for this is because it's lightweight, comes installed on most Windows machines, and just feels easier to understand than C# to me (I'm not a developer).  A lot of people might not realize you have the same access to all SDK assemblies through PowerShell.  It just looks a little different in PowerShell, and once you get the hang of it, it becomes easy to bust out a script in no time.

Background Info and Disclaimer: 
I don't claim to be an expert in PowerShell or anything else for that matter.  In fact I've only been doing this for a year or so at the time of this post.  I got started with this from this useful blog post and have expanded upon this on my own.  Suggestions are welcome, just please be nice.  :)  For all of these I just use the basic Windows PowerShell ISE and am running PowerShell version 3.0.  Our CRM version is 2013 Rollup 2 on premise.

Here We Go
As an introduction to this topic this example will show how to update the name of a single existing account record.  In subsequent posts I hope to demonstrate how to update large lists of records from CSV, SQL queries, and CRM queries.  Beyond that I hope to show how to update different data types, appends and more.

#Begin Script-------------------------------------------------------------------------------------------------
# SECTION 1: Add the CRM SDK Assemblies from the bin folder of the SDK
Add-Type -Path "***\Bin\Microsoft.Xrm.Sdk.dll"
Add-Type -Path "***\Bin\Microsoft.Xrm.Client.dll"
Add-Type -Path "***\Bin\Microsoft.Crm.Sdk.Proxy.dll"


# SECTION 2: Create CRM connection and save in a variable
$url = "https://myCRM.domain.com"
$user = "DOMAIN\myUserID"
$pwd = Read-Host "Enter password for account $user"
$crmConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$url; Username=$user; Password=$pwd")


# SECTION 3: Save the Organization Service in an object
$service = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $crmConnection


# SECTION 4: Create account entity object to store the new data
$recordToUpdate = New-Object -TypeName Microsoft.Xrm.Sdk.Entity -ArgumentList "account"


# SECTION 5: Assign the record GUID that you want to update as the Id property of the entity object. This is basically telling the API which record to update.  
$recordToUpdate.Id = "[Put GUID here]"


# SECTION 6: List any fields as attributes of the entity variable and set their values
$recordToUpdate["name"] = "New Name Text"


# SECTION 7: Call the Update command using the Organization Service, and pass in the account entity object we configured above.  
$service.Update($recordToUpdate);

#END Script--------------------------------------------------------------------------------


If everything went correctly, running this script will update the "name" field with the new text.  This example just updates the one text field.  To update fields of different data types please refer to this post.