Export CRM Users List Using PowerShell

How this came about:

     I was asked by the Manager of the QA team to send her a list of who had access to the QA environment; after we performed a refresh from production. I'm fairly new to CRM and was looking for ways to do this.

1st) I logged into CRM and went to Settings, Security, Users, Selected all users and then clicked Export to Excel.

This would work just fine if it was a one-time deal. You would still need to do it for both Enabled and Disabled users. I don't believe it will be a one-time report so I started looking for a way automate the process.

2nd) Since these are Dev and QA environments I was not too concerned with querying the database. Below is the PowerShell Script I came up with that allowed me to query both environments at the same time and save the files as a .csv

Read More

CRM Audit Counts Report

During our first few deployment weeks, management wanted some metrics showing user activity. If you have auditing turned on CRM will track when Opportunities or Accounts are changed and will show who changed them. The actual change is not logged but you can at least so who made a change.

Management decided this metric would bring them some value so we created a quick SQL Job to send out a daily Audit report. This query will give you a count of all the Opportunity and Account changes over the last 5 days.

The script contains a hack needed to make the CSV display correctly in Excel. So the [sep=," portion of the script is a special character the Excel will ignore. If you open the CSV in a text editor you will see this special character. The consumers of this data would only use Excel to open the CSV the hack works great for this purpose.

Read More

Azure Speed test using DiskSpd

The decision was made to utilize Azure as IaaS for a large portion of our AX project. We learned some quick lessons in IO speed using Azure right off the bat. I created this quick post to document some of the tools and steps we used to measure IO speed that helped us identify various IO bottlenecks and re-engineer some of our environment settings.

Microsoft released a tool a while backed called DiskSpd. This tool replaced the old tool SQLIO. It is pretty easy to use but will require some tweaking of parameters to get it to simulate various read/write payloads.

100% Reads 8K Blocks

We started off using 100% Read Payloads using 8KB Blocks since SQL Server is organized in 8KB Pages.


You can review the documentation of DiskSpd for details on the various parameters and options.

The initial results were awful. The MB/s were below 10, AvgLat was above 80ms. We contacted Microsoft with our results and they sent us over some build guides to help us maximize performance. After we applied their settings the speed was better but still terrible compared to our On-Prem storage.

We ended up moving some of our DEV Servers to a Azure Data Center that had premium storage. One key take away from the Azure experience is that if you need high performing IO (for SQL Server and such) you will need to use Premium Storage and get ready to spend some cash.

I wrote a PowerShell script that would execute a DiskSpd test every 20 minutes and then store the results in a Database. We had several DEV servers in the project and i wanted to see how the disk performed over several days. Here is the PowerShell



The powershell script calls a real simple Stored Procedure on the SQL Server I am using to store the stats.

Read More

CRM Organization Import Could not find stored procedure p_CreateAuditEntry

During our DEV server builds we came across a bug during an import of CRM Organizations. When we would try to import a refresh of production to DEV we received the following error in the Install log.

Could not find stored procedure p_CreateAuditEntry

After some testing we figured out that this error happens when you try to restore a second copy of production on the same DEV machine as a different organization. We wanted multiple organizations on our DEV server to test different upgrades and such. The first copy of production would import just fine. However when we tried to refresh a second copy of prod into a different organization we would hit this error.

The fix is pretty simple, however you cannot run this fix in a production CRM envrionment. Running the fix will void your warranty with Microsoft because we are modifying the MSCRM_CONFIG database.

DO NOT RUN THIS IN YOUR PRODUCTION ENVIRONMENT EVER.

Connect to your MSCRM_CONFIG Database using SQL Server Management Studio and execute the script below.

Now you can try to import your organization again and it should no longer give you an error.

Read More

CRM Refresh using PowerShell

We just started using CRM and wanted to get rid of the manual process of refreshing CRM from Production to QA\Dev for our developers.

Our environments

Developer Boxes
We have 14 dev servers running on Windows 2012 R2.
These boxes run SQL 2012 and CRM 2015.

Dev Servers
We also have 2 Dev Environments that each have a CRM 2015 application server running on Windows 2012 R2 and a SQL 2012 Server.

QA Servers
We have 1 QA environment that is setup identical to the Dev environment and we have 1 QA environment that has 2 CRM 2015 application servers with one SQL 2012 server and one SSRS 2012 server.

Production Servers
 2 CRM 2015 application servers on Windows 2012 R2, 1 SQL 2012 Always On availability group and 1 SSRS Always On availability group. Then we have the same configuration at our Disaster Recovery site.

So with all these different environments we have been asked to refresh them periodically. The all in one developer servers are pretty straight forward since all of the software is installed you do not need to add install additional software.

MSCRM_CONFIG Hack

Proceed with Caution

We found a bug when moving between the various envrionments. Example: If you take the production database backup and restore it to  your DEV environment to create an Organization, then you try to create another Organization with that same backup you will receive the follow error during import:

Could not find stored procedure ‘p_CreateAuditEntry’

We were trying to setup multiple Organizations on a DEV instance to test various upgrades. In order to get around this we found a solution on this blog: http://www.powerobjects.com/2015/04/29/error-importing-organization-after-update-0-1/

The below scripts are a hack that you should NEVER do in a Production environment. Modifying the MSCRM_CONFIG database void your Microsoft support agreement.

Prerequisites for Refresh

• The account you use to do the refresh needs to be a Deployment Administrator and have admin permissions on the application server and the SQL server. 
• It also needs sysadmin rights in SQL and needs to be the same account that runs the "CRMDeploymentServiceAppPool" in IIS. 
• For Multi-Tier Installs (dedicated App server and SQL Server) Install below files on the Application server:
• 1.Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
• 2.Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
• 3.Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)
• UserMapping.xml file example. This is needed if you are mapping a Production install account to your Dev environment. We use separate accounts for our PROD, DEV, and QA environments. See below for sample file.

Refresh Script 

The below script we wrote after doing a pretty good amount of research and not being able to find all the information we need in one location. Please use this with care we are not responsible for your use\misuse. Open PowerShell as Administrator. I suggest saving the below file as a .ps1 and then executing it from PowerShell. 

Read More