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 archi...
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
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