PowerShell | Rajeev Pentyala - Microsoft Power Platform

[Code Snippet] Xrm.Data.Powershell | Get-CrmRecordsByFetch

March 28, 2023 Rajeev Pentyala Leave a comment

In this article lets see how to execute the Get-CrmRecordsByFetch operation in PowerShell using Microsoft.Xrm.Data.PowerShell library.

What is Microsoft.Xrm.Data.PowerShell

The Microsoft.Xrm.Data.Powershell library is a PowerShell module that provides cmdlets for working with Microsoft Dynamics 365 and Dataverse data.
The library provides cmdlets for performing CRUD operations (Create, Retrieve, Update, and Delete) on entities in Dynamics 365 and Dataverse.
It also provides cmdlets for working with metadata, running FetchXML queries, and executing custom actions and workflows.

In this article, I will provide the code snippet for Get-CrmRecordsByFetch operation. Get-CrmRecordsByFetch operation retrieves CRM records by using FetchXML query.

Scenario:

I need to fetch ‘User’ table records, if either a ‘Domain Name’ or ‘Primary Email’ matches with the passed value.

Following is the FetchXML.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="systemuser">
    <attribute name="systemuserid" />
    <filter type="and">
      <filter type="or">
        <condition attribute="internalemailaddress" operator="eq" value="user@email.com" />
        <condition attribute="domainname" operator="eq" value="user@uname.com" />
      </filter>
    </filter>
  </entity>
</fetch>

Now lets see how to execute the Fetchxml using Get-CrmRecordsByFetch operation.

Code Snippet:

Following code executes the FetchXML and read the response.

function Get-User-By-Email-or-DomainName{
 param(
    [Parameter()] [String] [AllowEmptyString()]$filterValue,
    [Parameter(Mandatory)] [Microsoft.Xrm.Tooling.Connector.CrmServiceClient]$conn
    )

    $matchedUser = $null
    $fetchxml = @"
    <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
      <entity name='systemuser'>
        <attribute name='systemuserid' />
        <filter type='and'>
          <filter type='or'>
            <condition attribute='internalemailaddress' operator='eq' value='$filterValue' />
            <condition attribute='domainname' operator='eq' value='$filterValue' />
          </filter>
        </filter>
      </entity>
    </fetch>
"@

    Write-Host "Request XML - "$fetchxml
    $records = Get-CrmRecordsByFetch -Fetch $fetchxml-conn $conn
    try{
        $json = ConvertTo-Json $records # Optional step to print the Response in json format
        Write-Host "Response - $json"        
        
        # Loop through the matching records and gets the first record.
        if($records -and $records.CrmRecords){  
            $matchedUser = $records.CrmRecords[0]
            # Read the column value

            $systemUserId = $matchedUser.systemuserid
        }
    }
    catch {
        Write-Host "An error occurred in Get-User-By-Email-or-DomainName: $($_.Exception.Message)"
    }

    return $matchedUser
}

:)

Categories: Power Platform Tags: FetchXML, Get-CrmRecordsByFetch, PowerShell

[Code Snippet] PowerShell | Azure DevOps | Query Variable Group using API

July 20, 2022 Rajeev Pentyala Leave a comment

In Azure DevOps, Variable groups store values and secrets that you might want to be passed into a YAML pipeline or make available across multiple pipelines.

Variable Group is a collection of variables with Name and Value pair.

Below is my variable Group ‘BillingSystemDictionary’ with 3 variables in my DevOps organization ‘RajeevPentyala0011’ and project ‘Demo’ (These details are required in PowerShell script).

In DevOps pipeline scenarios, you may need to fetch the variables under a variable group. Following is the PowerShell script which calls DevOps API and fetch variables by variable group name.

    # Form API authentication Header
    $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
    $headers.Add("Authorization", "Bearer $(System.AccessToken)")
    $headers.Add("Content-Type", "application/json")

    # Pass Variable Group Name and read variables
    $variableGroupName = "BillingSystemDictionary"
    # 'projectid' pattern is https://dev.azure.com/{organization_name}/{projectname}
    $projectid = "https://dev.azure.com/RajeevPentyala0011/Demo"

    $variableGroupGetUrl = "$projectid/_apis/distributedtask/variablegroups?groupName=$variableGroupName*&queryOrder=IdDescending&api-version=6.0-preview.2"

    $queryResponseObject = Invoke-RestMethod -Uri $variableGroupGetUrl -Method GET -Headers $headers
 
    # $queryResponseObject will be of type pscustomobject
    # To fetch the variable value of "isactive"
    $variableName = "isactive"
    $variableValue = $queryResponseObject.value.variables.$variableName.value
    Write-Host "$variableName value is $variableValue"

🙂

Categories: CRM, DevOps Tags: API, Azure Devops, PowerShell, Variables

Azure DevOps | YAML | Could not load type ‘System.Management.Automation.PSSnapIn’

July 11, 2022 Rajeev Pentyala Leave a comment

I got the following exception while executing a PowerShell script in my Azure DevOps pipeline step.

Import-Module : Could not load type 'System.Management.Automation.PSSnapIn' from assembly 'System.Management.Automation, Version=7.2.5.500,

Reason:

I used following ‘pwsh‘ task in my YAML file to execute the PowerShell script.

steps:
- pwsh: |
    <My Script>
  env:
    SYSTEM_ACCESSTOKEN: $(System.AccessToken)
  displayName: 'My Task'
  condition: succeeded()

For unversed, pwsh is the cross-platform edition of PowerShell built on .NET Core / .NET 5+; by contrast, powershell is the executable name of the legacy Windows PowerShell edition (v5.1-), built on the Windows-only .NET Framework (v4.8-).
The issue is ”System.Management.Automation’ is built on .Net Framework and ‘pwsh‘ can’t be used in this case.

Fix:

Replace pwsh task with powershell task in YAML file.

steps:
- powershell: |
    <My Script>
  env:
    SYSTEM_ACCESSTOKEN: $(System.AccessToken)
  displayName: 'My Task'
  condition: succeeded()

🙂

Categories: DevOps Tags: PowerShell, pwsh

[Step by Step] Data Export Service – Replicate CDS data to Azure SQL Server Database

March 14, 2020 Rajeev Pentyala 1 comment

For unversed, The Data Export Service (DES) is an add-on service available on AppSource that adds the ability to replicate data to target destinations such as Azure SQL Database and SQL Server on Azure virtual machines.

In this article, lets see how to replicate CDS data to Azure SQL Server Database.

Prerequisites:

Subscribe to Dynamics 30 days trial and get Office 365 account.
Connect to Power Apps maker portal using Office 365 account and create a simple Model Driven App with ‘Account’ entity.
Install ‘Data Export Service’ add-on from AppSource.
Subscribe to Azure 30 days trial using the same Office 365 account.

DES_5

Azure SQL Server and Database.
Generate Azure Key Vault URL using Power Shell.

Installing ‘Data Export Service’ from AppSource:

From your Model Driven App, go to Settings -> Microsoft AppSource
Search for ‘Data Export Service’ and click on ‘Get it now’.

DES_1

In the next steps, select your Organization and click on ‘Agree’.

DES_3

Wait for the solution to get installed.

DES_4

Configuring Azure SQL Server and Database:

Connect to your Azure Portal and create a new SQL Database Server resource.

DES_6

Post creation, copy the ‘Server name’ and ‘Server admin’ and ‘Subscription ID’, which you would need in next steps.

DES_7

Now we need to create a new Database where you would replicate your CDS data to.
You can either create new Database from Azure Portal or from SQL Server Management Studio (SSMS). I am using SSMS to create the new Database.
Connect to Azure SQL server from SSMS using ‘Server name’ and ‘Server admin’ details captured in previous steps.

DES_9

Create a new Database and copy the name (i.e., DES in my case).

DES_10

Now go to Azure Portal and open the DES SQL database. Click on ‘Connection Strings’.

DES_11

Copy the ‘ADO.NET’ connection string which you would need in next steps.

DES_12

Generate Azure Key Vault URL using Power Shell and create Export Profile:

To connect to Azure SQL Server Data Export Service(DES) requires the SQL Server connection string.
DES will not accept the plain connection string.
DES accepts only Azure key vault URL with connection string stored as Secret.

As a standard practice, we configure Azure Key Vault URL using PowerShell script using following steps:

From the Model Driven App, go to Settings -> Data Export
- Note: Make sure you disable pop-up blockers.
Click on ‘+New’ to create ‘Export Profile’.

DES_13

From the pop-up, fill the details other than ‘Key Vault URL’ and click on information icon.

DES_14

Copy the PowerShell helper script from the pop-up.

DES_15

Now open the ‘Windows PowerShell’ and open a new window.
Paste the PowerShell helper script copied from previous step.
In the ‘PLACEHOLDER’ section provide the details specific to your Azure subscription.
- $subscriptionId – Use the copied value from previous steps.
- $keyvaultName – Your desired name.
- $secretName – Your desired name.
- $resourceGroupName – Either use existing or new. If a resource group doesn’t already exist a new one will be created.
- $location – provide the location (i.e., East US/West US/…)
- $connectionString – Use the copied value from previous steps.
- $organizationIdList – Dynamics organization ID. From the Model Driven App, go to Settings > Customizations > Developer Resources. The organization Id is under ‘environment Reference Information’.
- $tenantId – You can get it from ‘Azure Portal -> Azure Active Directory -> App registrations -> Endpoints‘ by copying the highlighted value as below.
Once all the required details provided, your PowerShell looks as below.

DES_17

Run the script which creates a new Azure Key Vault. You should get output as below.

DES_18

Now that the ‘Key Vault URL’ is formed, copy the value from Azure Portal -> Key vaults -> Secrets’

DES_19

Go back to the DES ‘Data Export Profile’ pop up and paste the ‘Key Vault URL’.
Click on ‘Validate’ to make sure the connection works.

DES_24

Click ‘Next’ and pick your CDS entities which would want to replicate to Azure SQL.

DES_29

As a last step click on ‘Create & Activate’ to complete the set up.

DES_30

Give it sometime and you should see the Metadata tables and ‘Account’ entity tables created in your target (i.e., Azure SQL Server).

DES_31

Notes:

Data Export Service can be used with model-driven apps in Dynamics 365, such as Dynamics 365 Sales and Customer Service.
Only entities that have change tracking enabled can be added to the Export Profile. Enable change tracking to control data synchronization
To use the Data Export Service the model-driven apps in Dynamics 365 and Azure Key Vault services must operate under the same tenant and within the same Azure Active Directory.
The Data Export Service does not drop (delete) the associated tables, columns, or stored procedure objects in the destination Azure SQL database when the following actions occur and these items must be dropped manually.
- An entity is deleted.
- A field is deleted.
- An entity is removed from an Export Profile.

Refer article to know more about Data Export Service. To troubleshoot connectivity issues refer article

🙂

Categories: Dynamics 365 Tags: Azure Key Vault, Data Export Service, PowerShell

Rajeev Pentyala – Microsoft Power Platform

Archive