..., etc.

====== Exchange Storage Reconciliation  ======

This began as a "I'll just whip this up" type of project, with the goal of being able to report on our Exchange server storage use by group/OU/user/server/mailbox store/etc...

Intended goals are/were:

  * Database definition and creation
  * Programmatic lookup of all exchange mailboxes in the Active Directory, based on CN 
  * Programmatic calculation of mailbox storage size, based on CN - Note: Earlier I wrote "//**This is never going to happen...**//" - but after a little more research on the possibilities of WMI, it indeed has already happened.
  * Long-term: UTF-16 parse via Perl.  Some names are not correctly displayed via non-unicode csvde output, and that shouldn't be the case.  For instance, "CN=Mimi N~RDiaye", this entry results in a blank insert, which is of course a duplicate and MySQL errors out on the remaining inserts.  **This is essentially done as well, the only two names which didn't work now do thanks to conversion to UTF-8 from "UTF-16"**
  * [[exchange:reporting_project:issues|Issues]]

So it looks like a random newline in the file will give bad results?

That's basically it.

====== To-do ======

Most important next step is cleanup of code and organizing it so it's easy for someone else to use. Blech.

====== The works ======

===== Database =====

I decided on MySQL simply because it is readily available and simple.

<tab>[[exchange:reporting:database_definition|Database Definition]]
===== LDAP Queries for Exchange Mailboxes =====

All queries should be as small as possible for the simple reason that doing so will ensure script readability, better portability if a different parsing engine should be desired, and easier changes to how specific parameters are exported/imported.  More files with fewer columns, and more db insert steps, is definitely favored over a large monolithic export/import.

**[[exchange:reporting_project:query_notes|Notes on queries]]**
=== Tools ===



Csvde has been chosen as the sole lookup utility in order to keep lookup output alike. Csvde has one major advantage over ldifde or dsquery/dsget, which is single-line output.  For Csvde syntax and examples see the notes on LDAP queries above.

===== Mailbox Size calculation =====
<del>//**There is no mailbox size calculation, and there probably never will be...**//</del>  Right- this is accomplished via WMI w/VBS.
====== Process ======

Basic Outline:

**Windows**:

All LDAP lookups are performed on a Windows system, which of course must be a domain member.

Windows files are on Central, under \\c-usr\users\b\ben\ad\exchange.

**Unix**:

All data processing and storage are on a Unix system, using MySQL v5.

Parsing is performed with Perl and basic Unix shell scripting and tools.  Files are under ~/src/ad/exchange.

===== Lookup =====

AD export/lookups are performed by a set of DOS batch scripts that execute CSVDE commands and output to a lookup specific file.

The lookup process is executed via the parent batch file, run_getexchangedata.bat.  Executing that script will in turn execute each individual lookup scripts.  The lookups and their output files are:

|^Lookup Script               |^Output File            |^Data Format           |^Description |
|[[0getallexchange.bat]]||all_exchange.txt        ||DN,cn,displayName ||Name information for all Exchange users  |
|[[1getIDexchange.bat ]]      ||id_exchange.txt         ||DN,userPrincipalName ||UPN/logon ID for all Exchange users      |
|[[1.1getIDexchange.bat]]     ||samid_exchange.txt      ||DN,sAMAccountName    ||SAM ID for all Exchange users    |
|[[2getdisabledexchange.bat]] ||disabled_exchange.txt   ||DN,(null)            ||All Exchange users with disabled AD account  |
|[[3getLastLogon.bat]]        || lastlogon_exchange.txt ||DN,lastLogon         ||Last AD logon time (64-bit 100ns since 1/1/1601 format) |
|[[4getallexchangeServerMDB.bat]]||all_exchange_mdb.txt    ||DN,homeMDB           ||Home Mailbox Database for all Exchange users  |
|[[getExchangeMailboxInfo.vbs]] ||specified on command line, mailbox_sizes.txt ||LegacyDN, Size, LastLogonTime, ServerName, StorageGroupName, StoreName, TotalItems, DeletedMessageSizeExtended || Size&Server info for all Exchange users  |
|[[getBesUsers.vbs]] || std_out, should pipe to besusers.txt || legacyExchangeDN || Server is hardcoded right now, need to fix this one up ||
|[[run_getexchangedata.bat]]      ||-        ||- ||Kicks off all the other scripts  |

===== Export Mailbox Sizes =====

A Visual Basic script to export Mailbox Information is done.  It uses the Exchangev2 WMI Namespace and was cobbled together from Microsoft's sample code available [[http://msdn.microsoft.com/en-us/library/aa143732(EXCHG.65).aspx| here.]]

**[[exchange:reporting:WMI_VBscript|WMI VB Script to return mailbox data]]**
===== Transfer Data =====
File transfer assumes Central users mount on SSHd enabled system.

exchange/batch/transfer.sh initiates recursive SSH copy job of users\b\ben\exchange\* with public-key authentication.

Files are copied to exchange/txt.
===== Parse and Prepare data fields =====
Once the file transfer is complete, batch processing is initiated via runExchangeReporting.sh in the root of the exchange directory.  The job runs each of the script files in the exchange/batch directory.

**[[exchange:reporting:initial_batch_scripts|Initial Bash Script Code]]**

This process is being moved to Perl.

Script files at this point:
<code>
load_db.sh*
parse_allExchange.pl*
parse_allExchange_uni.pl*
parse_bes.pl*
parse_disabled.pl*
parse_id.pl*
parse_lastExchangeLogon.pl*
parse_lastLogon.pl*
parse_mailboxes.pl*
parse_ou.pl*
parse_quota.pl*
parse_samid.pl*
prepare_db.sh*
sort_files.pl*
transfer.sh*
unicode.pl*

</code>

Top-level script: [[runExchangeReporting.sh]]

|^Script               |^Output File            |^Description |
|[[load_db.sh]]||all_exchange.txt        ||Name information for all Exchange users  |
|[[parse_allExchange.pl]]      ||id_exchange.txt         ||UPN/logon ID for all Exchange users      |
|[[parse_allExchange_uni.pl]]     ||samid_exchange.txt      ||SAM ID for all Exchange users    |
|[[parse_bes.pl]] ||disabled_exchange.txt   ||All Exchange users with disabled AD account  |
|[[parse_disabled.pl]]        || lastlogon_exchange.txt ||Last AD logon time (64-bit 100ns since 1/1/1601 format) |
|[[parse_id.pl]]||all_exchange_mdb.txt   ||Home Mailbox Database for all Exchange users  |
|[[parse_lastExchangeLogon.pl]] ||specified on command line, mailbox_sizes.txt || Size&Server info for all Exchange users  |
|[[parse_lastLogon.pl]] || std_out, should pipe to besusers.txt || Server is hardcoded right now, need to fix this one up ||
|[[parse_mailboxes.pl]]      ||-        ||Kicks off all the other scripts  |
|[[parse_ou.pl]]      ||-        ||Kicks off all the other scripts  |
|[[parse_quota.pl]]      ||-        ||Kicks off all the other scripts  |
|[[parse_samid.pl]]      ||-        ||Kicks off all the other scripts  |
|[[prepare_db.sh]]      ||-        ||Kicks off all the other scripts  |
|[[sort_files.pl]]      ||-        ||Kicks off all the other scripts  |
|[[transfer.sh]]      ||-        ||Kicks off all the other scripts  |
|[[unicode.pl]]      ||-        ||Kicks off all the other scripts  |



===== Database Refresh =====

Database refresh consists of two scripts:

**prepare_db.sh**: This script drops the exchange_info table, and then re-creates it.

**load_db.sh**: This script runs executes all of the batch_sql/*.sql scripts in mysql5, thereby importing all of the data.

===== Useful Database Queries =====

Some very useful reporting queries are on the **[[exchange:reporting_project:database_queries|Useful Queries]]** page.
..., etc.

User Tools

Site Tools

Page Tools
