Excellent file! Tested on a large enterprise BOXI infrastructure (3000+ users) and it is working perfectly. :D
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.
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... http://www.forumtopics.com/busobj/viewtopic.php?t=97773
Julien, Thanks a ton!!! That worked beautifully :) Toral.
I used it,Keep up the good work. :D
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,
I guess you need to "Enable Macro" since the code is an executable
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.
You are welcome to go into the code to improve it ;) It's quite easy to add a column in the document :) 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.
Great work, Julien. Just downloaded V3 and it is proving to be really useful. Please keep them coming :D
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?
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: [code:1:8a39e5ac83] 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 [/code:1:8a39e5ac83] All I had to do was move the loop for the groups to be inclusive of all the user fields and it works perfectly.
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
[quote:966d9b2d5e="reemagupta"]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"[/quote:966d9b2d5e] 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, Thank You for your reply. I tried it on other client machine and it works perfect. Regards Reema
I was using this tool and loving it but all of a sudden when I try to Extract Data from CMS now, I get an error: "VBAProject - 13: Type Mismatch 1000013" Any ideas what this error means? I haven't changed anything in my XI install and have everything installed. Any help would be greatly appreciated. Thanks.