Differences

This shows you the differences between two versions of the page.

--- exchange:reporting_project:database_queries [2010/02/02 07:31]
ben created
+++ exchange:reporting_project:database_queries [2010/02/18 15:50] (current)
ben
@@ Line 3: / Line 3: @@
 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:
+==== All non-bes users in a store: ====
 <code>
 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;
 </code>
-Count of bes users in a store, and how much mail is in the store:
+==== Count of bes users in a store, and how much mail is in the store: ====
 <code>
 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)';
@@ Line 19: / Line 19: @@
 +-----------+----------------+---------------------+-----------------------------------+----------+-----------+
 row in set (0.03 sec)
+</code>
+==== Count of bes users in a store, and how much mail is in only their boxes: ====
-</code>
-Count of bes users in a store, and how much mail is in only their boxes:
 <code>
 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)';
@@ Line 48: / Line 47: @@
 </code>
-Select from an OU, order by disabled, then by size ascending:
+==== Select from an OU, order by disabled, then by size ascending: ====
 <code>
 select displayName,disabled,alpha_id,size,ou,server,mdb from exchange_info where ou LIKE '%Columbia%ollege%' order by disabled,size ASC;
 </code>
+==== List sizes of all MDBs, sorted by size: ====
-List sizes of all MDBs, sorted by size:
 <code>
 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
 </code>
-List of users in an MDB, omitting "System" mailboxes:
+==== List of users in an MDB, omitting "System" mailboxes: ====
 <code>
-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%';
 </code>
-A decent OU search for distribution to a group:
+==== A decent OU search for distribution to a group: ====
 <code>
  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;
 </code>
+==== After adding dates to the DB: ====
-After adding dates to the DB:
 <code>
 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;
+</code>
+==== Some Quotas ====
+<code>
+select displayName as Name,alpha_samid as ID,size as "Mailbox Size (KB)",bes as "BES User",disabled as Disabled,lastExchangeLogon as "Last Mailbox logon",lastLogon as "Last AD Logon",mDBStorageQuota,mDBOverQuotaLimit from exchange_info where mDBStorageQuota > '0' AND displayName NOT LIKE '%System%' order by disabled,bes,OU,displayName;
+</code>
+==== Quotas set with no Prohibit Send ====
+<code>
+select displayName as Name,alpha_samid as ID,size as "Mailbox Size (KB)",bes as "BES User",disabled as Disabled,mDBStorageQuota,mDBOverQuotaLimit from exchange_info where mDBStorageQuota > '0' AND mDBOverQuotaLimit='0' AND displayName NOT LIKE '%System%' order by disabled,bes,OU,displayName;
+</code>
+==== All users from a store if not disabled, except also disabled if in a particular OU, and not bes users ====
+<code>
+select displayName,alpha_samid,size,disabled,bes,OU,server,mdb from exchange_info where (disabled='1' AND OU LIKE '%Medical Center%' AND mdb LIKE '%MS6%') OR displayName NOT LIKE '%System%' AND server LIKE '%exchange3%' AND mdb LIKE '%MS6%' and bes='0' AND disabled='0' ORDER BY disabled ASC,displayName;
+</code>
+==== Similar to above, but the alternate group, users in the mdb, not in that OU, but either disabled, or a bes user ====
+<code>
+select displayName,alpha_samid,size,disabled,bes,OU,mdb,lastExchangeLogon from exchange_info where NOT ((disabled='1' AND OU LIKE '%Medical Center%' AND mdb LIKE '%MS6%' AND bes='0') OR displayName NOT LIKE '%System%' AND server LIKE '%exchange3%' AND mdb LIKE '%MS6%' and bes='0' AND disabled='0') AND mdb LIKE '%MS6%' ORDER BY disabled,size;
+</code>
+==== Export to CSV file ====
+<code>
+select displayName,size,ou from exchange_info INTO OUTFILE '/Users/ben/tmp/test.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ;
+</code>
+==== Report for a particular OU into a CSV file ====
+<code>
+select displayName as "Mailbox Name",alpha_samid as "ID",disabled as Disabled,bes as "Blackberry User",size "Mailbox Size (KB)",lastLogon as "Last AD Logon", lastExchangeLogon as "Last Mailbox Logon",OU from exchange_info where OU LIKE '%Columbia%College%' AND displayName NOT LIKE '%System%' ORDER BY disabled DESC,size INTO OUTFILE '/Users/ben/tmp/test.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
 </code>

..., etc.

User Tools

Site Tools

Differences

Page Tools