Here is a little excel file with a bit of VBA code to extract from a CMS the complete list of user and the list of groups each user is member of. Quite useful when you need to check group membership.
It’s now possible to edit the CMS data, simply by modify entries in the Excel File. You need then to export your modification back to the CMS. Modifiables column are : Login, FullName, Email, Disabled and Description.
Platform: BO XI R2 / BOXI3.1 (it’s necessary to change the libs to fits XI3.1, see below)
Version: V4
Code: VBA inside the Excel file
History:
| Version | Date | Description |
|---|---|---|
| V3 | 2008-01-17 | Added the Last Login Time data, and the possibility to modifiy the Login, FullName, Email, Disabled and Description column and sync it to the CMS. |
| V2 | 2008-01-04 | Corrected the 1000 objects limit (hard coded to 1000000 objects) ; and the FullUser parameters, add of a topic for discussion |
| V1 | 2007-07-25 | Original Release |
What is it ?
It’s simply an Excel file with a little VBA code to extract from a CMS the complete list of user and the list of groups each user is member of. Quite useful when you need to check group membership.
Information provided for each user : id, login, full name, email adress, group list, flag user activated, flag is password to change, description and last login time.
It’s now possible to edit the CMS data, simply by modify entries in the Excel File. You need then to export your modification back to the CMS. Modifiables column are : Login, FullName, Email, Disabled and Description.
Some tips
XI3 version ?
I have read that a lot of people can’t use the XLS tool on a XI3.x installation. It’s true, because th XLS file search for version 11.5(XIR2) librairies ; but there is only version 12(XI3.x) librairies. To change that, you just need to go to Visual BAsic (Alt + F11) ; go to Tools/References (or something like that, i’ve got a french version here icon_wink.gif )and change the 11.5 librairies reference to 12 librairies reference. There is about 5 BOXI librairies used :
Crystal Enterprise Framework Library
Crystal Enterprise InfoStore Library
Crystal Enterprise Plugin Manager Library
Crystal Enterprise User PlugIn
Crystal Enterprise UserGroup PlugIn
BO Librairies
An other very important point is that the BO libs must be installed on the client where you launch the macro. Typically, you just need to install the client version of BO (with Designer, Desktop Intelligence) and theise lib are automatically installed.
SQL Used
There is no way to the equivalent using simple SQL and query a special database. There is no database containing theise infos ! Today, the only way to do that is query via the SDK, using a commercial tools (there are many like the Sebastien one, which is not dedicated for that) or using the CMC…
Audit DB
Last point, i am not using the auditing database ; just the repo.
Files
BOXI UserList.xls (56.0 KB)
BOXI UserList v2.xls (60.0 KB)
BOXI UserListV3.xls (71.0 KB)
(BOB member since 2002-09-19)
(BOB member since 2007-01-18)
(BOB member since 2006-03-09)
(BOB member since 2005-03-10)
(BOB member since 2006-01-04)