cancel
Showing results for 
Search instead for 
Did you mean: 

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.

 

1 Solution

Accepted Solutions
Accepted Solution!

hey sani, give this a shot.

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

View solution in original post

3 Replies
Accepted Solution!

hey sani, give this a shot.

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

View solution in original post

Hey Andrew! Thanks for that!

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

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.

 

Regards. Gertjan