..., 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 Setup ======

===== 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.

This process is being moved to Perl.  Here's the [[exchange:reporting:initial_batch_scripts|old/initial bash script code]]- a total mess, functional to a degree, but impossible to maintain.

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

|^Script                    |^Description |
|parse_allExchange.pl       ||dname,cname,displayName,legacyExchangeDN      |
|parse_allExchange_uni.pl   ||a test of unicode format    |
|parse_bes.pl               ||set bes to '1' where legacyDN=  |
|parse_disabled.pl          ||set disabled to '1' where DN= |
|parse_id.pl                ||set logon ID where DN= |
|parse_lastExchangeLogon.pl || |
|parse_lastLogon.pl         || |
|parse_mailboxes.pl         || |
|parse_ou.pl                || |
|parse_quota.pl             || |
|parse_samid.pl             || |
|sort_files.pl              || |
|unicode.pl                 || |

===== 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.
====== OpenRPT Notes ======
To allow OpenRPT to save reports to the database:
<code>
create table report (  report_id serial primary key,  report_name text,  report_descrip text,  report_grade integer not null, report_source text  );
</code>

Also need to check out:

http://www.eclipse.org/birt/phoenix/intro/

And 

http://oreports.com/
..., etc.

User Tools

Site Tools

Page Tools
