BOXI UserList & Group Extraction

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)

Simple query to extract current users
BOXI UserList & Group Excel Export(Wired)
OS Users and Groups Report
Like to list all users and email address (Query Builder ?)
List of Users
how to get users list by vba macro
BOXI UserList & Group Excel Export
Full Name From Audit Database Tables
How to get the users email from Query Builder
XIR2 Query Builder Admin Reports
User ID's and Group Rights
Finding all enabled users in XI R2
Tool to count total webi users , crystal users etc.
BI40 Users and Groups Lists
Export a List of Users?
Script for disabling user accounts in CMC
List of bo users
How to export BO user list into EXCEL from CMC?
System generated List of BO users and their respective user
How to display BO User Name
Macro for get user and group details
Querybuilder: Find users who are only in "Everyone&quot
View email addresses?
Audit Actions
SDK Query Tool?
Business Objects Users
Query Builder
Exporting User List to Excel or Text
List of users in each group.
Want all users in the system
Is there a way I can export Group/Users/Objects to Excel
Bulk Add Users
Security CMC - setting up lots of users into groups
Moving the User list from CMC to Excel or to any File
Geeting list of users groupwise
User/Universe relationship
query to list all my BOBJ users?
how can I get list of all BO users in BOXIR2?
Repository Tables
how I can see the repository BO XI R2 on file system ?
how to get the user's group
How to create Outlook Address list report
Creating reports from CMS Repository XIR2
Users and Groups List
BOXI userlist and groups
Alphabetized Index to BOB's Downloads
export list of users?
BO XI R2: generating report about existing user, groups, ...
Report of User Details from CMC
Need to find when user has created
BO XI R2: WebI/DeskI report showing all users
Removing all of the unneeded users from BOXIR2
BOBJ BI4.2 User List and Group Extraction (via SPA)
List of users in a particular group?

Updated with version 2.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

Perfect - works a treat and really useful!!


Diane1969 :uk: (BOB member since 2007-01-18)

Very useful, thanks. Is it possible to add a column to determine a user’s last login date?


surreal :us: (BOB member since 2007-08-29)

Updated with V3


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

This is great. Thanks so much!


surreal :us: (BOB member since 2007-08-29)

I have just tested it !

Great, thanks very much !


Arnaud Delalande :fr: (BOB member since 2006-03-09)

Everything was working fine but I installed CR 10 and now when I try to retrieve data from CMS I get an error stating ‘VBAProject - 13: Type mismatch 1000013’. Anyone know what is causing this error and how to fix it? Thanks.


surreal :us: (BOB member since 2007-08-29)

Thanks for sharing!!!


kmspsu93 :us: (BOB member since 2006-04-06)

Hi,
Tried this and its great. Is there any tool present which can give the entire security snapshot of a BO system?

Thanks & Regards,
Ashish K


ashishk :india: (BOB member since 2005-03-10)

Hi,

I would really love to use this tool, however when I connect I receive the following error message -
“VBAProject - 13: Type mismatch 1000013”

Any suggestions?

Ruan


hbunicorns :us: (BOB member since 2007-05-31)

Hi there - this looks like a great tool, but I’m getting the following error when I try to connect:

CrystalEnterprise12.SessionManager1 - 2147024891: Access Denied - You cannot logon to an older version of the CMS. 1000440

We are on BO XI R2, SP2.7 - we only use BO, no crystal.

Any ideas on this?


nyck944 :us: (BOB member since 2004-10-20)

It looks like you need to have Crystal Reports installed in order for this to work. The VBA code references several Crystal Reports files.

I was going to post a screenshot of the references but I can’t figure out how. Hope this helps.


JohnBClark :us: (BOB member since 2007-03-27)

I get an error
CrystalEnterprise115.InfoStore.1 --2137210999: Active Directory Authentication failed to get the active Directory groups for the acount with ID “841250165026213482174"ADEDUS”. Please make sure this account is valid and belongs to an accessible domain.

I am assuming I am only getting this error because we are using active Directory for our security

I am going to try getting a domain service account added to one of our Active Directory groups and then set that as an Administrator and try again.


Ron Johnson (BOB member since 2004-09-07)

Looks like installing Crystal Reports 2008 breaks the macro. The macro tries to reference the newer components in CR2008 and thus it does not work with XIR2. Uninstall CR2008 and see if that fixes your ability to run the Macro.


alpha1145 :us: (BOB member since 2006-01-04)

Looks like installing anything from R3 breaks it.

I am able to run it just fine on a machine that does NOT have BO installed at all, so clearly there is no requirement to have anything else installed.

I have run this in the past on a machine with R2 client tools on it, but have never had Crystal installed so I don’t think Crystal is a requirement. But, having R3 seems to be a problem.


Steve Krandel :us: (BOB member since 2002-06-25)

UserObject.Properties(“SI_LASTLOGONTIME”)

line of code gives me the error that “item SI_LASTLOGONTIME was not found in the collection”.

Can you just let me know how to handle it in the code so that I could do that on my copy?

Thanks in advance.

Best Regards…


BO_Stuffed (BOB member since 2008-03-29)

Sorry…

The error trapping on the VBA was set to “break on all errors”; I have now put it to “Break on unhandled errors” and got the resolution.

Thanks


BO_Stuffed (BOB member since 2008-03-29)

Hello,

I am getting timed out errror while running query against CMS.

error = “CMS operation timed out after 9 min 1000440”

Is there any way i can increase time limit?

Can someone please help me!

Thanks,


BOpat :us: (BOB member since 2006-11-09)

Hello,
This xls is very usefull, but we are migrating to MS Office 2007 Professianal. And this Macro doesn’t work with MS Office2007 ! Can you create a 2007 version ?
Thanks.


DheerJ (BOB member since 2005-10-20)