Highlighted

SQL Query

Can anyone point me toward a SQL query that will show the total size of the Archive Store and How many users are enabled?
1 Solution

Accepted Solutions
Accepted Solution!

One thing you could do is the

One thing you could do is the following


USE EnterpriseVaultDirectory
SELECT COUNT(DISTINCT(EME.DefaultVaultId) AS NumberOfUsers, SUM(S.ItemSize)
FROM EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
            yourVaultStoreDB.dbo.Saveset S,
            yourVaultStoreDB.dbo.ArchivePoint AP
WHERE EME.DefaultVauiltId = AP.ArchivePointId
       AND S.ArchivePointIdentity = AP.ArchivePointIdentity


few things to note:
 - Only enabled users will have a DefaultVaultID in the ExchangeMailboxEntry table
 - This will not work if the Vault Store DB and the EnterpriseVaultDirectory database exist on different servers
 - It will give you the itemsizes and the user count per vault store, not per site
 - ItemSize will give you the results in KB so you will have to do the /1024 if you want MB etc
 - Also note that ItemSize is a rounded number, so an if items are 4.5kb and 3.5kb, it will report them as 5 and 4 respectively, thats a 1kb offset in that scenario

so you may have 12,000 users, but if you target a vault store that has 4000 users, it will only give you the user count and itemsizes for those 4000 users
you'd have to run another query (perhaps a union or subselect) and target the other vault store databases)

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

View solution in original post

4 Replies

try these

For disabled mailboxes
SELECT *
FROM ExchangeMailboxEntry
Where MbxArchivingState = '2'  

For new mailboxes
SELECT *
FROM ExchangeMailboxEntry
Where MbxArchivingState = '0'  

For enabled mailboxes
SELECT *
FROM ExchangeMailboxEntry
Where MbxArchivingState = '1' 

For the size, have you tried the Vault Store Usage report or usage.asp?
http://vaultserver.company.com/EnterpriseVault/usage.asp
For the best O365 Reporting Solution click here Radar-Reporting

If you want numbers, you can

If you want numbers, you can use:

SELECT COUNT(*) FROM ExchangeMailboxEntry GROUP BY MbxArchivingState

Cheers
Michel

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

I do use usage.asp, but I

I do use usage.asp, but I need to use this data in a scripted report. So I'm looking for a way to query it from the database directly.
Accepted Solution!

One thing you could do is the

One thing you could do is the following


USE EnterpriseVaultDirectory
SELECT COUNT(DISTINCT(EME.DefaultVaultId) AS NumberOfUsers, SUM(S.ItemSize)
FROM EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME,
            yourVaultStoreDB.dbo.Saveset S,
            yourVaultStoreDB.dbo.ArchivePoint AP
WHERE EME.DefaultVauiltId = AP.ArchivePointId
       AND S.ArchivePointIdentity = AP.ArchivePointIdentity


few things to note:
 - Only enabled users will have a DefaultVaultID in the ExchangeMailboxEntry table
 - This will not work if the Vault Store DB and the EnterpriseVaultDirectory database exist on different servers
 - It will give you the itemsizes and the user count per vault store, not per site
 - ItemSize will give you the results in KB so you will have to do the /1024 if you want MB etc
 - Also note that ItemSize is a rounded number, so an if items are 4.5kb and 3.5kb, it will report them as 5 and 4 respectively, thats a 1kb offset in that scenario

so you may have 12,000 users, but if you target a vault store that has 4000 users, it will only give you the user count and itemsizes for those 4000 users
you'd have to run another query (perhaps a union or subselect) and target the other vault store databases)

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

View solution in original post