01-21-2014 02:31 PM
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.
Solved! Go to Solution.
01-21-2014 04:04 PM
"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
01-21-2014 04:04 PM
"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