Compliance Accelerator (CA) Sample SQL Query to report monitored employees by Departments

Compliance Accelerator (CA) Sample SQL Query to report monitored employees by Departments

Article: 100019021
Last Published: 2021-02-22
Ratings: 1 0
Product(s): Enterprise Vault

Problem

Compliance Accelerator (CA) Sample SQL Query to report monitored employees by Parent Department then by Department

Solution

Overview:

Some companies have CA configured with Departments that contain Child Departments.  There may be the need to provide a list of which Monitored Employees are in each Parent and Child Departments.  The following sample SQL query will provide a list of Monitored Employees per Parent/Child Departments.

Resolution:

Run the following sample query using SQL Server Management Studio against the CA Customer database:

DECLARE @ParentCase nvarchar(250)
DECLARE tblCaseScan CURSOR FOR
SELECT CaseID, ParentCaseID FROM tblCase WHERE Name NOT LIKE '%Compliance System Case%'
CREATE TABLE #CaseIDUsed (CaseNumber int)
OPEN tblCaseScan
DECLARE @CaseID int, @ParentCaseID int
FETCH NEXT FROM tblCaseScan INTO
@CaseID, @ParentCaseID
WHILE @@FETCH_Status = 0
BEGIN
INSERT INTO #CaseIDUsed (CaseNumber) VALUES (@ParentCaseID)
IF (SELECT COUNT(*) FROM #CaseIDUsed WHERE CaseNumber = @ParentCaseID) = 1
BEGIN
SET @ParentCase = (SELECT tc.[Name] FROM tblCase AS tc WHERE CaseID = @ParentCaseID)
SELECT @ParentCase AS ' Parent Case',
tc.[Name] AS 'Sub-Department',
tp.PrincipalName AS 'Monitored Employee',
tp.PrincipalLogin AS 'Principal Login'
FROM tblCase AS tc
INNER JOIN tblIntTarget AS tint ON tc.CaseID = tint.CaseID
INNER JOIN tblPrincipal AS tp ON tint.AddressOwnerID = tp.AddressOwnerID
INNER JOIN tblAddressUser AS tau ON tint.AddressOwnerID = tau.AddressOwnerID
WHERE tc.ParentCaseID = @ParentCaseID
AND tau.MonitorStatusID = 120
AND Name NOT LIKE '%Compliance System Case%'
ORDER BY tc.[Name], tau.DisplayName
END
FETCH NEXT FROM tblCaseScan INTO
@CaseID, @ParentCaseID
END
CLOSE tblCaseScan
DEALLOCATE tblCaseScan
DROP TABLE #CaseIDUsed

 

Was this content helpful?