Forum Discussion

Mary_Glenn's avatar
Mary_Glenn
Level 2
15 years ago

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

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!
Enterprise Vault
Information Governance
  • JesusWept3's avatar
    JesusWept3
    Level 6
    15 years ago

    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