How To retrieve list of Users and Groups on BI BOBJ 4.1

[former_member242731]

Hi

I need to extract from the BOBJ 4.1 sql server back end  a list of users and the Groups currently on the System to show them in report webi.

Please could anyone tell me about the way extract them from the Repository Database or how to extract the through sdk and insert the to a database.

Thanks

[DellSC]

You have to use one of the SDKs to do this.  The relevant information in the CMS database is stored in a proprietary format in a BLOB field so there's no way to query it directly.

How you're going to do this depends on which SDK you use.  The basic logic you'll use goes like this: 

- Log in to the BO system to get a session (IEnterpriseSession for Java, EnterpriseSession for .NET)

- Query for users: 

Select SI_NAME, SI_USERFULLNAME, SI_ID, SI_NAMEDUSER, SI_LASTLOGONTIME, SI_DESCRIPTION, SI_ALIASES, SI_EMAIL_ADDRESS From CI_SYSTEMOBJECTS Where SI_KIND='User' Order by SI_NAME

- Go through the list of users and add/update them to a User table in the  database.  This makes sure that you have ALL of the users, regardless of whether they're assigned to any groups other than "Everyone".

- Query for user groups:

Select SI_ID, SI_NAME, SI_REL_USERGROUPS, SI_USERGROUPS, SI_GROUP_MEMBERS,  SI_SUBGROUPS from CI_SYSTEMOBJECTS where SI_KIND = 'UserGroup' order by SI_NAME

- Go through the list of groups and do the following:

     1.  Add the group to a UserGroup table in the database.

     2.  Walk through the user list (IUserGroup.getUsers for Java, UserGroup.Users for .NET).  The user list is a list of the SI_ID values for each user.  Add the SI_ID of the UserGroup and the SI_ID of the user to a GroupMembership table in the database.

- Log off from the BO system.

-Dell