Usage Report by Archive/Age - EV 7.5 - Single Vault Store

[Mary_Glenn]

Hello,

I need to produce a report that will show the number of items archived per user per year.  The usage summary report shows me total mail items archived to date.  Can someone provide a quick SQL query i can use to do this?

Thanks in advance for your response!

[JesusWept3]

Try this, not sure how accurate it would be, i just knocked it up so hopefully it's alright
caveat is that it only resolves active users mailboxes and not archives, but it can be tweaked to do that, was just easier this way :)

SELECT EME.mbxDisplayName AS MailboxName,
       LEFT(convert(varchar, S.archiveddate,120),4) AS ArchivedDate,
       COUNT(S.ArchivedDate) AS ArchivedAmount

FROM  yourVaultStore.dbo.saveset S,
      yourVaultStore.dbo.Archivepoint AP,
      EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry EME

WHERE S.ArchivePointIdentity = AP.ArchivePointIdentity
  AND AP.ArchivePointId = EME.DefaultVaultID

GROUP BY EME.mbxDisplayName, LEFT(CONVERT(varchar, ArchivedDate,120), 4)
ORDER BY MailboxName ASC, ArchivedDate ASC