Migrator Pro for Exchange 20.11.1 - User Guide

Appendix D:  Migration Processing Scripts

The Migration Processing Scripts include these MCP scripts:

• Confirm-ScheduleMigration.ps1

• Confirm-BeginMigration.ps1

• Confirm-EndMigration.ps1

Enabling Migration Customization Scripts

Perform the following steps to enable the migration scripts to be called from Migrator Pro for Exchange.

1. Click Settings in the main toolbar.

2. Click the Scripts tab. The script settings are displayed.

3. Check Enable External Scripts.

4. Set the Scripts Folder location. For example, c:\contoso\scripts\ps\

5. Enter the script file name for the Migration Script you want to be called.  For example: In the End Migration Script field, enter Confirm-EndMigration.ps1

Failures designed to halt the migration can be expressed as thrown exceptions or as calls to Write-Error in the confirmation scripts. Both the Schedule and Begin confirmations can halt the migration by failing in this manner. The End confirmation cannot cause the migration to fail if the mailbox is already complete, but it can cause a warning to be reported. Because the confirmation script runs in a non UI environment, calls in PowerShell such as Write-Host will cause faults and should not be used. Write-Error, Write-Warning, and Write-Verbose are acceptable to use, since they write to specific pipelines which are redirected in PowerShell.

Migration Customization Scripts

If enabled, the Migration Customization scripts are called by the MCP during mailbox migrations. These scripts are installed to C:\Program Files\Binary Tree\Scripts\E2E\Extension; however, they are not usually run manually from the PowerShell console.  The files in the Extension folder are not removed during an uninstall.

Confirm-ScheduleMigration.ps1

This script is called when a mailbox has been scheduled for migration, and can be used to perform any custom actions prior to submitting the mailbox to Exchange.

<add key="Confirm-ScheduleMigration" value="$null"/>

The MCP will not execute any custom scripts for the Schedule Migration step of a mailbox.  This is the default entry in the MCP configuration file.

<add key="Confirm-ScheduleMigration" value="Confirm-ScheduleMigration.ps1"/>

The MCP will execute the Confirm-ScheduleMigration.ps1 script located in the defined script folder for the Schedule Migration step of a mailbox.

<add key="Confirm-ScheduleMigration" value=""/>

The MCP will execute the default script (named Confirm-ScheduleMigration.ps1 in this example) located in the defined script folder during the Schedule Migration step of a mailbox. 

<add key="Confirm-ScheduleMigration" value="MyCmdlet"/>

The MCP will execute the specified cmdlet during the Schedule Migration step of a mailbox. 

Confirm-BeginMigration.ps1

This script is called before a mailbox is migrated, and can be used to perform any prep-work necessary prior to the submission of the mailbox to Exchange.

<add key="Confirm-BeginMigration" value="Confirm-BeginMigration.ps1"/>

The same values listed above for the Confirm-ScheduleMigration script can be used for the Confirm-BeginMigration script.

Confirm-EndMigration.ps1

This script is called after a mailbox is migrated, and can be used to perform any post-processing work necessary after the mailbox has been migrated by Exchange.  Note however that this script is only called if the migration job is Completed or CompletedWithWarning.

<add key="Confirm-EndMigration" value="Confirm-EndMigration.ps1"/>

The same values listed above for the Confirm-ScheduleMigration script can be used for the Confirm-EndMigration script.

Script Parameters

All scripts include the following parameters:

• $SourceCredential: The source forest PS credentials defined in Migrator Pro for Exchange settings

• $SourceForest: Source forest name

• $SourceServer: Source server FQDN

• $SourceDatabase: Source database DN

• $SourceMailbox: Source mailbox DN

• $TargetCredential: The target forest PS credentials defined in Migrator Pro for Exchange settings

• $TargetForest: Target forest name

• $TargetServer: Target server FQDN

• $TargetDatabase: Target database DN

• $TargetMailbox: Target mailbox DN (not mandatory for the Confirm-BeginMigration.ps1 script)

• $Status: Current job status string

• $ControllingForest: optional, not used

 

Appendix E:  Migrator Pro for Exchange Mailbox Job Importer

The Binary Tree Migrator Pro for Exchange Mailbox Job Importer utility is installed as part of Migrator Pro for Exchange.  The Binary Tree Migrator Pro for Exchange Job Importer allows you import a CSV schedule file of mailbox migration jobs from a command line. A CSV file can also be used to update existing mailbox migration job values. A CSV cancel file can be used to cancel mailbox migration jobs.

Migration jobs that are in AutoSuspended or BlackoutSuspended state (only if Suspend When Ready to Complete flag was set) can be modified. For these jobs, only the Do Not Complete Before Date, Do Not Start Before Date, Migration Group, and Priority fields will be updated.

The Auto license 0365 users and Default Usage Location settings in the Tenant Forest General Settings are used by the Migrator Pro for Exchange Job Importer when scheduling mailbox migration to Office 365.

CSV Schedule File Format

The CSV schedule file of mailbox migration jobs must meet the following requirements:

• The file should not contain headers.

• No values can be blank except for the Do Not Start Before Date, Do Not Complete Before Date, Communication Language, Migration Group and Blackout Profile.

• All columns must be included.

The CSV schedule file must include these columns:

Col	Field	Example	Req'd?	Description
1	User Principal Name (UPN)	eprouser@contoso.com	Yes	User Principal Name of desired mailbox.
	Matched Against: CMTEUP_PersonADData.userPrincipalName
2	Target Forest Name	contoso.com	Yes	Name of target forest.
	Matched Against: CMTEUP_Forests.ForestName
3	Target delivery domain	root1.contoso.com	Yes	Name of target domain for mailbox.
	Saved To: CMTEUP_Job.param_TargetDeliveryDomain
4	Target Mailbox Database Name	primarymailbox1	No	Name of target mailbox database.  Leave blank if target is an O365 forest.
	Matched Against: CMTEUP_ServerDatabase.ServerDatabaseName
5	Do not start before date	10/15/2016 09:00AM	No	The time to start the migration, if not immediately.  If specified, must be a valid date/time string.
	Saved To: CMTEUP_Job.DoNotStartBefore
6	Suspend when ready to complete	"true" or "false"	Yes	Whether migration should be suspended before cutover.
	Saved To: CMTEUP_Job.param_SuspendWhenReadyToComplete
7	Bad Item Limit	10	No	Number of bad mailbox items to allow before migration is failed. Must be a valid number >= 0.
	Saved To: CMTEUP_Job.param_BadItemLimit
8	Large Item Limit	75	No	Number of large mailbox items to allow before migration is failed. Must be a valid number >= 0.
	Saved To: CMTEUP_Job.param_LargeItemLimit
9	Ignore Rule Limit Errors	"true" or "false"	Yes	Whether mailbox rules should be moved to target.
	Saved To: CMTEUP_Job.param_IgnoreRuleLimitErrors
10	Do not complete before date	10/15/2016 09:00AM	No	When to complete the migration. If specified, must be a valid date/time string AND Suspend when ready to complete must be "true."
	Saved To: CMTEUP_Job.param_SuspendWhenReadyToComplete
11	Communication Language	0 or "en" or "English"	No	Number or name of a valid culture. Must match a culture using CMTEUP_Culture. CultureName, CMTEUP_Cuture.Culture-Display Name or CMTEUP_Culture.Cul- tureID.  Also, when specified, CMTEUP_UserMailbox.CultureIDLocked=1 is set to prevent updates to this value from the Migrator Pro for Exchange rationalization process.
	Saved To: CMTEUP_UserMailbox.CultureID
12	Migration Group	MyMigrationGroup1	No	Name of a migration group.  Will either match an existing group on CMTEUP_MigrationGroups.MigrationGroupName, or create a new group if no match can be found.
	Saved To: CMTEUP_UserMailbox.MigrationGroupID
13	Priority	5	No	Relative priority used to schedule the mailbox migration.  Must be a number between 1 and 100.  If not specified, a default priority of 10 is used. This setting does not apply to migrations to Office 365.
	Saved To: CMTEUP_UserMailbox.Priority
14	Blackout Profile	MyBlackoutProfile1	No	The name of a Blackout profile to use.  This will either match an existing profile on CMTEUP_Blackout Profiles.BlackoutProfileName or create a new profile if no match can be found.  If not specified, the default blackout profile will be assigned.
	Saved To: CMTEUP_UserMailbox.BlackoutProfileID

An example of a valid row in the CSV schedule file looks like:

Test03102@dom1.epro.dom,targetForest.dom,TargetDeliveryDomain.dom,Mailbox Database 12345678,03-25-2014 04:12 pm,true,0,0,true,04-01-2014 05:52 pm,en-US,Group1,10,Profile 1

CSV Cancel File Format

The CSV cancel file used to cancel mailbox migration jobs must meet the following requirements:

• The file should not contain headers.

• The file must include column 1: User Principal Name (UPN).

An example of a valid row in the CSV cancel file looks like:

Test03102@dom1.epro.dom

To convert an Excel document to a CSV text file:

1. Open your Excel file.

2. Remove any existing header rows.

3. Format all cells as text by selecting all columns and then right-click > Format Cells > Text.

4. Save the document as a CSV.

Importing the CSV Schedule File of Mailbox Migration Jobs

To schedule or reschedule mailbox migration jobs by importing the CSV schedule file in the command prompt:

1. Open a command prompt.

2. Run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /s [CSV schedule file].

3. The schedule file is validated and saved to the database. If an error is encountered, no jobs are saved.

To review and correct import errors:

1. Errors will be displayed below the import command in the command prompt window.

2. Optionally, you can run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter [CSV schedule file] > OutputReport.txt to generate a text file containing the errors.

3. Correct the errors in the CSV schedule file and attempt the import again. Optionally, you can validate the CSV schedule file without importing (see steps below).

To validate the CSV schedule file without importing:

1. In the command prompt, run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /v [CSV schedule file]

2. The number of valid rows and rows with errors are displayed. The CSV schedule file is not imported.

To view the Help file:

1. In the command prompt, run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /? or /help

2. The help message is displayed.

Cancelling Mailbox Migration Jobs using the CSV Cancel File

Mailbox migrations jobs can be cancelled using a CSV cancel file that contains a list of UPNs associated with migration jobs.

To cancel mailbox migration jobs by importing the CSV cancel file in the command prompt:

1. Open a command prompt.

2. Run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /c [CSV cancel file].

3. The migration jobs for the UPNs are cancelled.

Usage Summary

BinaryTree.E2E.JobImporter [[/s <schedule file>] | [/v <schedule file>] | [/c <cancel file>]] [/?] or [/help]

/s <file> : Schedule (or re-schedule) mailboxes for migration. See JobImporter-ReadMe.txt for details on the format of <schedule file>.

/v <file> : Validate the contents of <schedule file>. Any errors found are reported, but no migration jobs are scheduled.

/c <file> : Cancel mailbox migrations for the list of UPNS found in <cancel file>.

/? or /help : Display this help message.

Appendix F:  Migrator Pro for Exchange Public Folder Job Importer

The Binary Tree Migrator Pro for Exchange Public Folder Job Importer utility is installed as part of Migrator Pro for Exchange.  The utility will create a job for each row of the input file. If a job already exists with the same source server name and folder path and same target server name and folder path, the information from the input file will update the existing job instead of creating a new one.

If the user elects to add subfolders for a given row, only subfolders that do not have a job will be added. The utility will not update subfolder job settings based on a parent job.

When the utility creates a job and the user does not specify a value for an optional field, the utility will use the same default as the Add folders to migration queue dialog.

CSV File Format (Scheduling New Jobs and Rescheduling Existing Jobs)

Col	Field	Example	Req'd?	Description
1	Source folder path	\TestFolder	Yes	Path of job’s source folder.
	Matched Against: CMTEUP_PublicFolder.FolderPath
2	Source folder server name	ExchgPro-EX10-1	Yes	Server name where job’s source folder resides.
	Matched Against: CMTEUP_Server.ServerName
3	Target folder parent path	\	No	Path of target’s parent folder.
	Matched Against: CMTEUP_PublicFolder.FolderPath
4	Target folder server name	QA-ExchgPro-EX10-1	Yes	Server name where job’s target folder resides.
	Matched Against: CMTEUP_Server.ServerName
5	Target public folder mailbox UPN	pfmbx@qa-epro.dom	Only for Exchg 2013+	UPN of mailbox where target public folder will reside.
	Matched Against: CMTEUP_PersonADData.userPrincipalName
6	Add or Modify subfolder jobs	True / False	No	Specifies if importer should add or modify jobs for subfolders. Defaults to “true.”
7	Sync Seconds	30	No	Sets interval for sync job. If value is 0 or omitted, job will run only once.
	Saved To: CMTEUP_PublicFolderJob.SyncSeconds
8	Scheduled Start Date	10-25-2016 4:12 pm	No	Scheduled start date for the job. If omitted, then defaults to current time.
	Saved To: CMTEUP_PublicFolderJob.ScheduledStart

Example valid lines:

\TestFolder,ExchgPro-EX10-1,\,QA-ExchgPro-EX10-1,,true,0,3-25-2015 04:12 pm

\TestFolder,ExchgPro-EX10-1,\,QA-ExchgPro-EX13-1,pfmailbox@qa-epro.dom,true,0,3-25-2015 04:12 pm

 

If the folder name has a quote character (") at the beginning and/or end, you must place quotes (") around the entire folder path. Example: "\Root\Directory\"Example Folder Name"" If the folder name has a quote character (") in the middle of the name, do not place quotes (") around the entire folder path. Example: \Root\Directory\Example "Folder" Name

CSV File Format (Cancelling existing jobs)

Col	Field	Example	Req'd?	Description
1	Source folder path	\TestFolder	Yes	Path of job’s source folder.
	Matched Against: CMTEUP_PublicFolder.FolderPath
2	Source folder server name	ExchgPro-EX10-1	Yes	Name of server where job’s source folder resides.
	Matched Against: CMTEUP_Server.ServerName
3	Target folder parent path	\	No	Path of target’s parent folder.
	Matched Against: CMTEUP_PublicFolder.FolderPath
4	Target folder server name	QA-ExchgPro-EX10-1	Yes	Name of server where job’s target folder resides.
	Matched Against: CMTEUP_Server.ServerName
5	Target public folder mailbox UPN	pfmailbox@ExchgPro.dom	Only for Exchg 2013+	UPN of mailbox where target public folder will reside.
	Matched Against: CMTEUP_PersonADData.userPrincipalNam
6	Cancel subfolder jobs	True / False	No	Specifies if importer should cancel jobs for subfolders. Defaults to “true.”

Example valid lines:

\TestFolder,ExchgPro-EX10-1,\,QA-ExchgPro-EX10-1,,true

\TestFolder,ExchgPro-EX10-1,\,QA-ExchgPro-EX13-1,pfmailbox@ExchgPro.dom,true

Command Line

Available command line switches:

BinaryTree.E2E.PFJobImporter [[/s <schedule file>] | [/v <schedule file>] | [/c <cancel file>]] [/? or /help]

• /s <file>: Schedule (or re-schedule) public folders for migration.

• /v <file>: Validate the contents of <schedule file>. Any errors found are reported, but no migration jobs are scheduled.

• /c <file>: Cancel public folder migrations for the list of folders found in <cancel file>.

• /? or /help: Display this help message.

Example command line for scheduling (or re-scheduling) a set of public folder jobs:

BinaryTree.E2E.PFJobImporter.exe /s jobfile.csv

Sample output:

Example command line for cancelling a set of public folder jobs:

BinaryTree.E2E.PFJobImporter.exe /c jobfile.csv

Sample output:

Example command line for validating a set of public folder jobs:

BinaryTree.E2E.PFJobImporter.exe /v jobfile.csv

Sample output:

Appendix G:  Public Folder Analysis and Job Administration

This appendix provides scripts and information for managing public folder migrations and querying collected data about public folders in Migrator Pro for Exchange.

To run the SQL scripts in this file, open SQL Server Management Studio and connect to your Migrator Pro for Exchange database. Right-click on the Migrator Pro for Exchange database and select New Query to open the New Query window. Then paste the SQL script into the query window, update the parameter values (if any), and execute the SQL script.

Public Folder Analysis Scripts

For many of the public folder analysis scripts to function correctly, you must perform a public folder content analysis on a forest at least once.

Below is a list of SQL scripts that can be used to query data collected about public folders. The SQL scripts can be downloaded from the FTP folder where the GA Migrator Pro for Exchange install files and documentation are located.   Download the file SQL Queries.zip and extract the files to the SQL server being used for Migrator Pro for Exchange public folder migrations.  The following scripts are also available in the file PF Analysis Queries.sql.  You can either open this file in SQL Server Management Studio or copy and paste the query text below into a query in SQL.  Some of these queries need to be modified before executing.  A good understanding of SQL is recommended before proceeding.

Find the 10 largest public folders by folder size

To find the 10 largest public folders by folder size, run this SQL command:

SELECT TOP 10

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

f.[MaxPFSizeBytes]

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

ORDER BY f.MaxPFSizeBytes DESC;

Find the 10 largest public folders by item count

To find the 10 largest public folders by item count, run this SQL command:

SELECT TOP 10

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

i.ItemCount

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

INNER JOIN (SELECT PublicFolderID, COUNT(*) AS ItemCount FROM CMTEUP_PublicFolderItem

GROUP BY PublicFolderID) i ON f.PublicFolderID = i.PublicFolderID

ORDER BY i.ItemCount DESC;

Find public folders with more than x items

To find all the public folders with more than x items, run this SQL command (change "10000" to your own limit number):

DECLARE @numItems INT

SET @numItems = 10000

SELECT f.PublicFolderID,

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

i.ItemCount

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

INNER JOIN (SELECT PublicFolderID, COUNT(*) AS ItemCount

FROM CMTEUP_PublicFolderItem

GROUP BY PublicFolderID) i ON f.PublicFolderID = i.PublicFolderID

WHERE i.ItemCount > @numItems

ORDER BY ItemCount DESC;

Find public folders where content has not changed in the last x days

To find all the public folders where content has not changed in the last x days, run this SQL command (change "90" to your own value for "x"):

DECLARE @daysSinceLastContentChange INT

SET @daysSinceLastContentChange = 90

SELECT

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

f.[ContentChangeDate]

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

WHERE f.ContentChangeDate <= GETUTCDATE()-@daysSinceLastContentChange;

Find public folders where attributes have not changed in the last x days

To find all the public folders where attributes (e.g., permissions) have not changed in the last x days, run this SQL command (change "90" to your own value for "x"):

DECLARE @daysSinceLastAttributeChange INT

SET @daysSinceLastAttributeChange = 90

SELECT

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

f.[AttributeChangeDate]

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

WHERE f.ContentChangeDate <= GETUTCDATE()-@daysSinceLastAttributeChange;

Find public folders where a specific user has any permission

To find all the public folders where a specific user has any permission, run this SQL command (change “'2013Throughput@ExchgPro.dom” to the user’s UPN):

DECLARE @userAnyPermission NVARCHAR(2000)

SET @userAnyPermission = '2013Throughput@ExchgPro.dom'

SELECT

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

p.AccessRight

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

INNER JOIN [dbo].[CMTEUP_PublicFolderPermission] p ON f.PublicFolderID = p.PublicFolderID

WHERE p.TrusteeIdentity = @userAnyPermission;

Find public folders where a specific user has a specific permission

To find all the public folders where a specific user has a specific permission or role, run this SQL command (change “2013Throughput@ExchgPro.dom” and “Contributor” to the user's UPN and the permission or role you want to find):

DECLARE @user NVARCHAR(2000)

SET @user = '2013Throughput@ExchgPro.dom'

DECLARE @permission NVARCHAR(100)

SET @permission = 'Contributor'

SELECT

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

p.AccessRight

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

INNER JOIN [dbo].[CMTEUP_PublicFolderPermission] p ON f.PublicFolderID = p.PublicFolderID

WHERE p.TrusteeIdentity = @user AND p.AccessRight = @permission;

Find Public Folders by email address

To retrieve a list of public folders by Email Address, run the following SQL command.

SELECT TOP 10

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

f.[Email]

f.[MaxPFSizeBytes]

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

ORDER BY f.MaxPFSizeBytes DESC;

Find Public Folders that have been processed by Content Analysis

To see a list of public folders that have been processed by Content Analysis, run the following SQL command.

Select f.folderpath,f.email,f.targetaddress, j.starttime as ReportingJobStarted, j.endtime as ReportingJobFinished

FROM cmteup_publicfolderreportjob j

INNER JOIN cmteup_publicfolder f

ON j.publicfolderid = f.publicfolderid

Find the 10 largest public folder items by item size

To the find the 10 largest public folder items by item size, run this SQL command:

SELECT TOP 10

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

i.ItemName,

i.ItemType,

i.ItemSize AS ItemSizeInBytes

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

INNER JOIN [dbo].[CMTEUP_PublicFolderItem] i ON f.PublicFolderID = i.PublicFolderID

ORDER BY i.ItemSize DESC;

Find public folder items larger than x MB

To find all the public folder items larger than x MB, run this SQL command (change "10" to the limit size, in MB):

DECLARE @sizeInMB INT

SET @sizeInMB = 10

SELECT

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

i.ItemName,

i.ItemType,

CONVERT(decimal(10, 3), (i.ItemSize / 1048576.0)) AS ItemSizeInMB

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

INNER JOIN [dbo].[CMTEUP_PublicFolderItem] i ON f.PublicFolderID = i.PublicFolderID

WHERE (i.ItemSize / 1048576.0) > @sizeInMB

ORDER BY i.ItemSize DESC;

Find total number of public folder items by item type

To find the total number of public folder items by item type, run this SQL command:

SELECT

[ItemType],

COUNT(*) As NumberOfItems

FROM [dbo].CMTEUP_PublicFolderItem

GROUP BY [ItemType]

ORDER BY [ItemType];

Find total number of public folder item attachments by type

To find the total number of public folder item attachments by type (extension), run this SQL command:

SELECT

[AttachmentExtension],

COUNT(*) AS NumberOfAttachments

FROM [dbo].CMTEUP_PublicFolderAttachment

GROUP BY [AttachmentExtension]

ORDER BY [AttachmentExtension];

Find total size of public folder item attachments by type

To find the total size of public folder item attachments by type (extension), run this SQL command:

SELECT

[AttachmentExtension],

SUM(AttachmentSize) AS TotalSizeInBytes

FROM [dbo].CMTEUP_PublicFolderAttachment

GROUP BY [AttachmentExtension]

ORDER BY [AttachmentExtension];

Find public folder item attachments larger than x MB

To find all the public folder item attachments larger than x MB, run this SQL command (change "10" to your limit size, in MB):

DECLARE @attachmentSizeInMB INT

SET @attachmentSizeInMB = 10

SELECT

s.[ServerName],

f.[Name],

f.[EntryID],

f.[FolderPath],

a.AttachmentName,

a.AttachmentExtension,

CONVERT(decimal(10, 3),(a.AttachmentSize / 1048576.0)) AS ItemSizeInMB

FROM [dbo].[CMTEUP_PublicFolder] f

INNER JOIN [dbo].[CMTEUP_Server] s ON f.ServerID = s.ServerID

INNER JOIN [dbo].[CMTEUP_PublicFolderAttachment] a ON f.PublicFolderID = a.PublicFolderID

WHERE (a.AttachmentSize / 1048576.0) > @attachmentSizeInMB

ORDER BY a.AttachmentSize DESC;

Public Folder Job Administration Scripts

These PowerShell and SQL scripts can be used to manage public folder migrations.  The SQL scripts can be downloaded from the FTP folder where the Migrator Pro for Exchange install files and documentation are located.   Download the file SQL Queries.zip and extract the files to the SQL server being used for Migrator Pro for Exchange public folder migrations.  The following scripts are also available in the file PF Job Administration Queries.sql.  You can either open this file in SQL Server Management Studio or copy and paste the query text below into a query in SQL.  Some of these queries must be modified before executing.  A good understanding of SQL is recommended before proceeding.

Count folders in Exchange

To count the total number of folders in Exchange for a subtree (e.g. \RootFolder1), run this PowerShell command from the Exchange server (change "\RootFolder1" to your root folder):

Get-PublicFolder –Recurse "\RootFolder1" | Select Name | Measure-Object

Count folders in Migrator Pro for Exchange

To count the number of folders in Migrator Pro for Exchange for a subtree (e.g. “\RootFolder1”), run the following SQL command (change "\RootFolder1" to the desired root folder, and change "ServerID = 1" to the ID of the desired source server as found in CMTEUP_Server):

SELECT COUNT(*) from CMTEUP_PublicFolder

WHERE (FolderPath like '\RootFolders\%' or FolderPath = '\RootFolders')

AND ServerID = 1

Find folder in Exchange

To look up a folder subtree in Exchange, run the following PowerShell command from the Exchange server (change "\RootFolder1" to the desired root folder, and change "Server1" to the desired Exchange Public Folder server name):

Get-PublicFolder –Recurse "\RootFolder1" –Server "Server1"

Compare jobs and folders

To compare the number of folders in a subtree to the number of jobs created, run the following SQL command (change "\2007Folders" to the desired root folder):

SELECT count(1)

FROM CMTEUP_PublicFolder

WHERE FolderPath like '\2007Folders%'

 

SELECT count(1)

FROM CMTEUP_PublicFolder fs

inner join CMTEUP_PublicFolderjob jt on jt.SourcePublicFolderID = fs.PublicFolderID

WHERE fs.FolderPath like '\2007Folders%'

Find all folders in source forest without Jobs

Change “airbus.corp” to the desired forest name:

SELECT f.FolderPath, *

FROM CMTEUP_PublicFolder f

inner join CMTEUP_Server s on s.ServerID = f.ServerID

inner join CMTEUP_Site i on i.SiteID = s.SiteID

inner join CMTEUP_Forest o on o.ForestID = i.ForestID

where o.ForestName = 'airbus.corp'

and f.FolderPath not in

(

SELECT DISTINCT(f.FolderPath)

FROM CMTEUP_PublicFolder f

inner join CMTEUP_PublicFolderJob j on j.SourcePublicFolderID = f.PublicFolderID

inner join CMTEUP_Server s on s.ServerID = f.ServerID

inner join CMTEUP_Site i on i.SiteID = s.SiteID

inner join CMTEUP_Forest o on o.ForestID = i.ForestID

where o.ForestName = 'airbus.corp'

)

Check folder provisioning progress

To check the folder provisioning progress for all jobs, run the following SQL command.  See Appendix B for more information on job states.

SELECT count(1) as JobCount, s.JobStatus, j.SyncCount

FROM CMTEUP_PublicFolderJob j

inner join CMTEUP_JobStatus s on j.JobStatusID = s.JobStatusID

WHERE j.SyncCount = 0

GROUP BY s.JobStatus, SyncCount

Sample Output

... which tells us that 8 jobs have been scheduled, but are not yet started, and 1 folder sync job has started (folder creation and attribute sync).

Find empty folders

To find folders (with or without jobs) that don’t have any items, run the following SQL command.

SELECT f.FolderPath, f.ItemCount, f.ByteCount, f.PublicFolderID

from CMTEUP_PublicFolder f

where f.ItemCount = 0

order by f.ItemCount desc

Sample Output

Find empty folders with jobs

To find folders (with jobs) that don’t have any items, run the following SQL command.

SELECT f.FolderPath, s.JobStatus, f.ItemCount, f.ByteCount, f.PublicFolderID, j.PublicFolderJobID

from CMTEUP_PublicFolderJob j

inner join CMTEUP_JobStatus s on j.JobStatusID = s.JobStatusID

inner join CMTEUP_PublicFolder f on j.SourcePublicFolderID = f.PublicFolderID

where f.ItemCount = 0

order by f.ItemCount desc

Sample Output

Find Large Folders

To find folders (with or without jobs) that contain a large number of items, run the following SQL command.

-- The max number of folder items

declare @maxItems int = 500

 

SELECT f.FolderPath, f.ItemCount, f.ByteCount, f.PublicFolderID

from CMTEUP_PublicFolder f

where f.ItemCount > @maxItems

order by f.ItemCount desc

Sample Output

Find large folders with jobs

To find folders (with jobs) that contain a large number of items, run this SQL command:

-- The max number of folder items

declare @maxItems int = 500

 

SELECT f.FolderPath, s.JobStatus, f.ItemCount, f.ByteCount, f.PublicFolderID, j.PublicFolderJobID

from CMTEUP_PublicFolderJob j

inner join CMTEUP_JobStatus s on j.JobStatusID = s.JobStatusID

inner join CMTEUP_PublicFolder f on j.SourcePublicFolderID = f.PublicFolderID

where f.ItemCount > @maxItems

order by f.ItemCount desc

Sample Output

Reschedule Large Folders

To postpone the processing of large folders, run the following SQL command.  

NOTE: if this is run for jobs that have not yet finished provisioning, the provisioning process will also be postponed.  See Check Folder Provisioning Progress to determine the state of the desired folders (change “2015-06-22” to the rescheduled date and time):

-- The max number of folder items

declare @maxItems int = 500

 

-- The desired reschedule date

declare @rescheduleDate datetime2(7) = '2015-06-22'

 

update CMTEUP_PublicFolderJob

set ScheduledStart = @rescheduleDate

where PublicFolderJobID in

(

  SELECT j.PublicFolderJobID

  from CMTEUP_PublicFolderJob j

  inner join CMTEUP_PublicFolder f on j.SourcePublicFolderID = f.PublicFolderID

  where f.ItemCount > @maxItems

)

Find Source Folders with Jobs Not Yet Processed by Change Detection

To find source folders not yet processed by change detection, run the following SQL command:

select j.PublicFolderJobID, j.SourcePublicFolderID, sfs.ServerName AS SourceServerName, sf.Name AS SourceName, sf.FolderPath AS SourceFolderPath, j.LastChangeDetectionDate, sf.ContentChangeDate AS SourceContentChangeDate

FROM CMTEUP_PublicFolderJob j

INNER JOIN CMTEUP_PublicFolder sf

ON j.SourcePublicFolderID = sf.PublicFolderID

INNER JOIN CMTEUP_Server sfs

ON j.SourceServerID = sfs.ServerID

WHERE j.LastChangeDetectionDate IS NULL

Sample Output

Find Target Folders with Jobs Not Yet Processed by Change Detection

To find target folders not yet processed by change detection, run the following SQL command.

Change detection only checks target folders if bi-directional syncing is enabled.

-- Change detection only checks target folders if bi-directional syncing is enabled

declare @bidi INT

set @bidi = (select PFCoexistenceMode from CMTEUP_Settings)

select j.PublicFolderJobID, j.TargetPublicFolderID, tfs.ServerName AS TargetServerName, tf.Name AS TargetName, tf.FolderPath AS TargetFolderPath, j.LastChangeDetectionDate, tf.ContentChangeDate AS TargetContentChangeDate

from CMTEUP_PublicFolderJob j

INNER JOIN CMTEUP_PublicFolder tf

ON j.TargetPublicFolderID = tf.PublicFolderID

INNER JOIN CMTEUP_Server tfs

ON j.TargetServerID = tfs.ServerID

where j.LastChangeDetectionDate IS NULL AND @bidi = 1

Table Definitions

Below is a description of the noteworthy columns for the Public Folder and Public Folder Job tables.

CMTEUP_PublicFolder

This table includes all Rationalized public folders from the Source and Target forests:

• PublicFolderID: Unique public folder ID

• Name: Name of the public folder

• FolderPath: Full Folder Path

• ParentFolderID/ParentPath: These two columns will reference the Parent Folder.  If this reference is out of sync, Child folder will not be visible in the UI.

• AttributeChangeDate/AttributeChanged: Indicate when a folder attribute (proxy address, permissions) has changed.

• ContentChangeDate/ContentChanged: Indicate when new contents were added to the folder.

• IsDeleted: This will be set to 1 if the folder was deleted.

CMTEUP_PublicFolderJob

This table includes all the Active public folder jobs.  See CMTEUP_PublicFolderJobArchive for all Completed public folder jobs.

• PublicFolderJobID: Unique Job ID for each job

• ScheduleStart: This column determines when the job will be submitted by MCP service.

• SourceServerID/TargetServerID: Identify the source and target Exchange Server

• SourcePublicFolderID/TargetPublicFolderID: These two columns will reference the respective public folder ID in the CMTEUP_PublicFolder table.

• BiDirectional: Bi-Directional Setting

• SyncCopy: True/false flag.

• SyncCount: Number of times a Sync Copy has completed.

• Result_ErrorMessage: Last Error message will be recorded here.

• Result_ErrorCount: Number of Errors observed.

• JobStatusID: Job Status description can be found in CMTEUP_JobStatus table.

• LastChangeDetectionDate: The last time the change detection worker processed this job.

Job States

Public Folder Job Status

Public Folder Migration Job flow

• Scheduled: Job has been scheduled, but not yet started.

• Started: Folder sync (if necessary, create folder and sync folder attributes and permissions) has started.

• Provisioned: Folder sync has completed and content sync is ready to start.

• In Progress: Folder item copy has started.

• Completed: Folder item copy is done.

Public Folder Migration Engine Folder Handling Notes

If the public folder does not contain any items, the public folder migration engine will skip the folder except when Source to Target Content Deletes are selected to be synced in the Migrator Pro for Exchange Public Folder Settings. If Source to Target Content Deletes are checked then the engine will check for deleted items in source and delete them in target accordingly.

Migrator Pro for Exchange Public Folder Migration Child Job Management

When an administrator creates a public folder migration job using the Migrator Pro for Exchange Admin Portal or the public folder Job Importer utility, Migrator Pro for Exchange will check if a job already exists for the source folder or any child of the source folder. A child folder will belong to the same forest as its parent and reside on the same server (or mailbox in the case of Exchange 2013). If a child folder with an existing migration job is detected (in the case of a drag and drop in the UI, Migrator Pro for Exchange will display a warning dialog asking the user if they want to modify the child jobs) Migrator Pro for Exchange will update the job’s target folder and target server as well as the job’s sync interval and scheduled start fields. Finally, Migrator Pro for Exchange will reset the modified job’s status to Scheduled and its sync count to zero (0).