Duplicate and Master report from CP

Has anyone generated an Audit Report (via Report Builder) that will list the Duplicate Files and their Master Location? 

We will be deleting some internal duplicates in a File Share but would like to have a report, for future use, to show where the Master file name and path location. 

OR

Just an export where I can include properties that will export to a spreadsheet.

Either one will do but I don't think any of the Advanced Properties would show the Master except the MD5 that matches the Master 

Thanks!

  • Verified Answer

    Hi Erik,

    I'm not aware of anyone creating an audit report for this however I can share with you a SQL script to produce a duplicate report which will work with fileshares. It gets the list of duplicates the master document for each. 

    Please unzip the attached file called duplicateDocumentReport.zip

    Load both stored procedures in SQL Server where your CP databases reside.

    Then, to generate a report of duplicates change name for Repository to desired repository name e.g. 'folder3' and run:

    USE [ControlPointMetaStore]
    GO
    DECLARE @return_value int
    EXEC @return_value = [dbo].[GetListOfDuplicateDocuments]
    @RepositoryName = N'folder3'

    The results will be in results pane. You can highlight the rows and copy them as required.
    Please note that the order of the list is that the master document is shown first with the master flag set to 1 and all the following documents are the duplicates until next document with master flag set, and the order repeats.

     

    Regards,

    Colin

    duplicateDocumentReport.zip
  • Hi Colin,

    This report will give within single Repository with Items as Master and Duplicates. But, How do we get the report to identify the records availabe in TRIM when we tried to use TRIMConnector and set TRIM as Master Location.

    I can able to go to the File system repository and can check the properties as "Master Locations" and it will display the Location as TRIM Number. [ For ex: trim:**/rec/20642765].

    1) I am not able to find which table holding this Location info? Pls advice

    2) I can able to check the "Duplicates > Duplicates BY Location" and it will show the documents residing in CM. 

    Is there any way to get the consoldated Duplicate List against Master [ In my case TRIM ] and it will helpfult to compare before run the policy to capture for the douments not in TRIM. Pls advice