QueryBuilder User/Group join

[ptc-4790284]

Greetings! I'm trying to use Query Builder to produce a list of user with their associated groups ( plus vice-versa later).

I can query users and groups individually and get results, but can't figure out how to join the tables.

Any advice is appreciated.

[pcnelson]

Following! I had the same question. The response from tech support was "It
cant be done". I don't believe them. Maybe complicated, because of multiple
group memberships, but not impossible.

[rhegde]

Ann,

Basic group membership information is stored in "membershiplink" table and
as per my knowledge, corresponding query-builder class is
"wt.org.MembershipLink".

*Note:*

- Groups can also be members of other groups. QueryBuilder might be limited
in looping recursively to find all group membership in one single go.

- WTGroups are basically used in the context of the team. Groups that are
hidden (created through contexts/teams) have "internal" flag set to "1".
Such groups are not visible to end users through user interface. Groups
that are exposed to end users have "internal" flag set to "0".


- Some group membership information is stored directly in LDAP. Some of
those groups are as below. Such membership information can not be queried
through Query-Builder (I think, this might be the limitation PTC tech is
pointing out)

Administrators

Type Administrators

Attribute Administrators

Classification Administrators

Lifecycle Administrators

Workflow Administrators

ESI Administrators

etc


--
Rochan Hegde
Productspace Solutions Inc.
Cell: 630-495-2999 x 8121


*Follow us at *
*Join Windchill360 at:

Windchill360.com

[SimonHeath]

Much of this data is not in the database but stored in the LDAP, so that is why PTC say it can't be done, however this is not strictly speaking true as query build can now call Java and there are some APIs which will interrogate the LDAP. Think about if you have an enterprise LDAP as that can return you alot of data about groups that have nothing to do with Windchill and your corporate IT will not be happy if you bombard it with heavy queries.

Simon

Wincom

[SteveVinyard]

This has been brought up before (by myself). It's done using InfoEngine. Not exactly an easy task though. Sudeep at Najanaja can probably do this for you.

[Description: C:\Documents and Settings\krista.roy\Application Data\Microsoft\Signatures\tristar_email_signature_files\image001.gif]

Steve Vinyard
Senior Solution Architect

[atre.shreyas]

Yes… Java method can be used in Report Manager/ QB to get the data from windchill DB. But I don't think it will be possible even using java method in QB to get group membership information and display in report. From QB you need to call java method for each row and also output returned by java method will displayed only in single row.

However you can use the windchill API's or Info*Engine task to retrieve group information form LDAP. if you want information in report then you can use info*engine to retrieve data and displayed it using cognos report.

Attached is the sample Java program / command line utility to print the group members

Hope this helps !!!

Thanks,

[dgraham-4]

Guys, It would not take much time at all to write a class where the admin could input a user and all groups the user is a member of would be printed out. Likewise the admin could input a group and all WTPrincipals (Users and Groups) that are in the group would be printed out. A jsp could also be created so that this could all be done from a browser. If anyone needs this drop me a line. David GrahamWindchill Developer

Please note: message attached

[MikeLockwood]

I'm REALLY surprised that this is not OTB by this release of Windchill. It's needed by everybody.

Organization, Groups has all the needed info, so clearly the system is pulling it - from LDAP.
It would seem that PTC would just need an export from this page, which eliminates any development to get the info and makes it just an export development.

[cid:image001.png@01CEF664.D4C5D320]

[bellj]

I agree. I use this data daily in my constant battle to keep permissions and user profiles in check.

I use an Oracle procedure that queries LDAP and periodically populates a materialized view with Group/User data.
Then create my own Oracle views on everything from ACLs, Groups, Documents and their CM history, Workflows, Lifecycles, etc.
Then I pull it into many, many Excel spreadsheets for analysis/further action.
Lots of moving (breakable) parts and it's not pretty but it works.

I don't use QueryBuilder for anything. A very finicky report generator.


joe bell
GSIMS Administrator
GPS Sustainment Information Management System
719-474-8899
bellj@gpssims.com<">mailto:bellj@gpssims.com>

[ddemay]

Ann,



Aside from everyone informing about java classes and issues with groups and
users, I'm not sure anyone answered your question about how to perform a
join using query builder. (Irrespective of the data being queried.) Did you
get the help you needed? Perhaps someone can explain this.





David

[ha]

Hi Ann

I have created a little example how to use Query Builder to list all users in a specifiy group. You can just extend the query so it list all groups.

If you have configured MSAD you can filter out the list of result using the wt.ufid.RemoteObjectInfo.Birth Id. This is always =1 for groups in the AdministrativeLDAP so Birthid=1

The trick is to create an "outer join" between the WTUser and WTGroup table.

I have attached the qml export of the users_groups and screenshots of the 2 query examples.

Example 1: Users in groups

Example 2: List specific MSAD informations such as Postal Address for MSAD users.

I use this query to list information's stored on a user in the corporate LDAP such as MSAD. The information is not stored in the Windchill DB and you need to use the jndiAdapter configuration to list those info. Normally you can only go to the info page for a specific user to list this, but in case you like to query the infomation for all users in your system this query is handy.

/Henrik