Hi ya,
I have two queries that I have used. This one will give you a count of items by folder for each archive.
SELECT A.ArchiveName
,CAST(AF.FolderPath AS NVARCHAR(MAX))
,AF.FolderName
,COUNT(S.IdTransaction) "Number of Archived Items"
-- ,SUM(S.ItemSize) "Total Size (KB)"
FROM Saveset S,
ArchivePoint Ap,
Vault V,
EnterpriseVaultDirectory.dbo.Root R,
EnterpriseVaultDirectory.dbo.Root R2,
EnterpriseVaultDirectory.dbo.Archive A,
EnterpriseVaultDirectory.dbo.ArchiveFolder AF
WHERE S.VaultIdentity = V.VaultIdentity
AND S.ArchivePointIdentity = AP.ArchivePointIdentity
AND AP.ArchivePointID = R.VaultEntryID
AND V.VaultID = R2.VaultEntryID
AND R.RootIdentity = A.RootIdentity
AND R2.RootIdentity = AF.RootIdentity
AND AF.FolderPath Like '%System%'
GROUP BY A.ArchiveName, CAST(AF.FolderPath AS NVARCHAR(MAX)),AF.FolderName
This one will give you the count by retention category for each archive:
SELECT ex.MbxDisplayName, rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count
FROM Saveset s
Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
GROUP BY rc.RetentionCategoryName, ex.MbxDisplayName
ORDER BY ex.MbxDisplayName
I haven't put them into one query but might be possible, I just don't have time to give it go but using them should give you an idea of what you have going on.
