Forum Discussion

dlisterjr's avatar
dlisterjr
Level 2
16 years ago
Solved

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?
Database
Enterprise Vault
Information Governance
Reporting
  • JesusWept3's avatar
    JesusWept3
    16 years ago

    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)

4 Replies

Sort By
  • JesusWept3's avatar
    JesusWept3
    Level 6
    16 years ago

    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)

  • dlisterjr's avatar
    dlisterjr
    Level 2
    16 years ago
    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.
  • MichelZ's avatar
    MichelZ
    Level 6
    16 years ago
    If you want numbers, you can use:

    SELECT COUNT(*) FROM ExchangeMailboxEntry GROUP BY MbxArchivingState

    Cheers
    Michel
  • TonySterling's avatar
    TonySterling
    Moderator
    16 years ago
    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