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.
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;
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)
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 displayName,disabled,alpha_id,size,ou,server,mdb from exchange_info where ou LIKE '%Columbia%ollege%' order by disabled,size ASC;
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%';
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;
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;