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.
Click Settings in the main toolbar.
Click the Scripts tab. The script settings are displayed.
Check Enable External Scripts.
Set the Scripts Folder location. For example, c:\contoso\scripts\ps\
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
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:
Open your Excel file.
Remove any existing header rows.
Format all cells as text by selecting all columns and then right-click > Format Cells > Text.
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:
Open a command prompt.
Run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /s [CSV schedule file].
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:
Errors will be displayed below the import command in the command prompt window.
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.
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:
In the command prompt, run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /v [CSV schedule file]
The number of valid rows and rows with errors are displayed. The CSV schedule file is not imported.
To view the Help file:
In the command prompt, run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /? or /help
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:
Open a command prompt.
Run C:\Program Files\Binary Tree\Exchange Pro\bin>BinaryTree.E2E.JobImporter /c [CSV cancel file].
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.
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:
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).
© ALL RIGHTS RESERVED. Feedback Termini di utilizzo Privacy Cookie Preference Center