Exchange 2007 – Tracking and Analysing Large Mailbox Move Reports – using the ExchangeMoveMailboxTaskViewer Tool…
Sorry folks, it has been a while since I last posted, it has been a very busy period for me; not just with rebuilding a Data Centre (which is now completed), but I have also been working on a few very large Exchange 2003 to Exchange 2007 migrations (see an up and coming post) – which the latter (the Exchange migrations) brings me onto the subject of this post.
You might be thinking here – “What is the ExchangeMoveMailboxTaskViewer” – never heard of it! – and indeed you would be right – as until recently it didn’t exist.
As mentioned above I have been moving thousands of mailboxes between servers over the last month – which has produced a rather large amount of XML logs files which contain the detail of each move.
Now as many of you will already know – when you perform a task either from the Exchange Management Console or indeed the Exchange Management Shell such as a Move-Mailbox an XML summary log file is produced (there is also a text based log – but this I might cover in a later article) – typically both are located in “C:\Program Files\Microsoft\Exchange Server\Logging\MigrationLogs\” – but if you are using the move-Mailbox cmdlet you can redirect the XML log file to another location (as I have been doing) using the –ReportFile switch.
Now with thousands of mailboxes involved in the the move operations; you might expect I have automated the process from the command line via a script which to runs overnight. This is all well and good – until it comes to the point where I have to analyse the logs to check to see which mailboxes failed to move the following day. Having an XML summary log which is over 2MB in size with 19294 entries (XML elements) within it to go through (even with Search) is no fun whatsoever.
So I started thinking that there must be an easier way to do this; so I started experimenting with Powershell as I knew that it had native support for parsing XML elements.
My first attempts worked very well where I was able to get the content of the Move XML files into a HTML page – but, I still ended up with a rather large HTML document which I still had to search through – so I started thinking about getting the migration data into a Database.
Of course getting Powershell to dump the data into a SQL database was not that hard considering that I had already worked out the Powershell structure to parse the files to HTML – but the trick was how could I report on this data quickly.
If was at this point the ExchangeMoveMailboxTaskViewer was born. I wanted an Application which could connect to my database and filter the migration data – showing me only what I needed to see – so out came my copy of Visual Studio and I started writing.
I ended up with an application which, when combined with the Powershell script that I had written and SQL Server gave me the results that I needed – and I would like to share it all with you (for those of you whom may need it).
Downloading the tool:
The tool is available from the following location:
Installing the Tool:
The ExchangeMoveMailbox Task Viewer can be run from either a local workstation or your Exchange 2007 Server – the fundamental prerequisite is that somewhere on your network you have a SQL 2005 / 8 installation which contains the MoveMailbox database (the configuration of which is explained below).
The recommended configuration is to install the tool locally on your personal / management machine (with a copy of SQL Server Express and Management Studio Express) and copy down the logs XML locally.
- Microsoft SQL Server Express Edition – 2005 or 2008
- Microsoft SQL Server Management Studio Express 2005 or 2008
- Windows Powershell 1.0 or Powershell 2.0 (Windows 7)
- .NET Framework 2.0
- Exchange 2007 Move Mailbox Log Files
For the purposes of this article I will assume that you already have SQL Server installed in one form or another either on your local machine or on a remote server – therefore I will not go through the SQL server installation process.
Download the SFX package above to your local workstation and extract the files to a location of your choice.
When you have done this you should see the following folder structure:
SQL – Folder Containing the Database Construction Scripts
PowerShell – Folder Containing the Powershell XML Parser
ExchangeMoveMailboxTaskViewer.exe – the Reporting Application
Copy the SQL folder to your SQL Server (if the SQL server is your local machine your can skip this part) – open the SQL Management Studio version that you have an then connect to your Database Engine instance – see below:
From the file menu go to: [ File –> Open –> File ] and then navigate to the location where you stored the scripts.
Open the script entitled “CreateDatabase-Step1.sql” – in the script window locate the lines which look like the following:
CREATE DATABASE [MoveMailbox] ON PRIMARY ( NAME = N'MoveMailbox', FILENAME = N'D:\ExchangeMigration\MoveMailbox.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'MoveMailbox_log', FILENAME = N'D:\ExchangeMigration\MoveMailbox_log.ldf' , SIZE = 3136KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
You will need to change the values which read:
D:\ExchangeMigration\MoveMailbox.mdf – this is the location of the physical database file D:\ExchangeMigration\MoveMailbox_log.ldf – this is the location of the physical transaction log
To suitable locations on your server / workstation.
When you have done this – click on the “Execute” button which is located at the top of the Management Studio Window – you should see the following message in the status output Window:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
When you have seen the above – go back to the [ File –> Open –> File ] menu and then choose the “CreateLogin-Step2.sql” file – when this has loaded just click on the “Execute” button, again you should see the following message in the status window:
Command(s) completed successfully.
Repeat the above for both the following Database Scripts:
You should (after a refresh) see the following in both the “Object Explorer –> Databases and Security –> Logins” Trees:
The MoveMailbox and mvSA Database and Accounts are now ready – if you expand the database node you will also see two tables within the database – these are used for the session data and the mailbox moves data.
In order for you to have any data to query the program relies on a Powershell based agent script – which essentially munches through the XML log files, orders the data and then places it within the SQL Database (that you have just configured).
For the script to Execute on your local machine you will need to ensure that the Execution Policy settings are set to “Remote Signed” this is accomplished by opening a Powershell command window and typing in the following command:
You will then be able to use the script.
Populating the Database:
As mentioned above the database is populated with reformed data from the move-Mailbox XML logs – this is performed by the “ReadExchangeLogsToDatabase.ps1” script which is located in the Powershell folder in the SFX extraction location that you performed when you downloaded the program.
It is at this point that I should point out that there is a function in the Powershell code which is used to display a Windows open dialog box – this was based on code borrowed from Hugo Peeters http://www.peetersonline.nl/index.php/powershell/powershell-open-file-dialog-box/ – whom I send my thanks to for writing an excellent function.
In order to use the script open a Powershell Window and navigate to the scripts location and type in the following command “.\ReadExchangeLogsToDatabase.ps1” – see below:
When you execute the script you will be presented with a standard Windows Open Dialog box – from here navigate to the location where you have your Exchange Mailbox Move XML reports, choose a report and then click on the “OK” button – see below;
When you have clicked on the OK button you will be taken back to the Powershell console which will begin processing the file – when completed you should see something like the following:
The screen above may differ depending on the amount of records which are in the XML file – you can of course run the script again the add in more XML files – but it would be prudent not the add the same XML file twice!
Using ExchangeMoveMailbox Task Viewer:
From the folder where you extracted the SFX archive – double click on the ExchangeMoveMailboxTaskViewer.exe program which will present you with the following screen:
The screen is split out into the following areas:
- SQL Database Settings – if you have not changed the default user ID, Password and Database name and are using a local SQL express instance then you should not need to change these settings, however if you are using a remote SQL server you can change the values here.
- Interrogation – this contains the Session Window (top) and the Session Data Window (bottom) – each XML file that you have imported into the database represents a session – this will be displayed in the top most Window – when you choose a session to view the bottom most window will show you the data – See below:
In order to open a Session you need to ensure that the database settings are correct – then click on the “Open Session” button located in the “SQL Database Settings” area – this will populate the Session Window with all of the sessions in the Database.
To view the Session Data – double click on the desired SessionID (DO NOT Click on any other field) or choose the SessionID and click on the “Open Selected Session” button from the Session Details window.
The details will then be populated (see above).
When you have your Data Populated you can perform the following tasks:
- Show all mailbox moves which reported an error
- Show all sessions which did not complete with a normal exit code of “0” (e.g. completed in error)
- Show details for a particular Mailbox
All of the above are available from the “Filter Options” section – filters can only be applied one at a time – e.g. you can only view Exit codes, but not Exit codes and Warnings.
To apply a filter – choose or type what you would like to view and click on the button next to the filter option.
The following is an example of the screen when the exit code filter is set:
To remove a filter – click on the “Reset Filters” button.
You can export the full session information, session data information and filtered session information to CSV should you wish to by clicking on the relevant Export buttons.
I hope that you find the tool as useful as I have – and indeed if you have any feedback / comments please feel free to pass them on – I am certain that there are one or two bugs in the application which need looking at.