Forum Discussion

Sani_B's avatar
Sani_B
Level 6
10 years ago

SQL-Query listing ArchiveName, ArchivedItems, ArchivedItemsSize for users in certain OU

Hi,

Since I'm not a genius with the SQL querys I'm asking your help.

I need a query that lists ArchiveName, ArchivedItems, ArchivedItemsSize for users in certain OU.

There are 3 Vaultdatabases that this query has to take in to count.

 

I have no idea how to put this query together, please help.

 

Sani B.

 

Enterprise Vault
Information Governance
Reporting
Windows Server (2003-2008)
  • AndrewB's avatar
    AndrewB
    10 years ago

    hey sani, give this a shot. you need to replace <VaultStoreDB> with the name of your vault store database and <OU> with the name of the OU you're filtering on.

     

    SELECT A.ArchiveName, 

           COUNT(S.ItemSize) "Item Count",

           SUM(S.ItemSize)/1024 "Total Size (MB)"

    FROM   <VaultStoreDB>.dbo.Saveset S,

           <VaultStoreDB>.dbo.ArchivePoint AP,

           EnterpriseVaultDirectory.dbo.Root R,

           EnterpriseVaultDirectory.dbo.Archive A,

           EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry E

    WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity

      AND  AP.ArchivePointId = R.VaultEntryId

      AND  R.RootIdentity = A.RootIdentity

      AND  AP.ArchivePointId = E.DefaultVaultId

      AND  E.ADMbxDN like '%<OU>%'

    GROUP BY A.ArchiveName

    ORDER BY A.ArchiveName

4 Replies

Sort By
  • AndrewB's avatar
    AndrewB
    Moderator
    10 years ago

    hey sani, give this a shot. you need to replace <VaultStoreDB> with the name of your vault store database and <OU> with the name of the OU you're filtering on.

     

    SELECT A.ArchiveName, 

           COUNT(S.ItemSize) "Item Count",

           SUM(S.ItemSize)/1024 "Total Size (MB)"

    FROM   <VaultStoreDB>.dbo.Saveset S,

           <VaultStoreDB>.dbo.ArchivePoint AP,

           EnterpriseVaultDirectory.dbo.Root R,

           EnterpriseVaultDirectory.dbo.Archive A,

           EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry E

    WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity

      AND  AP.ArchivePointId = R.VaultEntryId

      AND  R.RootIdentity = A.RootIdentity

      AND  AP.ArchivePointId = E.DefaultVaultId

      AND  E.ADMbxDN like '%<OU>%'

    GROUP BY A.ArchiveName

    ORDER BY A.ArchiveName

  • Sani_B's avatar
    Sani_B
    Level 6
    10 years ago

    Hey Andrew!

    Thanks for that! Looks like it's working.

    Definitely on the right track and it's not a big deal for me to run that query against all the three database separately, but just out of curiosity and for the future - how does one run the query on all three Vault databases at once?

     

    Sani B.

  • GertjanA's avatar
    GertjanA
    Moderator
    10 years ago

    Hi Sani,

    I believe you cannot use multiple VSDB's, due to the VSDB's having identical columns.

    I use such queries as below.

    SELECT A.ArchiveName,
           COUNT(S.ItemSize) "Item Count",
           SUM(S.ItemSize)/1024 "Total Size (MB)"
    FROM   <VaultStoreDB>.dbo.Saveset S,
           <VaultStoreDB>.dbo.ArchivePoint AP,
    -- FROM   <VaultStoreDB2>.dbo.Saveset S,
    --       <VaultStoreDB2>.dbo.ArchivePoint AP,
    -- FROM   <VaultStoreDB3>.dbo.Saveset S,
    --        <VaultStoreDB3>.dbo.ArchivePoint AP,
           EnterpriseVaultDirectory.dbo.Root R,
           EnterpriseVaultDirectory.dbo.Archive A,
           EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry E
    WHERE  S.ArchivePointIdentity = AP.ArchivePointIdentity
      AND  AP.ArchivePointId = R.VaultEntryId
      AND  R.RootIdentity = A.RootIdentity
      AND  AP.ArchivePointId = E.DefaultVaultId
      AND  E.ADMbxDN like '%<OU>%'
    GROUP BY A.ArchiveName
    ORDER BY A.ArchiveName

    Run it. Save result.

    Remove -- from the 2nd from line, and the one below, and -- in front of the 1st and 2nd one.

    That way, I have one query, I can run multiple times to get the full result.

     

  • MikeK-GT's avatar
    MikeK-GT
    Level 4
    2 years ago

    Hey Guys, I need the same query but listing all archives that do not have AD account. Basically a list of all archives with no AD account. Thanks in advance. 

Related Content