====== 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. [[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 ===== //**There is no mailbox size calculation, and there probably never will be...**// 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: 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: {{:exchange:reporting:datavision_connection_info.png|}}