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.
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.
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 !!!
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.