How to obtain a list of all users from IBM Rational Focal Point 6.X instance

Technote (FAQ)


Question

How do I obtain a list of all users from Focal Point 6.X instance?

Answer

Bring up the SQL interface in IBM Rational Focal Point by clicking Information -> SQL

In any of the text fields next to an Execute button, enter the following text and click on the corresponding Execute button:


    *
    SELECT tableidno from toc where type='allusers'

In the lower portion of the web browser window, you will get the SQL Results page. You will use the number in the result to write the next SQL query.

In the large text area of the SQL interface, enter the following text (without clicking Execute):


    SELECT a2.textvalue AS "user", a1.textvalue||'#'||a1.eida AS "id" FROM attX a1, attX a2 WHERE a1.refa=(SELECT refd FROM defX WHERE attr='loginname') AND a2.refa=(SELECT refd FROM defX WHERE attr='Title' AND midd=(SELECT mid FROM modX WHERE typem=35)) AND a1.eida=a2.eida AND length(a1.textvalue)>0

You must now exchange all occurrences of X with the number found in (3). If the number was 2, you should replace attX with att2, defX with def2, etcetera. It is very common that the number found in (3) is 1, so here follows a pre-replaced SQL query for convenience:


    SELECT a2.textvalue AS "user", a1.textvalue||'#'||a1.eida AS "id" FROM att1 a1, att1 a2 WHERE a1.refa=(SELECT refd FROM def1 WHERE attr='loginname') AND a2.refa=(SELECT refd FROM def1 WHERE attr='Title' AND midd=(SELECT mid FROM mod1 WHERE typem=35)) AND a1.eida=a2.eida AND length(a1.textvalue)>0

However the above SQL will provide a list of all users created in the Focal Point instance including the deleted users.

To know the list of users who are currently present in the Rational Focal Point instance run the below SQL query:


    SELECT a2.textvalue AS "user", a1.textvalue||'#'||a1.eida AS "id" FROM attX a1, attX a2 WHERE a1.refa=(SELECT refd FROM defX WHERE attr='loginname') AND a2.refa=(SELECT refd FROM defX WHERE attr='Title' AND midd=(SELECT mid FROM modX WHERE typem=35)) AND a1.eida=a2.eida AND length(a1.textvalue)>0 AND a1.eida not in (select eid from eleX where deleted=1)

replace all occurrences of X with the number found in (3).

    SELECT a2.textvalue AS "user", a1.textvalue||'#'||a1.eida AS "id" FROM att1 a1, att1 a2 WHERE a1.refa=(SELECT refd FROM def1 WHERE attr='loginname') AND a2.refa=(SELECT refd FROM def1 WHERE attr='Title' AND midd=(SELECT mid FROM mod1 WHERE typem=35)) AND a1.eida=a2.eida AND length(a1.textvalue)>0 AND a1.eida not in (select eid from ele1 where deleted=1)

Rate this page:

(0 users)Average rating

Document information


More support for:

Rational Focal Point
General Information

Software version:

6.4.0.1, 6.4.1, 6.4.1.1, 6.4.1.2

Operating system(s):

Linux, Solaris, Windows

Reference #:

1447655

Modified date:

2010-09-24

Translate my page

Machine Translation

Content navigation