This is an old revision of the document!
For now this will only consist of useful queries. Maybe in the future I'll try to setup a reporting web page for the database.
All non-bes users in a store:
select displayName,bes,size from exchange_info where server='cuit-exchange1' AND mdb='Mailbox Store 10 (CUIT-EXCHANGE1)' AND bes IS NULL order by size;
Count of bes users in a store, and how much mail is in the store:
select count(bes) as "BES Users",server,sg,mdb,sum(size) as space, count(size) as mailboxes from exchange_info where server='cuit-exchange1' AND mdb='Mailbox Store 10 (CUIT-EXCHANGE1)'; mysql> select count(bes) as "BES Users",server,sg,mdb,sum(size) as space, count(size) as mailboxes from exchange_info where server='cuit-exchange1' AND mdb='Mailbox Store 10 (CUIT-EXCHANGE1)'; +-----------+----------------+---------------------+-----------------------------------+----------+-----------+ | BES Users | server | sg | mdb | space | mailboxes | +-----------+----------------+---------------------+-----------------------------------+----------+-----------+ | 18 | cuit-exchange1 | Third Storage Group | Mailbox Store 10 (CUIT-EXCHANGE1) | 47687618 | 79 | +-----------+----------------+---------------------+-----------------------------------+----------+-----------+ 1 row in set (0.03 sec)
Count of bes users in a store, and how much mail is in only their boxes:
select count(bes) as "BES Users",server,sg,mdb,sum(size) as space, count(size) as mailboxes from exchange_info where bes='1' AND server='cuit-exchange1' AND mdb='Mailbox Store 10 (CUIT-EXCHANGE1)'; mysql> select count(bes) as "BES Users",server,sg,mdb,sum(size) as space, count(size) as mailboxes from exchange_info where bes='1' AND server='cuit-exchange1' AND mdb='Mailbox Store 10 (CUIT-EXCHANGE1)'; +-----------+----------------+---------------------+-----------------------------------+----------+-----------+ | BES Users | server | sg | mdb | space | mailboxes | +-----------+----------------+---------------------+-----------------------------------+----------+-----------+ | 18 | cuit-exchange1 | Third Storage Group | Mailbox Store 10 (CUIT-EXCHANGE1) | 15374889 | 18 | +-----------+----------------+---------------------+-----------------------------------+----------+-----------+ 1 row in set (0.04 sec)
mysql> select mdb as "MailStore",sum(size) as "Total Data (KB)",count(bes) as "BES Users",count(displayName) as Mailboxes from exchange_info where server='cuit-exchange1' AND mdb='Mailbox Store 10 (CUIT-EXCHANGE1)' order by displayName; +-----------------------------------+-----------------+-----------+-----------+ | MailStore | Total Data (KB) | BES Users | Mailboxes | +-----------------------------------+-----------------+-----------+-----------+ | Mailbox Store 10 (CUIT-EXCHANGE1) | 34096736 | 18 | 61 | +-----------------------------------+-----------------+-----------+-----------+ 1 row in set (0.04 sec)
Select from an OU, order by disabled, then by size ascending:
select displayName,disabled,alpha_id,size,ou,server,mdb from exchange_info where ou LIKE '%Columbia%ollege%' order by disabled,size ASC;
List sizes of all MDBs, sorted by size:
select sum(size) as "Size in bytes",mdb from exchange_info group by mdb order by sum(size); or select sum(size) as "Size in bytes",mdb from exchange_info group by mdb order by sum(size+0); or
List of users in an MDB, omitting “System” mailboxes:
or select displayName as Name,alpha_id as ID,size as "Mailbox Size in KB",bes as "BES User",disabled from exchange_info where mdb='Mailbox Store 10 (CUIT-EXCHANGE1)' AND displayName NOT LIKE '%System%';
A decent OU search for distribution to a group:
select displayName as Name,alpha_samid as ID,size as "Mailbox Size in KB",server as Server,ou as OU,disabled as Disabled from exchange_info where ou LIKE '%Earth Institute' ORDER BY disabled,size;
After adding dates to the DB:
select displayName as Name,alpha_samid as ID,size as "Mailbox Size in KB",lastLogon as "Last AD Logon",lastExchangeLogon as "Last Mailbox Logon",ou as OU,disabled as Disabled from exchange_info where ou LIKE '%Earth Institute' ORDER BY disabled,lastExchangeLogon;