Summary Archive Content by Date (monthly) Report

[Elio_C]

Hi

Given a specified archive I'm looking for a way to summarise the content (item count and size) by month, something like the below would be ideal:

Month    Count    Size
2015-06    100    10
2015-05    125    12
2015-04    150    15
...    ...    ..

I have related queries (https://www-secure.symantec.com/connect/forums/archive-folder-and-item-counts & https://www-secure.symantec.com/connect/forums/summarize-archived-data) but can't figure out a way to filter the savesets by archive.

Any help would be greatly appreciated.

Thanks

[TonySterling · Accepted solution]

This should work, run against the vault store databases:

select ex.MbxDisplayName, "Archived Date" = left (convert (varchar, s.ArchivedDate,20),7),"Monthly Rate" = count(*)

,"Archived Size-MB" = sum (itemsize)/1024

from saveset s

Join Archivepoint a on a.ArchivePointIdentity = s.ArchivePointIdentity

Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID

where s.ArchivedDate > dateadd("mm", -12, getdate())

group by  ex.MbxDisplayName, left(convert(varchar, s.ArchivedDate,20),7)

order by  ex.MbxDisplayName, "Archived Date" Desc

[Elio_C]

Tremendous, exactly what I was looking for, thanks Tony