Total Emails Sent/Received per Department/Advisor

[CSathiya]

We are working on creating a custom report that will help us determine the total number of emails sent/received per department and per advisor for a particular date range.

Is there any SQL table(s) in CA, DA or EV that can be referenced to find the total number of emails sent/received without running any searches in CA and DA?

Any help in determining this information will be appreciated.

[Pradeep-Papnai]

When you say ‘department’ then how it is integrated with EV, like each department have a separate vault store, If yes then you can run following query against vault store database.

SELECT

datepart(yy, iddatetime) as 'year'

,datepart(mm, iddatetime) as 'month'

,datepart(dd, iddatetime) as 'day'

, sum(itemsize)/(1024) as 'MB size'

, count(*) as 'count'

FROM saveset

WHERE iddatetime > (getdate() - 135)

GROUP BY

datepart(yy, iddatetime)

,datepart(mm, iddatetime)

,datepart(dd, iddatetime)

ORDER BY

datepart(yy, iddatetime)

,datepart(mm, iddatetime)

,datepart(dd, iddatetime)

This will only report items those are archived by EV based on send/receive date, it will report for last 135 days, you can change this no as per your wish.

[GabeV]

Hi CSathiya,

You can try with this query:

DECLARE @DateFrom datetime, @DateTo datetime

SET @DateFrom = '2014-01-01'
SET @DateTo = GETDATE() -- For today's date

USE YourVaultStoreDB

SELECT Archive.ArchiveName, COUNT(*)AS TotalItems     
FROM EnterpriseVaultDirectory.dbo.Archive Archive JOIN EnterpriseVaultDirectory.dbo.Root RT on Archive.rootidentity = RT.rootidentity JOIN ArchivePoint on RT.VaultEntryId = ArchivePoint.ArchivePointId JOIN Saveset on ArchivePoint.ArchivePointIdentity = Saveset.ArchivePointIdentity JOIN SavesetProperty SP on Saveset.SavesetIdentity = SP.SavesetIdentity
WHERE Saveset.IdDateTime > @DateFrom AND Saveset.IdDateTime < @DateTo 
-- AND ArchiveName IN ('User1')
GROUP BY ArchiveName ORDER BY ArchiveName

This query will give you all the savesets per archive. You can change the parameters DateFrom - DateTo to specify a date range. However, if you want to select only a few archives, you can update the line in comment and add more archives to the list, such as ('User1','User2','User3', .... ). You can also export the output to Excel and work with the data. I hope this helps.

[CSathiya]

Thanks Pradeep for the suggestion, but the deparment is not the vault store database.

Compliance Accelerator lets us organize monitored employees into departments that reflect the structure of the company. For example, we can create departments that are called "Marketing", "Sales", and "Engineering". Then you can add the employees that you want to monitor to the appropriate departments.

We are looking for getting the ecommunications count per CA departments. Is there a way to find this information (the total number of emails sent/received) in CA without running a search?

Or can this information be found in DA or EV database? If not by department, is it possible to get this information per employee?

[CSathiya]

Thanks GabeV for the suggestion. We are using EV 10.0 base. I do not see the dbo.Archive in the list of tables. Please let me know if I am looking at the wrong place.

We are trying to find the total number of emails sent/received per person.

[CSathiya]

Please find a screenshot of the tables in the EV Journal Database attached.

[GabeV]

You need to run that SQL query from the Vault Store DB. The references for that specific table is in the query. Run it and let us know if that's the information you are looking for.

[CSathiya]

Hi GabeV,

I ran the query, the query result was empty.

It had 2 columns ArchiveName and TotalItems but there were no rows.

Thanks,

C. Sathiya

[GabeV]

Hi,

Are you using the dates on the original query? You might need to use a different date range.

[GabeV]

Change these paremeters:

SET @DateFrom = '2014-01-01'
SET @DateTo = GETDATE() -- For today's date

To something like this:

SET @DateFrom = '2013-01-01'
SET @DateTo = '2013-07-01'

That will set the date range from January 1st 2013 to July 1st 2013

[CSathiya]

Thanks GabeV, the change in date displayed all archives in the Journal Database and the total number of items in the archives. This script is helpful but we are looking for number of emails sent/received by individuals. For eg, the number of emails sent and received by Employee A during a particular period.

[GabeV]

You can just run the script in another Vault Store database where you have the user's archives. Also, in the Enterprise Vault databases you don't have the classification you are looking for, such as departments. You can add the archives to the query for a specific department. You might need to get your DBA involved to assist you with the query.

[CSathiya]

Thanks GabeV, is there any table in CA/EV/DA that might have details on all the emails sent/received that has been archived? we can use this table to create a query to find the count of emails sent/received based on the from and to address.