exchange:reporting_project:database_queries
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
exchange:reporting_project:database_queries [2010/02/02 07:36] ben |
exchange:reporting_project:database_queries [2010/02/18 15:50] (current) ben |
||
|---|---|---|---|
| Line 59: | Line 59: | ||
| select sum(size) as "Size in bytes",mdb from exchange_info group by mdb order by sum(size+0); | select sum(size) as "Size in bytes",mdb from exchange_info group by mdb order by sum(size+0); | ||
| </code> | </code> | ||
| - | |||
| ==== List of users in an MDB, omitting "System" mailboxes: ==== | ==== List of users in an MDB, omitting "System" mailboxes: ==== | ||
| <code> | <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%'; | 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> | </code> | ||
| Line 72: | Line 70: | ||
| 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",server as Server,ou as OU,disabled as Disabled from exchange_info where ou LIKE '%Earth Institute' ORDER BY disabled,size; | ||
| </code> | </code> | ||
| - | |||
| ==== After adding dates to the DB: ==== | ==== After adding dates to the DB: ==== | ||
| <code> | <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; | 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> | </code> | ||
exchange/reporting_project/database_queries.1265114193.txt.gz ยท Last modified: 2010/02/02 07:36 by ben
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 3.0 Unported
