Archive | PowerShell

We had a user delete a Marketing List. I needed to recreate it. I went to a database backup and found the GUID of the deleted list.
Then I used the following SQL query to find the GUIDs of all the members of that list:

SELECT FullName
    ,ParentCustomerIdName
    ,[EntityId]
    ,[ListId]
    ,[ListMemberId]
  FROM [CRMDataBaseName].[dbo].[ListMember],[CRMDataBaseName].[dbo].Contact
  where ListId = '787b77ca-c47d-431b-863e-12a98969b097' AND 
  [EntityId] = ContactId
  order by LastName,FirstName

I saved the EntityId column to a text file, and then I used the following PowerShell code to loop through the GUIDs and add them to a new MarketingList

$ListMembers = Get-Content C:\IT\Temp\ListMemberGUIDs.txt
foreach ($EntityId in $ListMembers){
$xml = ""
$xml += "<s:Envelope xmlns:s='http://schemas.xmlsoap.org/soap/envelope/'>";
$xml += "  <s:Body>";
$xml += "    <Execute xmlns='http://schemas.microsoft.com/xrm/2011/Contracts/Services' xmlns:i='http://www.w3.org/2001/XMLSchema-instance'>";
$xml += "      <request i:type='b:AddMemberListRequest' xmlns:a='http://schemas.microsoft.com/xrm/2011/Contracts' xmlns:b='http://schemas.microsoft.com/crm/2011/Contracts'>";
$xml += "        <a:Parameters xmlns:c='http://schemas.datacontract.org/2004/07/System.Collections.Generic'>";
$xml += "          <a:KeyValuePairOfstringanyType>";
$xml += "            <c:key>ListId</c:key>";
$xml += "            <c:value i:type='d:guid' xmlns:d='http://schemas.microsoft.com/2003/10/Serialization/'>5deb4efb-4ed7-47f3-8e8e-bb487e0db423</c:value>";
$xml += "          </a:KeyValuePairOfstringanyType>";
$xml += "          <a:KeyValuePairOfstringanyType>";
$xml += "            <c:key>EntityId</c:key>";
$xml += "            <c:value i:type='d:guid' xmlns:d='http://schemas.microsoft.com/2003/10/Serialization/'>$($EntityId)</c:value>";
$xml += "          </a:KeyValuePairOfstringanyType>";
$xml += "        </a:Parameters>";
$xml += "        <a:RequestId i:nil='true' />";
$xml += "        <a:RequestName>AddMemberList</a:RequestName>";
$xml += "      </request>";
$xml += "    </Execute>";
$xml += "  </s:Body>";
$xml += "</s:Envelope>";
 
$url="http://crm.sardverb.com/SardVerbinnen/XRMServices/2011/Organization.svc/web"
 
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.Open('POST', $url, $false)
$http_request.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute")
$http_request.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
$http_request.setRequestHeader("Content-Length", $xml.length)
$http_request.send($xml)
}

I wanted to mark a meeting/appointment as completed via code. I came up with the PowerShell script below. Maybe it will be of use to some one?

FUNCTION JBM-CRM-SetState {
PARAM(
    [string][ValidateSet("crmserver.company.com", "dev-crmserver.company.com")]$ServerName="crm.sardverb.com",
    [string][ValidateSet("CRMOrganizationName")]$OrganizationName="SardVerbinnen",
    [string][parameter(Mandatory=$true)][ValidateSet("email", "phonecall", "appointment")]$EntityType,
    [string][parameter(Mandatory=$true)][ValidateScript({ $_ -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")})]$TargetGUID,
    $State=1,$Status=3,[switch]$MyDebug
    )
$requestMain = ""
$requestMain += "<s:Envelope xmlns:s=`"http://schemas.xmlsoap.org/soap/envelope/`">";
$requestMain += "  <s:Body>";
$requestMain += "    <Execute xmlns=`"http://schemas.microsoft.com/xrm/2011/Contracts/Services`" xmlns:i=`"http://www.w3.org/2001/XMLSchema-instance`">";
$requestMain += "      <request i:type=`"b:SetStateRequest`" xmlns:a=`"http://schemas.microsoft.com/xrm/2011/Contracts`" xmlns:b=`"http://schemas.microsoft.com/crm/2011/Contracts`">";
$requestMain += "        <a:Parameters xmlns:c=`"http://schemas.datacontract.org/2004/07/System.Collections.Generic`">";
$requestMain += "          <a:KeyValuePairOfstringanyType>";
$requestMain += "            <c:key>EntityMoniker</c:key>";
$requestMain += "            <c:value i:type=`"a:EntityReference`">";
$requestMain += "              <a:Id>$TargetGUID</a:Id>";
$requestMain += "              <a:LogicalName>$EntityType</a:LogicalName>";
$requestMain += "              <a:Name i:nil=`"true`" />";
$requestMain += "            </c:value>";
$requestMain += "          </a:KeyValuePairOfstringanyType>";
$requestMain += "          <a:KeyValuePairOfstringanyType>";
$requestMain += "            <c:key>State</c:key>";
$requestMain += "            <c:value i:type=`"a:OptionSetValue`">";
$requestMain += "              <a:Value>$State</a:Value>";
$requestMain += "            </c:value>";
$requestMain += "          </a:KeyValuePairOfstringanyType>";
$requestMain += "          <a:KeyValuePairOfstringanyType>";
$requestMain += "            <c:key>Status</c:key>";
$requestMain += "            <c:value i:type=`"a:OptionSetValue`">";
$requestMain += "              <a:Value>$Status</a:Value>";
$requestMain += "            </c:value>";
$requestMain += "          </a:KeyValuePairOfstringanyType>";
$requestMain += "        </a:Parameters>";
$requestMain += "        <a:RequestId i:nil=`"true`" />";
$requestMain += "        <a:RequestName>SetState</a:RequestName>";
$requestMain += "      </request>";
$requestMain += "    </Execute>";
$requestMain += "  </s:Body>";
$requestMain += "</s:Envelope>";
if ($MyDebug){write-host $requestMain}

$url="http://$ServerName/$OrganizationName/XRMServices/2011/Organization.svc/web"

$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.Open('POST', $url, $false)
$http_request.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");
$http_request.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
$http_request.setRequestHeader("Content-Length", $xml.length);
$http_request.send($requestMain);
if ($MyDebug){$http_request.responseText}
}

I wanted to bulk create a bunch of users in CRM. PowerShell and the Microsoft CRM 2011 REST/OData endpoint make it easy. Here is a function to create a SystemUser via PowerShell

FUNCTION JBMURPHY-CRM-CreateSystemUser {
PARAM([string][ValidateSet("crmserver.company.com", "dev-crmserver.company.com")]$ServerName="crmserver.company.com",
[string][ValidateSet("CRMOrganizationName")]$OrganizationName="CRMOrganizationName",
[string]$BusinessUnitId="BusinessUnitGUID",
[string]$SystemUserDomain="CRMSystemUserDomain",
[string][parameter(Mandatory=$true)]$FirstName,
[string][parameter(Mandatory=$true)]$LastName,
[string][parameter(Mandatory=$true)]$UserName,[switch]$MyDebug
)
[string]$url="http://$ServerName/$($OrganizationName)/xrmservices/2011/OrganizationData.svc/SystemUserSet"

$SystemUserInfo = @{
DomainName="$($UserName)@$($SystemUserDomain)"
FirstName=$FirstName
LastName=$LastName
BusinessUnitId=@{LogicalName="businessunit";Id=$BusinessUnitId}
}
$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
$SystemUserInfoData=$json.Serialize($SystemUserInfo)

$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Accept", "application/json")
$http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
$results=$http_request.send($SystemUserInfoData)
if ($MyDebug){
$http_request.statusText
$http_request
}
$SystemUserId=$($json.DeserializeObject($http_request.responseText)).d.SystemUserId
return $SystemUserId
}

Below is a PowerShell function to create a contact in CRM 2011. Hope it is helpful to some one.

FUNCTION JBM-CRM-CreateContact {
PARAM(
    [string][ValidateSet("crmserver.company.com", "dev-crmserver.company.com")]$ServerName="crmserver.company.com",
    [string][string][ValidateSet("CRMOrganizationName")]$OrganizationName="CRMOrganizationName",
    [string][parameter(Mandatory=$true)]$FirstName,
    [string][parameter(Mandatory=$true)]$LastName,
    [string]$MiddleName,[string]$Suffix,[string]$Email,[string]$JobTitle,
    [string]$Telephone,[string]$Description,[string]$ParentCustomerId,[string]$Address_Line1,[string]$Address_Line2,
    [string]$Address1_Country,[string]$Address1_PostalCode,[switch]$MyDebug
    )

$ContactInfo = @{
FirstName=$FirstName
LastName=$LastName
MiddleName=$MiddleName
Suffix=$Suffix
EMailAddress1=$Email
NickName=$NickName
JobTitle=$JobTitle
Telephone1=$Telephone
Address1_Line1=$Address_Line1
Address1_Line2=$Address_Line2
Address1_City=$Address1_City
Address1_PostalCode=$Address1_PostalCode
Address1_Country=$Address1_Country
Description=$Description
}
if (!($ParentCustomerId -eq "")){
$ContactInfo.Add("ParentCustomerId" , @{Id=$ParentCustomerId;LogicalName= "account"})
}

if ($MyDebug){
$ParentCustomerId
$ContactInfo
}

$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
$ContactInfoData=$json.Serialize($ContactInfo)

$url="http://$ServerName/$($OrganizationName)/xrmservices/2011/OrganizationData.svc/ContactSet"
 
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Accept", "application/json")
$http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
$results=$http_request.send($ContactInfoData)
if ($MyDebug){
$http_request.statusText
$http_request
}
$ContactId=$($json.DeserializeObject($http_request.responseText)).d.ContactId
return $ContactId
}

I wanted to create some crm activities based on data in a spreadsheet. Looping thorough the csv file would be easy, the challenge came when I wanted to create new activities in Microsoft CRM 2011, specifically appointments. Below is a PowerShell function that I pieced together to create a new activity if you know GUIDs of the contacts for the regarding, required fields. This is a more fleshed out function compared to this

FUNCTION JBM-CRM-CreateActivityODATA {
PARAM([string][parameter(Mandatory=$true)][ValidateSet("Email", "PhoneCall", "Appointment")]$EntityType,
    [string][ValidateSet("crmserver.company.com", "dev-crmserver.company.com")]$ServerName="crmserver.company.com",
    [string][ValidateSet("CRMOrganizationName")]$OrganizationName="CRMOrganizationName",
    [string][parameter(Mandatory=$true)][ValidateScript({ $_ -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")})]$OwnerGUID,
    [string][ValidateScript({ $_ -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")})]$RegardingGUID,
    [ValidateScript({-not @( $_ | where {$_ -notmatch("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$") }).Count})]$ReqiredGUIDs,
    [string]$Subject,[string]$Description,[datetime]$ScheduledStart,[datetime]$ScheduledEnd,[string]$Location
    )

Function CreateActivityParty{
    PARAM($ActivityId,$ParticipationTypeMask,$EntityType,$GUIDs)

    foreach ($Id in $GUIDs){
    $activityParty = @{}
    write-host $Id
    $activityParty.Add("PartyId", @{Id= $Id;LogicalName= "contact"})
    $activityParty.Add("ActivityId", @{Id= $ActivityId;LogicalName= "$($EntityType.ToLower())"})
    $activityParty.Add("ParticipationTypeMask" , @{ Value=$ParticipationTypeMask })
    $json=new-object System.Web.Script.Serialization.JavaScriptSerializer
    $activityPartyData=$json.Serialize($activityParty)
    $activityPartyData

    $url="http://$ServerName/$($OrganizationName)/xrmservices/2011/OrganizationData.svc/ActivityPartySet"
 
    $http_request = New-Object -ComObject Msxml2.XMLHTTP
    $http_request.open('POST', $url, $false)
    $http_request.setRequestHeader("Accept", "application/json")
    $http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
    $http_request.send($activityPartyData)
    $http_request.statusText
    $results=$http_request
    }
}

$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
$activityObject = @{
ActivityTypeCode=$EntityType
Subject=$Subject
Description=$Description
ScheduledStart=$($ScheduledStart.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ"))
ScheduledEnd=$($ScheduledEnd.ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ssZ"))
Location=$Location
RegardingObjectId=@{
Id= $RegardingGUID
LogicalName= "contact"
}
}

$json = new-object System.Web.Script.Serialization.JavaScriptSerializer
$activityData=$json.Serialize($activityObject)


$url="http://$ServerName/$($OrganizationName)/xrmservices/2011/OrganizationData.svc/$($EntityType)Set"
 
$http_request = New-Object -ComObject Msxml2.XMLHTTP
$http_request.open('POST', $url, $false)
$http_request.setRequestHeader("Accept", "application/json")
$http_request.setRequestHeader("Content-Type", "application/json; charset=utf-8")
$http_request.send($activityData)
$results=$http_request
$ActivityId=$($json.DeserializeObject($results.responseText)).d.ActivityId

# Set Required Attendees
CreateActivityParty -ActivityId $ActivityId -ParticipationTypeMask 5 -EntityType $EntityType -GUID $ReqiredGUIDs
# Set Orgaizer
CreateActivityParty -ActivityId $ActivityId -ParticipationTypeMask 7 -EntityType $EntityType -GUID $OwnerGUID
}