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

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.

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.

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.