cancel
Showing results for 
Search instead for 
Did you mean: 

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

Sani_B
Level 6
Partner Accredited

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 ACCEPTED SOLUTION

Accepted Solutions

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

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

4 REPLIES 4

AndrewB
Moderator
Moderator
Partner    VIP    Accredited

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
Level 6
Partner Accredited

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

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

MikeK-GT
Level 4

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.