Report

[CSathiya]

We are working on creating a custom report that will help us determine the following per Employee in a Department in Compliance Accelerator.

1) Total Number of Lexicon Items Flagged

2) Total Number of Random Sample Items Flagged

3) Total Number of Items with Unreviewed Status

4) Total Number of Items with Pending Status

5) Total Number of Items with Questioned Status

The tblIntDiscoveredItems contains the above information at Department level, We are looking for the SQL table or the key that needs to be used which will help us determine the above information in Employee level. 

Any help in determining this information in Employee level will be appreciated.

[Kenneth_Adams · Accepted solution]

Table tblPrincipal contains the PrincipalName - which is the Display Name - of each Monitored Employee. Each Monitored Employee has an AddressOwnerID in that table that relates to the employee's SMTP address or addresses in the tblAddress table. The Display Name or SMTP address(es) for each Monitored Employee can be used to look in the tblIntDiscoveredItems table's Author or Recipient columns to locate messages to or from each Monitored Employee. Does that help?

[CSathiya]

Thanks a lot Kenneth for your suggestion. Please give me a few days to try this with our SQL team.

I looked into the tblIntDiscoveredItems Table and noticed that there is a PrincipalID column in there, is this PrincipalID the same as the PrincipalID in tblPrincipal.

Can this PrincipalID be used instead of the Author or recipient email address?

Also, I noticed that all the rows in tblIntDiscoveredItems had PrincipalID=NULL, can you please let me know if we are missing some setting here that the prinicipal id is not captured in the table?

[Kenneth_Adams]

Greetings and Happy New Year, C Sathiya;

My apologies for the delayed response. I was out of the office due to the holidays and just returned to the office today.

You cannot use the PrincipalID as a substitute for an author or recipient.  You'll need to use the PrincipalID to reference the AddressOwnerID, which will have the reference to all SMTP addresses and Display Name attributes for the user specified by their PrincipalID.

The reason for this is the the tblIntDiscoveredItems table only contains the SMTP address(es) and / or Display Names of each captured e-mail's author and recipients.

The PrincipalID that you saw in the tblIntDiscoveredItems table is referenced in the tblPrincipal table, but is for an attribute of the item other than author or recipient.  You need to pay particular attention to the column name in which you saw the PrincipalID to know what that information is for.  As an example, we could be specifying the PrincipalID of the person who reviewed the item as part of a standard review process to determine if the item is in need of being escalated to an Escalation Reviewer within the Department in which the item was captured.

The PrincipalID column that contains all NULL values is something that was put in place for a future feature that has yet to be used.  You may safely ignore that column.

Please let us know if there is anything more you need for this information request.  We'll be glad to help as much as we are allowed.

[CSathiya]

Greetings and a happy new year to you too Kenneth.

Thanks a lot in helping us determining this information.

As per your suggestion, we will be performing the following:

Step 1: Find the list of employees in a department. For this we use the tblIntTarget.

Step 2: We take the list of address owner ID’s and find their display name. For this we use the tblAddress

Step 3: We take the list of addresses for each address owner ID and run the query for the address in from and to field in tblIntDiscoveredItems for the count against the employee.

The only different step is step 1 as we might not need to find the supervision for all monitored employee but only all monitored employee of a particular department.

Once again, thank you so much for helping us determining the supervision per employee, your help is greatly appreciated :)

[Kenneth_Adams]

You're welcome.  I'm glad that I could be of assistance.