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.