Description

The monitoring policy consists of percentages per item type that should be captured by Enterprise Vault (EV) Compliance Accelerator (CA). These percentages are user-defined values that can be customised per item type for each Department and/or for each Monitored Employee. Item types include Bloomberg, Exchange, Fax, Instant Messaging, Lotus Domino, Social and SMTP. Some item types, such as Exchange, Lotus Domino and SMTP, can have multiple sub-types. For example, Internal specifies the items where the author and all recipients are internal to the organisation, Inbound specifies the items where the author is external to the organisation and at least one recipient is internal, and External specifies the items where the author is internal to the organisation and at least one recipient is external. Note that if an item type's percentage for a Monitored Employee is different from the same item type's percentage at the Department level, the higher of the two percentages will be applied.

The Show effective monitoring policy button in each Department will list the actual or effective policy percentages for each item type per Monitored Employee. There may be a need to programmatically obtain the effective monitoring policy for one or more Departments, such as for reporting purposes or to investigate issues with Tagging and/or Random Sampling. The following SQL queries can assist with this need.

To execute these queries:

1. Log on to the SQL Server with a user having rights to run queries against the CA Customer database.
2. Open a new query window focused on the CA Customer database.
3. Select the required query output under Query | Results To. The available output types are:
3.1. Results to Grid: This is the default output type and will output the results in a table format in the query window's Results pane. The results can then be saved as a .csv file (default) or a text file.
3.2. Results to Text: This will output the results in the query window's Messages pane. The results can then be copied and pasted to a text file.
3.3. Results to File: This will prompt for a location and filename for the output file, with a default file extension of .rpt. The output file is a text file and should be readable in any text editor. This option is similar to Results to Text and allows for saving the query output directly to a file without displaying the results.
4. Execute the required query, editing as indicated.

Query 1 - For a specific Department (edit the Department's CaseID as indicated):

DECLARE @CaseID int = X -- Edit the Department's CaseID here
, @NumMonitoredEmployee int
, @BypassMonitoring bit;
SELECT * FROM tblCase WHERE CaseID = @CaseID;
SELECT @BypassMonitoring = BypassMonitoring
, @NumMonitoredEmployee = NumMonitoredEmployee 
FROM tblCase WHERE CaseID = @CaseID
IF @BypassMonitoring = 1 OR @NumMonitoredEmployee = 0
BEGIN;
SELECT [Department CaseID] = @CaseID
, [Disable Monitoring of Employees in this Department] = CASE @BypassMonitoring WHEN 0 THEN 'Monitored' WHEN 1 THEN 'Not Monitored'END
, [Monitored Employees in Department] = @NumMonitoredEmployee;
END ELSE BEGIN;
EXEC rpt_MonitoredEmployees_SR @CaseID = @CaseID;
END;

Query 2 - For all Open Departments (no edits needed):

DECLARE @CaseID int
, @NumMonitoredEmployee int
, @BypassMonitoring bit;
DECLARE EffectiveMonPol CURSOR FOR
SELECT CaseID
FROM tblCase
WHERE Type = 102
AND StatusID = 20 -- Open
ORDER BY CaseID;
OPEN EffectiveMonPol;
FETCH NEXT FROM EffectiveMonPol INTO @CaseID;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT * FROM tblCase WHERE CaseID = @CaseID;
SELECT @BypassMonitoring = BypassMonitoring
, @NumMonitoredEmployee = NumMonitoredEmployee 
FROM tblCase WHERE CaseID = @CaseID;
IF @BypassMonitoring = 1 OR @NumMonitoredEmployee = 0
BEGIN;
SELECT [Department CaseID] = @CaseID
, [Disable Monitoring of Employees in this Department] = CASE @BypassMonitoring WHEN 0 THEN 'Monitored' WHEN 1 THEN 'Not Monitored'END
, [Monitored Employees in Department] = @NumMonitoredEmployee;
END ELSE BEGIN;
EXEC rpt_MonitoredEmployees_SR @CaseID = @CaseID;
END;
FETCH NEXT FROM EffectiveMonPol INTO @CaseID;
END;
CLOSE EffectiveMonPol;
DEALLOCATE EffectiveMonPol;