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”
• Issues

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

That's basically it.

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

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

     Database Definition

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.

Notes on queries

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.

There is no mailbox size calculation, and there probably never will be… Right- this is accomplished via WMI w/VBS.

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.

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:

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

WMI VB Script to return mailbox 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.

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 old/initial bash script code- a total mess, functional to a degree, but impossible to maintain.

Top-level script: runExchangeReporting.sh

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.

Some very useful reporting queries are on the Useful Queries page.

To allow OpenRPT to save reports to the database:

create table report (  report_id serial primary key,  report_name text,  report_descrip text,  report_grade integer not null, report_source text  );

Also need to check out:

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

http://oreports.com/

http://www.pentaho.com/

http://www.jaspersoft.com/ - http://jasperforge.org/

Setup for datavision - copy mysql connector J .jar file into lib directory, setup connection like: