Intralink Commonspace Search for Locked Objects

A PTC/User post of mine from Sep 2002:

The official way:

Check out http://www.ptc.com/cs/tan/106363.htm for a TAN describing how to add a search for Commonspace Status (aka Status Description). Also check out http://www.ptc.com/cs/tan/106893.htm for another TAN describing how to add additional filtering options using a similar workaround.

The trick for Locate is to define and save a search for Release Level, then edit your ilprefs.txt file to change the search to Status Description. In the ilprefs.txt file the 'Operand' for a Release Level search is 7. This needs to be changed to 14 for a Status Description search. Set the 'Value' field to be 'Lock*' to search for all locked objects. Set it to '*username*' for objects locked by or shared with a user and also those with Intent to Modify.

After figuring out how to get it to work consistently, I'm beginning to see why searches for Status Description were 'omitted'. I think the developers found that this type of search didn't work right and excluded it from the gui. It seems as if Locate must find everything first, and then it can search for a specific value.

Here is a method that works every time, unfortunately it also takes a really, really long time:

1. Make sure 'Status Description' is a displayed column in the results section.
2. Search for: Status Description='*' and Latest Revision/Version. Bypassing this step seems always to produce no results. This isn't needed for every search, but it is needed at least once per session.
3. Search for: Status Description='*something*'. Where 'something' is what you were looking for in the first place.

The unofficial way:

If you want much faster results (1-2 seconds), you can use the following SQL query to show locked objects and the user who owns the lock. Replace 'joe' with the user name, or use the '%' wildcard (slower). The 'set' and 'col' statements are optional, but help clean up the output.

set linesize 200
col username format A15 heading 'User Name'
col PINAME format A35 heading 'Object Name'
col MODIFIEDON format A15 heading 'Date Changed'
col FOLPATH format A75 heading 'Folder Name'

select
  usr.username,
  pi.piname,
  brlock.MODIFIEDON,
  fld.folpath
from
  pdm.PDM_BRLOCK brlock,
  pdm.PDM_BRANCH branch,
  pdm.PDM_PRODUCTITEM pi,
  pdm.PDM_USER usr,
  pdm.PDM_FOLDER fld
where
  brlock.brid=branch.brid
  and branch.piid=pi.piid
  and brlock.userid=usr.userid
  and pi.folid=fld.folid
  and usr.username like 'joe'
;

To see users with whom a lock is shared and the corresponding objects, use this SQL query. Replace 'joe' with the user name, or use the '%' wildcard.

set linesize 200
col username format A15 heading 'User Name'
col PINAME format A35 heading 'Object Name'
col FOLPATH format A75 heading 'Folder Name'

select
  usr.username,
  pi.piname,
  fld.folpath
from
  pdm.PDM_BRLOCK brlock,
  pdm.PDM_BRLOCKSHARED brlockshared,
  pdm.PDM_BRANCH branch,
  pdm.PDM_PRODUCTITEM pi,
  pdm.PDM_USER usr,
  pdm.PDM_FOLDER fld
where
  brlock.brid=branch.brid
  and brlockshared.brlid=brlock.brlid
  and branch.piid=pi.piid
  and brlockshared.userid=usr.userid
  and pi.folid=fld.folid
  and usr.username like 'joe'
;

If you use this query and you get results that have old dates for TXNMSTARTTIME and/or TXNMFINISHTIME, then chances are you have objects that are Locked by System or some other similar problem.

set linesize 200
col username format A15 heading 'User Name'
col PINAME format A35 heading 'Object Name'
col FOLPATH format A75 heading 'Folder Name'

select
  usr.username,
  pi.piname,
  txnmaster.TXNMSTARTTIME,
  txnmaster.TXNMFINISHTIME,
  fld.folpath
from
  pdm.PDM_TXNMASTER txnmaster,
  pdm.PDM_BRLOCK brlock,
  pdm.PDM_BRANCH branch,
  pdm.PDM_PRODUCTITEM pi,
  pdm.PDM_USER usr,
  pdm.PDM_FOLDER fld
where
  txnmaster.txnmid=brlock.txnmid
  and brlock.brid=branch.brid
  and branch.piid=pi.piid
  and txnmaster.userid=usr.userid
  and pi.folid=fld.folid
;