BusinessObjects Board

BOXI UserList & Group Excel Export

This topic is dedicated to the BOXI UserList & Group Extraction located in the BOB’s Download (here)

A little description :

History:
V3 (17/01/2008) – 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 (04/01/2008) – Corrected the 1000 objects limit (hard coded to 1000000 objects) ; and the FullUser parameters, add of a topic for discussion
V1 (25/07/2007) – Original Release

Feel free to post here remarks on this tool, possible improvements and bugs…

Excellent file!
Tested on a large enterprise BOXI infrastructure (3000+ users) and it is working perfectly.

:smiley:


darkstar09 (BOB member since 2004-09-28)

Hi,

I agree that this macro is excellent! However, my requirement is a little bit different. I want to be able to run this macro just for certain group. So, I modified the macro to pull in the users first and then filter it for a group.

However, what I am noticing is that the user object can bring in just 1000 rows. Thus it is providing me a partial resultset. I know that the query builder tool too has this limitation.

Will appreciate any suggestions regarding this.

Thanks in advance.

Toral.


Toral Goradia (BOB member since 2003-10-22)

Hi,

For the V2 version i have just released, i have corrected this ‘1000 objects’ problem. It’s just a syntax problem inside the infostore query (add after the SELECT a ‘TOP xxxxx’ where xxxx is the number of row you want to get, and it’s set by default to 1000).

For my tool i have hard-coded the limit at 1,000,000 row, i think it will be enough …

Regards,

You can use this tool to get the user list by group…


rs90144 (BOB member since 2006-04-18)

Julien,

Thanks a ton!!! That worked beautifully :slight_smile:

Toral.


Toral Goradia (BOB member since 2003-10-22)

I used it,Keep up the good work. :smiley:


Sheshachala5 :india: (BOB member since 2004-01-09)

I downloaded the v2. But the button on the excel sheet is not clickable. Do I need to do some configuration before using the excel sheet?

THanks,


taskoriented (BOB member since 2007-05-08)

I guess you need to “Enable Macro” since the code is an executable


Sheshachala5 :india: (BOB member since 2004-01-09)

Thanks. It works. That was some problems with my system.

BTW, just wondered the possibility of adding the alias column into the spreadsheet. It will be extremly helpful for our project to do some house cleaning.


taskoriented (BOB member since 2007-05-08)

You are welcome to go into the code to improve it :wink:
It’s quite easy to add a column in the document :slight_smile:

Regards,

taskoriented, when you speak of alias column, what is the meaning ? The description ? The login name ?

V3 version available, with the Last Login date information, and R/W capactiy (it’s now possible to modify data in the CMS with the excel file)

julienbras. Good job! It is glad to see the V3 out. I really like the tool. I am able to modify the code to get the alias information. In my environment, I created enterprise alias account from WinAD or LDAP account for troubleshoot purpose. So that I need to make sure all the enterprise alias are deleted afterwards. This tool is very useful for this purpose.


taskoriented (BOB member since 2007-05-08)

Great work, Julien. Just downloaded V3 and it is proving to be really useful.

Please keep them coming :smiley:


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

I used to be able to use this tool with no problems and I really like it.

However, I’m suddenly getting an error box when clicking on the “Get Users & Groups” button.

Failure in UserGroups()
VBA Project - 429: ActiveX component can’t create object 1000429

Attached a screenshot.

Any ideas?
vba_error.gif


bmalak_98 :us: (BOB member since 2006-07-06)

I LOVE this tool. Thanks Julien! I needed to get a list of users and group membership, however I wanted a list that would repeat the user record for each group the user was a member of. That way I could give a department a list of everyone that was a member of their particular group. All I had to do was change Julien’s code just a little to get it to work. Here’s what I did:


Private Sub cbExtract_Click()
    Application.ScreenUpdating = False
    Dim ModeRecalcul As Long
    ModeRecalcul = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    'Purge existing data
    Sheets("Users").Range("A3:L65000").ClearContents
    'Session Manager declaration
    Dim SessionManager, Sess As SessionMgr
    'Enterprise Session declaration
    Dim esession As EnterpriseSession
    'InfoStore declaration
    Dim iStore As InfoStore
    'Info Objects declaration
    Dim Users, Groups As InfoObjects
    'Info Object declaration
    Dim UserItem, GroupItem As InfoObject
    'User Object declaration
    Dim UserObject As User
    Dim Rng As Excel.Range
    Dim RowNum, GroupNum As Long
    On Error GoTo ErrorHandler
    Dim ErrorState As String
    'Session Manager instanciation
    Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")
    'Enterprise Session instanciation
    Set esession = SessionManager.Logon(tbName, tbPassword, tbCMS, "secEnterprise")
    'Infostore instanciation
    Set iStore = esession.Service("", "InfoStore")
    'document the users
    Set Users = iStore.Query("SELECT TOP 1000000 SI_EMAIL_ADDRESS, SI_FORCE_PASSWORD_CHANGE, SI_NAME, SI_ID, SI_USERGROUPS, SI_USERFULLNAME, SI_ALIASES, SI_DESCRIPTION, SI_LASTLOGONTIME, SI_PASSWORDEXPIRE FROM CI_SYSTEMOBJECTS Where SI_KIND='User'")
    RowNum = 2
    Set Rng = Sheets("Users").Cells
    'Write in the top the server/login used, the update date
    Rng(1, 4) = "Server: " & tbCMS & Chr(10) & "User: " & tbName & Chr(10) & "Update Date: " & Date & " " & Time
    For Each UserItem In Users
    Set UserObject = UserItem
    
        For Each GroupId In UserObject.Groups
        Set Groups = iStore.Query("SELECT SI_NAME, SI_DESCRIPTION FROM CI_SYSTEMOBJECTS Where SI_ID=" & GroupId)
    
        RowNum = RowNum + 1
        Rng(RowNum, 1) = UserItem.ID
        Rng(RowNum, 2) = UserItem.Title
        ErrorState = "FullName"
        Rng(RowNum, 3) = UserObject.FullName
        Rng(RowNum, 4) = UserObject.EmailAddress
        GroupNum = 0
'                For Each GroupId In UserObject.Groups
'                GroupNum = GroupNum + 1
'                    Set Groups = iStore.Query("SELECT SI_NAME FROM CI_SYSTEMOBJECTS Where SI_ID=" & GroupId)
'                    If (GroupNum = 1) Then
'                        Rng(RowNum, 5) = Groups(1).Title
'                    Else:
'                        Rng(RowNum, 5) = Rng(RowNum, 5) & Chr(10) & Groups(1).Title
'                    End If
'                Next GroupId
        Rng(RowNum, 5) = Groups(1).Title
        Rng(RowNum, 6) = Groups(1).Description
        If (UserObject.Aliases(1).Disabled) Then
            Rng(RowNum, 7) = 1
        Else
            Rng(RowNum, 7) = 0
        End If
        If (UserObject.ChangePasswordAtNextLogon) Then
            Rng(RowNum, 8) = 1
        Else
            Rng(RowNum, 8) = 0
        End If
        Rng(RowNum, 9) = UserObject.Description
        ErrorState = "LastLogon"
        Rng(RowNum, 10) = UserObject.Properties("SI_LASTLOGONTIME")
        
     Next GroupId
        
    Next UserItem
    Application.Calculation = ModeRecalcul
    Calculate
    Application.EnableEvents = True
    Me.Hide
CleanUp:
    Me.Hide
    On Error Resume Next
    esession.Logoff
    Application.Calculation = ModeRecalcul
    Calculate
    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    If Err.Number = -2147210697 Then
        If ErrorState = "FullName" Then Rng(RowNum, 3) = "Error on Full Name"
        If ErrorState = "LastLogon" Then Rng(RowNum, 10) = ""
        Resume Next
    End If
    Me.Hide
    MsgBox Err.Source & " - " & Err.Number & ":  " & Err.Description & " " & Err.HelpContext, _
        vbCritical, "Failure in UsersGroups()"
    Resume CleanUp
End Sub

All I had to do was move the loop for the groups to be inclusive of all the user fields and it works perfectly.


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

Thanks for putting this useful tool.
I am getting an error using this tool when I click the “Extract data from CMS” button . The error is “Compile Error: Can’t find project or library”

Any idea?

Thanks
Reema


reemagupta (BOB member since 2002-09-18)

The SDK (specifically the COM version of the Enterprise SDK) has to be installed on the machine. If you did a client install (which installs EVERYTHING, including the SDK), you should be fine. If you did a selective install (or no client install) then that would explain it.


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

Dwayne,

Thank You for your reply. I tried it on other client machine and it works perfect.

Regards
Reema


reemagupta (BOB member since 2002-09-18)