SQL Queries

[PATRICK_Kitchen]

Hi Everyone,

Does enyone have any SQL queries ? looking for two to do the following:-

1.   List number of mailboxes enabled for archiving.

2.   List all mailboxes that are enabled for archiving with a archive of less that 50MB.

Thanks team

Pat

[JesusWept3]

List all those enabled for Archiving

 USE EnterpriseVaultDirectory
 SELECT MbxDisplayName "Mailbox Name"
 FROM ExchangeMailboxEntry
 WHERE MbxArchivingState =1 AND DefaultVaultID IS NOT NULL

List Archives enabled and under 50mb

   SELECT EME.MbxDisplayName "Mailbox Name",
          AP.ArchivedItemsSize/1024 "Archive Size (MB)"
   FROM   EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
          yourVaultStore.dbo.ArchivePoint AP
   WHERE  AP.ArchivePointId = EME.DefaultVaultId
     AND  EME.MbxArchivingState = 1
     AND  AP.ArchivedItemsSize < 51200

For the above you will need to run that for each vault store, replaced yourVaultStore.dbo, with the physical database name for your Vault Store database

[PATRICK_Kitchen]

The first query list all teh archives I just want the total number of mailboxes that are enabled for archiving please

[TonySterling]

That is what the first query does.  If a mailbox archiving state is 1 that means it is enabled.

Do you mean you just want a count?

[PATRICK_Kitchen]

Yes please

[MichelZ]

 SELECT COUNT(*)
FROM ExchangeMailboxEntry
WHERE MbxArchivingState =1 AND DefaultVaultID IS NOT NULL 

[PATRICK_Kitchen]

Msg 208, Level 16, State 1, Line 1
Invalid object name 'VaultStoreEntry.dbo.ArchivePoint'. 

On second script - also want to modify script to say if mailboxis enabled for archiving and size is 50MB or less then set MbxArchivingstate=2 in other words disable archiving for all mailboxes of 50mb or less 

 =2+ ===

[JesusWept3 · Accepted solution]

Would suggest working with your DBA Also take a look at this http://www.w3schools.com/sql/default.asp SQL is pretty simple and is a useful tool for you to know, I'm not however going to post those changes because firstly it's not the way to disable users, secondly if you need to undo the changes you will need to do it asap rather than waiting for people on the forums What I would suggest is run the second query, dump the user list in to a Distribution List called something like "Less than 50mb" or something as equally creative then Once in a dl you can provision them based on that dl name