Intralink SQL: User and Group Folder Authorizations

Intralink stores folder authorizations in four Oracle tables. Two for user authorizations (PDM_USERFOLAUTH and PDM_USERFOLRLAUTH) and two for group authorizations (PDM_USERGRFOLAUTH and PDM_USERGRFOLRLAUTH).

The PDM_USERFOLAUTH and PDM_USERGRFOLAUTH tables contain folder role authorizations. The PDM_USERFOLRLAUTH and PDM_USERGRFOLRLAUTH tables contain folder authorizations by release level.


Query for user folder role authorizations:

-- user folder auth
--
set linesize 135
col folpath format a60
col userexpname format a12
col rolename format a15
--
select 
  FOLPATH, 
  USEREXPNAME, 
  ROLENAME 
from 
  pdm.PDM_USERFOLAUTH ufa, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USER usr
where
  role.ROLEID=ufa.ROLEID and 
  ufa.FOLID=fol.FOLID and 
  ufa.USERID=usr.USERID
;

 


Query for user folder/releaselevel role authorizations:

-- user folder/releaselevel auth
--
set linesize 135
col folpath format a60
col userexpname format a12
col rlname format a15
col rolename format a15
--
select 
  FOLPATH, 
  USEREXPNAME, 
  RLNAME, 
  ROLENAME 
from 
  pdm.PDM_USERFOLRLAUTH ufrla, 
  pdm.PDM_RELEASELEVEL rl, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USER usr
where
  role.ROLEID=ufrla.ROLEID and 
  ufrla.RLID=rl.RLID and 
  ufrla.FOLID=fol.FOLID and 
  ufrla.USERID=usr.USERID
;

 


Query for group folder role authorizations:

-- group folder auth
--
set linesize 135
col folpath format a60
col rolename format a15
col grexpname format a25
--
select 
  FOLPATH, 
  GREXPNAME 
  ROLENAME, 
from 
  pdm.PDM_USERGRFOLAUTH gfa, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USERGROUP grp 
where
  role.ROLEID=gfa.ROLEID and 
  gfa.FOLID=fol.FOLID and 
  gfa.GRID=grp.GRID 
;

 


Query for group folder/releaselevel role authorizations:

-- group folder/releaselevel auth
--
set linesize 135
col folpath format a60
col grexpname format a25
col rlname format a15
col rolename format a15
--
select 
  FOLPATH, 
  GREXPNAME, 
  RLNAME, 
  ROLENAME 
from 
  pdm.PDM_USERGRFOLRLAUTH gfrla, 
  pdm.PDM_RELEASELEVEL rl, 
  pdm.PDM_ROLE role, 
  pdm.PDM_FOLDER fol, 
  pdm.PDM_USERGROUP grp
where
  role.ROLEID=gfrla.ROLEID and 
  gfrla.RLID=rl.RLID and 
  gfrla.FOLID=fol.FOLID and 
  gfrla.GRID=grp.GRID
;

 


Putting it all together

To streamline these queries, unions can be used to combine the output into one report. In the following example, user and group names are reported under the 'ID' column. The final where clause can be used to filter for a specific user or group. Leave it out to report authorizations for everyone.

The report is also enhanced in that the order of the release levels in the output matches the output one would see in the folder authorization window in Intralink Admin.

The syntax is somewhat inefficient because it is setup to allow outer joins based on folders (see end of article). However, even for very large numbers of folders, user, and groups, the combined query is still pretty fast.

-- user & group auth
--
set linesize 200
column folpath format a70
column rolename format a20
column rlname format a15
column id format a25
--
select FOLPATH,id,rlname,rolename,rlseqnum
from
  (
    select
      folpath,
      (select userexpname from pdm.pdm_user where userid=ufa.userid) as id,
      '' as rlname,
      to_number('') as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=ufa.roleid) as rolename
    from
      pdm.pdm_userfolauth ufa,
      pdm.pdm_folder fol
    where
      ufa.folid=fol.folid
  UNION
    select 
      FOLPATH,
      (select userexpname from pdm.pdm_user where userid=ufrla.userid) as id,
      (select rlname from pdm.pdm_releaselevel where rlid=ufrla.rlid) as rlname,
      (select rlseqnum from pdm.pdm_releaselevel where rlid=ufrla.rlid) as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=ufrla.roleid) as rolename
    from 
      pdm.PDM_USERFOLRLAUTH ufrla, 
      pdm.PDM_FOLDER fol
    where
      ufrla.FOLID=fol.FOLID
  UNION
    select
      folpath,
      (select grexpname from pdm.pdm_usergroup where grid=ugfa.grid) as id,
      '' as rlname,
      to_number('') as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=ugfa.roleid) as rolename
    from
      pdm.pdm_usergrfolauth ugfa,
      pdm.pdm_folder fol
    where
      ugfa.folid=fol.folid
  UNION
    select 
      FOLPATH,
      (select grexpname from pdm.pdm_usergroup where grid=gfrla.grid) as id, 
      (select rlname from pdm.pdm_releaselevel where rlid=gfrla.rlid) as rlname,
      (select rlseqnum from pdm.pdm_releaselevel where rlid=gfrla.rlid) as rlseqnum,
      (select rolename from pdm.pdm_role where roleid=gfrla.roleid) as rolename
    from 
      pdm.PDM_USERGRFOLRLAUTH gfrla, 
      pdm.PDM_FOLDER fol
    where
      gfrla.FOLID=fol.FOLID
  )
where
  id like '%smith%'
order by folpath,id,rlseqnum
;

 


Outer Joins for More Information

To show all folders, regardless of whether they contain any authorization settings, add the following outer join syntax to the above:

      ...folid(+)=fol.folid

 

There will be some minor duplications, but it does give a good sense of where the authorizations are set in your folder hierarchy.

Intralink SQL: Searching Based on Attribute Values

Searching based on attribute values can be performed using Oracle SQL, but a little investigation work needs to be done beforehand.

Attributes and their characteristics are contained in the pdm.PDM_CLASSATTR table in the Oracle database. The first step that needs to be done is to identify the 'id' number of the attribute.

The following query shows, for the "file based" attributes, the 'id', the name, and the 'type'. Also included is the CLADEFVALU column which would show any default values for the attributes.

-- show file based attributes
--
select CLAID,CLANAME,CLADEFVALUE,CLATYPE
from pdm.PDM_CLASSATTR
where CLAUSERDEF=1 and CLAFILEBASED=1
order by CLANAME;

     CLAID CLANAME              CLADEFVALU    CLATYPE
---------- -------------------- ---------- ----------
       123 Desc                                     5
       234 Matl                                     5
       345 Mc_Config                                5
       456 Mc_Errors                                2
       567 Mc_Mode                                  5
       678 Model_Check                              5
       789 Weight                                   3

 

The CLATYPE numbers correspond to these data types:

• 2: Integer
• 3: Real
• 4: Boolean
• 5: String

If the 'Desc' attribute is needed, we take the CLAID value and prepend "UDA" to form the column name in the pdm.PDM_PRODUCTITEM_VUDA1 table. In this example, the Desc attribute corresponds to the "UDA123" column.


Putting this into an example, the following query searches for Intralink PIV's with the Desc attribute matching "...some string...".

-- search based on attribute values 
-- 
column piname format a35 
column UDA123 format a40 
column REVVER format a7 

SELECT 
piv.pivid, PINAME, PIVREV'.'PIVVER as REVVER, UDA123 
FROM 
PDM.PDM_PRODUCTITEM pi, 
PDM.PDM_BRANCH br, 
PDM.PDM_PRODUCTITEMVERSION piv, 
pdm.PDM_PRODUCTITEM_VUDA1 vuda 
WHERE 
pi.PIID = br.PIID 
AND br.BRID = piv.BRID 
AND piv.pivid = vuda.pivid 
AND UDA123 like '%some string%' 
;

 


For an exact match, use this as the last condition in the "WHERE" clause instead:

AND UDA123 = 'some string'

 

Intralink SQL: Monitoring User Passwords Part 2

Continued from Intralink SQL: Monitoring User Passwords Part 1

The Trigger:

Finally, we're ready for the trigger itself. It's a bit long and I'll try to explain it in sections. The main three sections define the trigger name and when it is executed, declarations of cursors and variables, and the trigger body.

In this example, 'create or replace' allows us to create a new trigger or replace an existing trigger, without having to first drop the trigger. 'before update of userpassword' indicates that the trigger runs when the userpassword field of pdm.PDM_USER is changing, but not if the email address is changing. We are capturing each row change as it happens using 'for each row'.

In the declare section, two cursors and two variables are defined. The cursors allow looping through (potentially) many values in a table. The cursors here are used for looping through the old passwords less than 180 days old, and all bad passwords. The cursors are limited to rows related to the user undergoing the password change.

In the body of the trigger, virtual tables ":old" and ":new" are often used. As you might suspect, they represent the old values of the table row and the new values of the table row. The trigger can see both and decide upon what action to take.

Much of the trigger below is wrapped in an if-then block. This block determines whether the user being changed is the same as the user doing the change. Only an admin can change another user's password. The 'if' statement figures out whether the user being changed is an admin, and if not, applies the rules of the trigger. If this 'if' statement (and its 'endif') are removed, the trigger applies to all accounts, even admin accounts.

The next if-then block prevents the user from reusing the current password (old password = new password) with 'raise value_error'. This introduces a fictitious error, which stops any further execution. The password change is denied. The user will get a cryptic error message, and there is no way to better inform them why. The error message is semi-obvious for most users that the password did not get changed.

The next two chunks of code open the old password and bad password cursors. The trigger loops through each row in each cursor looking for values in order to 'raise value_error'. The code to process the cursors is nearly identical, even though the cursors are a little different.

The insert statement writes the necessary information to the tracking table. It mostly uses the new table row values, except for the ID which is pulled from the next available value of the sequence. For this trigger, regardless of admin change or not, all successful password changes are recorded to the tracking table.

To create the trigger, copy and paste everything from 'create' down through and including 'run;' into an sqlplus session. 'run' is not actually part if the trigger. It is needed by oracle to compile the trigger. It doesn't actually run the trigger at that time.

create or replace trigger pdm.custom_pwchange_track_trg
before update of userpassword on pdm.PDM_USER
for each row
declare
cursor old_passwords(uid int) is
select userpassword from pdm.custom_pwchange_track
where userid=uid and modifiedon>=(sysdate-180);
old_pw old_passwords%rowtype;
cursor bad_passwords(uid int) is
select userpassword from pdm.custom_bad_passwords
where userid=uid;
bad_pw bad_passwords%rowtype;
begin
--
-- if a non-admin user is changing their own password, apply rules
if (:new.username = :new.modifiedby AND :new.usertype != 1) then
--
-- If new password is old password, raise exception
if (:new.userpassword = :old.userpassword) then
raise value_error;
end if;
--
-- Read bad passwords, make sure they are not used
open bad_passwords(:new.userid);
loop
fetch bad_passwords into bad_pw;
exit when bad_passwords%notfound;
if bad_pw.userpassword = :new.userpassword then
raise value_error;
end if;
end loop;
close bad_passwords;
--
-- Read old password values for this user, make sure
-- they do not reuse a password
open old_passwords(:new.userid);
loop
fetch old_passwords into old_pw;
exit when old_passwords%notfound;
if old_pw.userpassword = :new.userpassword then
raise value_error;
end if;
end loop;
close old_passwords;
--
end if;
--
-- Insert new password into tracking table
insert into pdm.custom_pwchange_track
(pwcid, userid, userpassword, modifiedby, modifiedon)
values
(pdm.custom_pwchange_track_seq.nextval, :new.userid,
:new.userpassword, :new.modifiedby, :new.modifiedon);
end;
.
run;

When the trigger is in place, if there is some form of error, this command will help (though only a little):

show errors trigger pdm.custom_pwchange_track_trg;

These commands can be used to report information about the trigger:

column OWNER format a8
column table_name format a30

select OWNER, TRIGGER_NAME, TRIGGER_TYPE,
TABLE_OWNER'.'TABLE_NAME as table_name, status
from
all_triggers
where
TRIGGER_NAME like 'custom_PWCHANGE%'
;

Should the trigger need to be disabled or re-enabled use these commands:

alter trigger pdm.custom_pwchange_track_trg disable;
alter trigger pdm.custom_pwchange_track_trg enable;

You may want to purge the old password table of old passwords, without dropping the table altogether. This is not absolutely necessary as the trigger will ignore password more than 180 days old.

To delete old passwords use this command:

delete from pdm.custom_pwchange_track where modifiedon<sysdate-180;

Next time: Using Intralink Scripting to Change Passwords

Intralink SQL: Monitoring User Passwords Part 1

PTC provides very little control over passwords, and unfortunately expiration dates cannot be set from within the GUI. However, it can be accomplished in the oracle backend using triggers. Not with C based Intralink triggers (you can't trigger on a password change), but with PL/SQL based oracle triggers.

The basic concept is to setup a trigger that monitors changes to the password column of the user table (PDM_USER). Generally, the trigger writes the username, date, and optionally the "encrypted" password to a separate tracking table.

In the simplest application, an sql query on the tracking table would report users who have not changed their password within the required period. A cron job or scheduled task can use this query to send nag messages to users. If desired, this process could update the user's password field in the PDM_USER table to a value that would effectively disable the account.

A more complex trigger can prohibit password reuse, disallow known bad passwords, purge old "good" passwords from the database, and potentially send emails itself.

In this two part example, I will show you how to setup a trigger that tracks passwords, prohibits "known bad passwords", and prohibits some password reuse. 180 days is used here as a threshold, but any numeric values can be used, just be consistent.


Tracking Table:

You need a table to store the user/password/date information. The following code will create a table using datatypes from the PDM_USER table itself in the form of a query.

create table pdm.custom_pwchange_track unrecoverable as
select
userid as pwcid,
userid,
userpassword,
modifiedby,
modifiedon
from
pdm.PDM_USER
where
userid=-1
;

Even though the query doesn't return any values, it does provide a table structure with which to build a table. By matching datatypes with the PDM_USER table, we don't need to worry about the data size of the columns in the new table because they will be the same.


The table columns and sizes can be verified with 'describe':

describe pdm.custom_pwchange_track;

Here are a few examples of queries to get useful info:

-- set column widths (for two queries below)
--
column UserName format a15
column UserPassword format a15
column ModifiedBy format a12
column ModDateTime format a20
column lastchange format a20


-- report password history, all users
--
select PWCID, a.userid, username, a.userpassword, a.MODIFIEDBY,
to_char(a.modifiedon,'DD-MM-YY HH:MI:SS') moddatetime
from pdm.custom_pwchange_track a, pdm.pdm_user b
where a.userid=b.userid;


-- report users who have not changed their password in 180 days
--
select username,
to_char(max(a.modifiedon),'YY-MM-DD HH:MI:SS') lastchange
from pdm.custom_pwchange_track a, pdm.pdm_user b
where a.userid=b.userid
group by username having max(a.modifiedon)<sysdate-180;

Bad Password Table:

The bad password table concept is a little tricky to implement. Since the password is encrypted, the bad encrypted passwords would need to be stored for each user. A "new user" script or process, could go through the motions of changing the new user's password to a list of bad passwords, before changing to the password given to the user.

With the tracking table and trigger in place, the bad passwords can be pulled from the tracking table and inserted into the bad password table. As I said, it's a little tricky, but it can be done without too much effort. Might make for a big table if you get too strict about "bad" passwords.


Code to create the table, again, based on a query:

create table pdm.custom_bad_passwords unrecoverable as
select
userid as bpid,
userid,
userpassword
from
pdm.PDM_USER
where
userid=-1
;

Commands to verify the table:

describe pdm.custom_bad_passwords;

select * from pdm.custom_bad_passwords;


-- Take values from old password table for user 'fred' and insert
-- into bad password table
--
insert into pdm.custom_bad_passwords (bpid, userid, userpassword)
select pwcid, userid, userpassword from pdm.custom_pwchange_track
where userid=(
select userid from pdm.pdm_user where username='fred'
)
;

Sequence for unique IDs:

Typically a well designed database uses sequences to generate unique integer ids. This allows differentiation between each record in a table. In this case, it is not absolutely necessary, because we are not linking multiple tables together, but it's generally a good thing and very easy to do.

Code to create the sequence:

create sequence pdm.custom_pwchange_track_seq
increment by 1
start with 1
;

Commands to verify the sequence:

select SEQUENCE_OWNER,SEQUENCE_NAME,
MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER
from all_sequences
where SEQUENCE_NAME='custom_PWCHANGE_TRACK_SEQ';

Next time, implementing the trigger in Intralink SQL: Monitoring User Passwords Part 2.

Intralink SQL: Changing Revision/Version with Oracle SQL

A PTC/User post of mine from April 2003

"Mongilio, Michael" wrote:

Does anyone know if it is possible to "successfully" change an objects revision to an earlier revision through Oracle? I have an object with two revisions "F" and "G" in the database. They should be "B" and "C". It would be a real hassle to delete both revisions and put them back in (especially since they are instances on a family table).

The following is a procedure to do what you asked for. The changes may work successfully, or they may not. Use them at your own risk. Definitely try them on a test server before messing with your production server.

To see all revisions (and all branches) of 'abc.prt':

set linesize 120
column PINAME format a35
column BRPATH format a20
column PIVREV format a6

select
  piv.PIVID,
  piv.PIVCLASS,
  pi.PINAME,
  br.BRPATH,
  piv.PIVREV,
  piv.PIVVER
from
  pdm.PDM_PRODUCTITEMVERSION piv,
  pdm.PDM_BRANCH br,
  pdm.PDM_PRODUCTITEM pi
where
  pi.PIID=br.PIID and
  piv.BRID=br.BRID and
  pi.PINAME='abc.prt'
;

Note: The value of PIVCLASS is 0, 1, or 2

• 0: Non family table objects
• 1: Instances
• 2: Generics

To change the 'main' branch Revision/Version of 'abc.prt' from
'B.0' to 'A.3':

update pdm.PDM_PRODUCTITEMVERSION
set
  PIVREV='A',
  PIVVER=3,
  MODIFIEDON=sysdate
where
  PIVID IN (
    select
      piv.PIVID
    from
      pdm.PDM_PRODUCTITEMVERSION piv,
      pdm.PDM_BRANCH br,
      pdm.PDM_PRODUCTITEM pi
    where
      piv.BRID=br.BRID and
      pi.PIID=br.PIID and
      pi.PINAME='abc.prt' and
      piv.PIVREV='B' and
      piv.PIVVER=0 and
      br.BRPATH='main'
  )
; 

In this example, the MODIFIEDON column is updated as well as the Revision/Version. If this does not occur, Intralink clients that have the revision cached (displayed in a browser window) get very confused and generate error messages, since they don't know to update from the Commonspace. The column is a 'freshness' timestamp that the client uses to determine whether its cached data is current or out of date.

Also, be sure that you are updating the correct branch. You may have multiple files at 0.0, but only one will be in the 'main' branch.

I'm sure what I am asking is not recommended, but...

Highly not recommended, but possible. If you are not careful, it is easy to make two revisions both have the same Revision/Version (i.e. both could have 2.1), to change the order (i.e. 4.0 becomes 3.6 and 4.1 becomes 3.5), and to set a Revision to something that is not in the list of valid revisions. Trying to do any of these is a really bad idea.

Changing these values also causes some issues with workspaces that have the files checked-out. The revision info in the workspace will no longer correspond to the revision info for the same version in the commonspace, unless the file is checked-out again.

Some unusual conflicts may be produced upon check-in, as later versions may now be present in the commonspace even though the workspace version really is the latest. These conflicts can be overridden with apparently no problems.


As long as you're careful, the procedure should not create any major problems.

Intralink: Who's Logged In?

A PTC/User post of mine from May 2002:

Use this sql query as a privileged user to list intralink users who have logged into the dataserver:

  col un format A15 heading 'User Name'
  col mn format A15 heading 'Host Name'
  col lt format A10 heading 'Login Date'
  col lc format 999999999 heading 'Last Cmd (Sec)'
  select
    OSUSER un,MACHINE mn,STATUS,TERMINAL,LOGON_TIME lt,LAST_CALL_ET lc
    from v$session where SCHEMANAME='PDM';

The 'Status' column will show 'ACTIVE' or 'INACTIVE', with 'ACTIVE' indicating that there is ongoing sql activity (i.e. Search, Checkout, Checkin, etc.). The 'Last Cmd...' column shows the number of seconds since the last sql command issued by the client. It's only updated every three seconds.

An entry will appear immediately when the user logs in, and will disappear immediately when the user logs out. The entry will remain even if the client license on your FlexLM license server has timed out.

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
;

Orphaned Instances in Intralink Commonspace

A PTC/User post of mine from April 2003:

After noticing a few family table generics where instances were removed or omitted from the table in Pro/Engineer, I wrote the following sql query to find others in the Intralink Commonspace.

The logic works like this: look up the latest version of an instance name on the main branch, figure out the corresponding generic, and output if the associated generic is not the most recent generic.

If the most recent instance revision does not match up with the most recent generic revision, then the instance has been 'orphaned'. The output includes the revision and version of the instance and its associated generic.

set linesize 120
column INSTANCE format a35
column GENERIC format a35
column PIVREV format a6
column PIVVER format 99999

select
pii.PINAME as INSTANCE,
pivi.PIVREV,
pivi.PIVVER,
pig.PINAME as GENERIC,
pivg.PIVREV,
pivg.PIVVER
from
pdm.PDM_PRODUCTITEM pii,
pdm.PDM_BRANCH bri,
pdm.PDM_PRODUCTITEMVERSION pivi,
pdm.PDM_GENINSREL gis,
pdm.PDM_PRODUCTITEMVERSION pivg,
pdm.PDM_BRANCH brg,
pdm.PDM_PRODUCTITEM pig
where
bri.BRPATH='main' and
pii.PIID=bri.PIID and
bri.BRID=pivi.BRID and
bri.BRLASTVERID=pivi.PIVID and
pivi.PIVID=gis.INSTPIVID and
gis.GENPIVID=pivg.PIVID and
pivg.BRID=brg.BRID and
brg.PIID=pig.PIID and
brg.BRLASTVERID!=pivg.PIVID
order by GENERIC, INSTANCE
;