Thanks Colin, Agreed and Noted. Will raise new community post in the future.

CPExecutionLog table is just used for ControlPoint to keep track of the queue of docs policy is executing against. If policy has been successful those rows are removed so its not a tracking table.

The questions you are asking don't relate to the original community post so may cause confusion for other users. In future please raise a new community post for each quick question or if the question is more in depth raise a support call.

Thanks Colin, Noted. I can use Policy Approver email as a proof and upload in TRIM since it contains the number of items to approve\reject and also run the Document and \ or User activity report which contains the EventType IN (100, 107, 108, 112) [ For ex: 107 Approved after review ] to confirm \ show.

Just one question, I can see the Info in Execute Policies.log as you mentioned.

I can see the [ControlPoint].[dbo].[CPExecutionLog] tables returns zero. This table is alternate to the logs file/ I mean, if any config we can update to store the info into the table rather than file or vice versa? Or this table will always does not have any records and this is expected behaviour?  

Note: In TRIM, all the tables will have the Extended properties which contains the table explanation or purpose. But, in Controlpoint tables extended properties all blank.

I'm not aware of anything being audited however we have instead introduced the option for a review when setting up a policy. If you set a mandatory review for the policy that will ensure all docs the policy was applied to enter 'Awaiting Review' state.  The authorised reviewer(s) must then approve or reject the review before the policy will execute. Without this review being approved no docs would ever be destroyed by accident.

The Execute Policies.log will show a count of docs awaiting review at any given time.

e.g.

25/11/2020 23:37:56 [7500] Info SqlDiscoverer 'delete with review' (7), phase 'Dispose' (7) : 1 documents awaiting review

So if files are missing you know who to contact - the designated reviewers since they had the final say and must have approved the review. The relevant connector delete.log will also show when the delete(s) took place if they were approved.

One further thought is we do offer a facility to notify policy approvers via email.
You could get this email sent to an address that is always saved. That way you have a record of who was emailed to approve reviews etc.

Hi Colin,

In TRIM, We usually update the notes and upload the destruction certificate while deleting records for business justification incase if someone questioning later. Is that any option or tracking mechanism possible in Controlpoint when we dispose the redundant or obsolete etc? I am going through different available reports and could not find much. Usually, redundant once confirmed, we assign the policy and it will dispose as per the scheduled job run. Pls advice.

Thanks Colin, Agree on the workaround and also change in place in future release. I realized by comparing these 2 SP's and commented out the section 4 and it will never work for me untill I cleared duplicate data from the ControlPointMetaStore.ControlPointmetadata.AuditDocumentEvent table or update the RefreshDatbase SP \ Table. Since, I dont require this Audit Info in my export Audit Table, I commented out and reran and all looks good . I can use other way to run the query from AuditDocumentEvent if need as well.

Thanks so much for sharing the events audit info regarding the code and description and really helpful. 

Hi,

RemovePolicyFromRejectedDocs.sql was provided as a workaround for clearing documents in the state Review Rejected as the CP UI does not currently cater for that (CP-10029).

Therefore that script would not have tested in conjunction with other functionality such as Export for BI.

If there is an issue then you should be able to drop the ControlPointExport DB and repopulate it from scratch via "Initial_export.ps1".

[ControlPoint].[dbo].[CPAuditEvent] view contains info on events audited:

Id Item
====================================
100 Policy assigned by user
101 Policy assigned by category
102 Execution rules met
103 Conflict encountered
104 Prevented due to conflict resolution
105 Allowed after conflict resolution
106 Awaiting review
107 Approved after review
108 Rejected after review
109 Executed
110 Execution failed
111 Resubmitted
112 Policy removed by user
113 Policy removed due to policy delete
114 Policy revoked
115 Updating
116 Updated
117 Aborted
118 Request resubmit
119 Request abort
120 Phase executed
121 Classification assigned
122 Policy removed due to item change
123 Request abort and prevent automatic assignment
124 Aborted and automatic assignment prevented
125 Policy to be re-done due to item change
130 Phase undo executed
131 Policy undo executed
132 Item marked for phase undo
133 Policy execution has not started; policy to be removed due to category change
134 Policy phase cannot be undone
200 Assign classification requested
201 User assigned
202 Remove classification requested
203 User removed
204 Update classification alert requested
205 User alert
206 Classification assigned to IDOL item
207 Classification assigned to IDOL item from folder
208 Classification value on item not found
300 Category published
400 Tag added
401 Tag removed
402 Tag updated
403 Tag assigned by user
404 Tag removed by user
500 Delete SOR Record

Regards,
Colin

If someone share the EventType code and description for "ControlPointMetaStore.ControlPointmetadata.AuditDocumentEvent" table will be helpful? 

For ex: 108, 100 etc...

Thanks Colin, it looks fine and data imported successfully.

When I tried to run StoredProcedure [dbo].[RefreshPublishedDocuments] for sync testing purpose, getting the new error as below:

Msg 2627, Level 14, State 1, Procedure RefreshPublishedDocuments, Line 260 [Batch Start Line 33]
Violation of PRIMARY KEY constraint 'PK_AuditDocumentEvent'. Cannot insert duplicate key in object 'dbo.AuditDocumentEvent'. The duplicate key value is (2, 0x000a75c2db9ce279).
The statement has been terminated.

When I checked further: DocKey and RepositoryID is the primaryKey in the ControlPointExport "AuditDocumentEvent" table. The SP[RefreshPublishedDocuments], as part of the Step4,  trying to fetch rows from ControlPointMetaStore.ControlPointMetaStore.ControlPointmetadata.AuditDocumentEvent and insert here, but it contains duplicates and failing. 

I can see many duplicate rows in the AuditDocumentEvent table which is surprise to me. Any idea, is it expected behaviour or these duplicates remains because I ran "RemovePolicyFromRejectedDocs.sql" and the Storedprocedure not cleaning the AuditDocumentEvent table causing the issue? The one temp solution, thinking to comment section4  [ I no need AuditDocumentEvent refresh in the Export database ] and rerun the SP again? Any comments are appreciated. 

--Duplicate of id for pairs based on RepositoryId and Dockey:
select s.id, t.*
from ControlPointMetaStore.ControlPointmetadata.AuditDocumentEvent s with (nolock)
join (
select RepositoryId, Dockey, count(*) as qty
from ControlPointMetaStore.ControlPointmetadata.AuditDocumentEvent with (nolock)
group by RepositoryId, Dockey
having count(*) > 1
) t on s.RepositoryId = t.RepositoryId and s.DocKey = t.DocKey

Yes you are correct in CP 5.8 onwards db_bcp_initial_export_import.sql was replaced with the powershell script Initial_export.ps1.