Duplicate Documents in OTCS

Hello OTCS Community,

I'm looking for a way to determine if there are any duplicate documents with a Content Server system. I would define duplicate as: Same Name and Same File Size.

Looking through old forum posts I see that I am not the first person to ask this question. Unfortunately reading through the posts it seems that not satisfactory answer has been posted.

My DB platform is MS SQL.

The ideal report would show:
Name, ObjectID1, ObjectID2, ... ObjectIDn (where is the number of duplicates)

The document name and the object ID of each of the offending instances.
Unfortunately I cannot figure out the complex relationship between DTree, DVersData and the SQL statement to produce a meaningful result.

I was disappointed to see that this report is not part of the standard reports that ship with Content Server v10.5.

The best I have been able to come up with is:

SELECT Name, COUNT(Name) AS NumberOfTimes
FROM DTree
WHERE SubType = 144
GROUP BY Name HAVING (COUNT(Name)>1)
ORDER BY Name DESC

This report returns the number of documents that have the same name and the number of times the name is used. It does NOT compare the filesize at all, or compare old versions of documents, etc.

Has anyone found a better solution to this expected problem with a Document Management system?

Regards,
-MC

Find more posts tagged with

Comments

Greg Griffiths

Mike,

if you ask your OT account team they will be able to provide an NDA for you to sign, after which you can get the schema details. There is a supporting document available at Livelink_Schema_Companion_Reference_Guide which may also help.

Mike Clarke

I am very well versed in Content Server schema. That isn't the problem.

Regards,
-MC

Mike Clarke

I have continued to work on this problem. The best I can do is come up with two queries:
Query 1 identifies duplicates based on the name and the file size.
Query 2 takes the file name (identified in query 1) and gives you all the object ids of the offenders.

Here are the queries:

Query 1:

SELECT DTree.Name, DVersData.DataSize
FROM DTree INNER JOIN DVersData ON DTree.DataID = DVersData.DocID
WHERE DTree.SubType = 144
GROUP BY DTree.Name, DVersData.DataSize
HAVING COUNT(*) > 1

Query 2:

SELECT DTree.Name, DVersData.DocID, DVersData.Version, DVersData.DataSize
FROM DVersData INNER JOIN DTree ON DTree.DataID = DVersData.DocID
WHERE FileName ='Test_Doc.docx'
ORDER BY DVersData.DataSize;

Somebody with more time, and more skill with SQL could combine the two to make a comprehensive report.

Regards,
-MC

Colin_Schmidt

A simple change. I tried your SQL and got some numbers and then added a reference to the DVERSDATA. I checked those numbers and they seem to match. The code below matches on name and size. There was a document labeled "test" and it had 4 different lines for the file name because there were 4 different, but matching file sizes where the count > 1.

SELECT dt.Name, dv.datasize, COUNT(Name) AS NumberOfTimes

FROM DTree dt

join dversdata dv on dt.dataid = dv.docid and dt.versionnum = dv.version

WHERE dt.SubType = 144

GROUP BY dt.Name, dv.datasize HAVING (COUNT(dt.Name) > 1)

ORDER BY dt.Name DESC

Colin J.

On Thu, Mar 12, 2015 at 8:07 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Duplicate Documents in OTCS Posted bygreg.griffiths@cgi.com (Griffiths, Greg)On 03/12/2015 08:04 PM

Mike, if you ask your OT account team they will be able to provide an NDA for you to sign, after which you can get the schema details. There is a supporting document available at Livelink_Schema_Companion_Reference_Guide which may also help.
[To post a comment, use the normal reply function]
Topic: Duplicate Documents in OTCS
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Lenny_Walker

Hi Mike,

What other parameters did you set up to make these run? I get a blank return. What did you set up in the display columns etc. to show results?

Thanks!