Table of Contents

Reporting/Database Queries

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)
<