cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query

dlisterjr
Level 2
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 ACCEPTED SOLUTION

Accepted Solutions

JesusWept3
Level 6
Partner Accredited Certified

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 4

TonySterling
Moderator
Moderator
Partner    VIP    Accredited Certified
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

MichelZ
Level 6
Partner Accredited Certified
If you want numbers, you can use:

SELECT COUNT(*) FROM ExchangeMailboxEntry GROUP BY MbxArchivingState

Cheers
Michel

cloudficient - EV Migration, creators of EVComplete.

dlisterjr
Level 2
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.

JesusWept3
Level 6
Partner Accredited Certified

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