cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

SQL Queries

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

 

 

 

 

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

Would suggest working with

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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post

7 Replies
Highlighted

List all those enabled for

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

https://www.linkedin.com/in/alex-allen-turl-07370146
Highlighted

The first query list all teh

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

Highlighted

That is what the first query

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?

https://www.linkedin.com/in/awsterling/
Highlighted

Yes please

Yes please

Highlighted

SELECT COUNT(*) FROM

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

Quadrotech - EV Migration and Office 365 Solutions, creators of Archive Shuttle.
Highlighted

Msg 208, Level 16, State 1,

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+ ===

Highlighted
Accepted Solution!

Would suggest working with

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
https://www.linkedin.com/in/alex-allen-turl-07370146

View solution in original post