Any thoughts on a better report on broken links?

We have a request to show all broken links in Livelink, my initial reaction is this is easy, and handed to us with the "My Bad Aliases and Generations" LiveReport.However after investigating it doesnt show items pointing to objects Recycle Bin which to most users is the same as deleted and can be considered broken.Is there a way to include parent objects in the recycle bin in the report?

Find more posts tagged with

Comments

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

The current SQL used by the "My Bad Aliases and Generations" report is:

Select * from DTree where (%1 OR %2) and %3 and (OriginDataID NOT IN (select DataID from DTree))

%1 = Filter Shortcut
%2 = Filter Generations
%3 = Filter Permissions

The query checks to see if the item referred to by the alias/generation, i.e. the OriginDataID, is not present in DTree. We can enlarge the scope of the query to include cases where the item referred to by the alias/generation is in the Recycle Bin. This turns out to be relatively straightforward.

First, we need the DataID of the Recycle Bin Volume. To get it, run this SQL query:

select DataID, Name from DTree where subtype=3030

In my investigation, I found items in the Recycle Bin Volume have an ownerID value that is the negative DataID of the Recycle Bin Volume returned by the above query.

For example, 40528461 was the DataID for the Recycle Bin on my system, and items in the Recycle Bin had a DTree.OwnerID value of -40528461

As a preliminary test, I then modified the "My Bad Aliases and Generations" query to look _only_ at aliases/generations pointing to items in the Recycle Bin. The "abs" function was used to handle the negative value of the DTree.OwnerID field:

Select * from DTree where (%1 OR %2) and %3 and ( OriginDataID IN (select DataID from DTree where abs(OwnerID)=(select DataID from DTree where SubType=3030)))

That worked. I then copied the "My Bad Aliases and Generations" report and modified the copy to include the above changes. The final query was:

Select * from DTree where (%1 OR %2) and %3 and ( (OriginDataID NOT IN (select DataID from DTree)) OR OriginDataID IN (select DataID from DTree where abs(OwnerID)=(select DataID from DTree where SubType=3030)))

This copy of the report now returns bad aliases and generations, and considers aliases/generations whose original items are in the Recycle Bin as "bad" also.

Of course, I would highly recommend testing the above queries yourself prior to trusting them to a production system.

Let me know if that helps...

- Bhupinder
----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Senior Systems Analyst, Information Technology
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, November 07, 2008 10:51 AM
To: eLink Recipient
Subject: Any thoughts on a better report on broken links?

Any thoughts on a better report on broken links?

Posted by timothy.hunter.ctr@wpafb.af.mil (Hunter, Tim ) on 11/07/2008 10:48 AM

We have a request to show all broken links in Livelink, my initial reaction is this is easy, and handed to us with the "My Bad Aliases and Generations" LiveReport.
However after investigating it doesnt show items pointing to objects Recycle Bin which to most users is the same as deleted and can be considered broken.
Is there a way to include parent objects in the recycle bin in the report?

Tim_Hunter

This is exactly what I was looking for, the key for me was the query to get the ID of the recycle bin. Thanks!