Enterprise Vault useful SQL queries

**  Description:                This Query will sum up the items that were archived by week.                   
**                                                      
**  Requirements:  Updated the Vault databases as required, and update the Vault store identifier to match.    Change VaultstoreX to
**     your first vaultstore and VaultstoreY to your second, you can add as many as you need to get them all.        
**     DateDiff - we used this as our starting week, so we could see by week, buy you could change that to be the first day of the year
**     and report on weekly archiving.
**    then import into excel and create a pivot chart.

select "Vault Store" = 'VaultStoreX',

"Week Number" = DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

"Date" = CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME),

"Items Archived" = count (*),

"Original Size (MB)" = sum (sp.originalsize)/1024/1024,

"Compressed Size (MB)" = sum (s.itemsize)/1024

from VaultStoreX.dbo.saveset S with (nolock)

INNER JOIN VaultStoreX.dbo.savesetproperty SP with (nolock) on S.savesetidentity = SP.savesetidentity

INNER JOIN VaultStoreX.dbo.Vault v with (nolock) on s.VaultIdentity = v.VaultIdentity

INNER JOIN VaultStoreX.dbo.ArchivePoint ap with (nolock) ON v.ArchivePointIdentity = ap.ArchivePointIdentity

group by DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME)

UNION ALL

select "Vault Store" = 'VaultStorey',

"Week Number" = DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

"Date" = CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME),

"Items Archived" = count (*),

"Original Size (MB)" = sum (sp.originalsize)/1024/1024,

"Compressed Size (MB)" = sum (s.itemsize)/1024

from VaultStorey.dbo.saveset S INNER JOIN

VaultStorey.dbo.savesetproperty SP on S.savesetidentity = SP.savesetidentity

group by DATEDIFF(week, '2008-10-01 00:00:00' , s.archiveddate),

CAST(CONVERT(VARCHAR(10), s.archiveddate, 111) AS DATETIME)

order by 1,2,3