Forum Discussion

avnish's avatar
avnish
Level 2
11 years ago

Emails Archived per employees

We would want to determine the total number of emails archived per employee for a particular date range.

Is there any EV SQL table(s) that can be referenced to find the total number of emails archived?

Any help in determining this information will be appreciated.

Please note that we do not have a 1-1 mapping between the employee and the Archives ie an archive can have emails from multiple employees.

Enterprise Vault
Information Governance
Reporting
Windows Server (2003-2008)
  • JesusWept3's avatar
    JesusWept3
    11 years ago

    "Please note that we do not have a 1-1 mapping between the employee and the Archives ie an archive can have emails from multiple employees." --- how do you mean? do you only do journaling?

    If its journaling only, you can't do this in SQL at all, unless you have Discovery Accelerator
    then you could do a search on the journal mailbox and then run some SQL Queries against the customer database

    If it was a mailbox archive then it would simply be something along the lines 

    SELECT  A.ArchiveName "Archive",
	COUNT(S.IdTransaction) "Items Arcchived",
	SUM(S.ItemSize)/1024 "Item Size (MB)"
FROM	EnterpriseVaultDirectory.dbo.Archive A,
	EnterpriseVaultDirectory.dbo.Root R,
	EVVSYourVaultStore_1.dbo.ArchivePoint AP,
	EVVSYourVaultStore_1.dbo.Saveset
WHERE   A.RootIdentity = R.RootIdentity
  AND   R.VaultEntryId = AP.VaultEntryId
  AND   AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND   A.ArchivedDate >= '2013-12-01 00:00:00.0000'
  AND   A.ArchivedDate <= '2013-12-31 23:59:59.0000'
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName

1 Reply

Sort By
  • JesusWept3's avatar
    JesusWept3
    Level 6
    11 years ago

    "Please note that we do not have a 1-1 mapping between the employee and the Archives ie an archive can have emails from multiple employees." --- how do you mean? do you only do journaling?

    If its journaling only, you can't do this in SQL at all, unless you have Discovery Accelerator
    then you could do a search on the journal mailbox and then run some SQL Queries against the customer database

    If it was a mailbox archive then it would simply be something along the lines 

    SELECT  A.ArchiveName "Archive",
	COUNT(S.IdTransaction) "Items Arcchived",
	SUM(S.ItemSize)/1024 "Item Size (MB)"
FROM	EnterpriseVaultDirectory.dbo.Archive A,
	EnterpriseVaultDirectory.dbo.Root R,
	EVVSYourVaultStore_1.dbo.ArchivePoint AP,
	EVVSYourVaultStore_1.dbo.Saveset
WHERE   A.RootIdentity = R.RootIdentity
  AND   R.VaultEntryId = AP.VaultEntryId
  AND   AP.ArchivePointIdentity = S.ArchivePointIdentity
  AND   A.ArchivedDate >= '2013-12-01 00:00:00.0000'
  AND   A.ArchivedDate <= '2013-12-31 23:59:59.0000'
GROUP BY A.ArchiveName
ORDER BY A.ArchiveName