Hello,
This vbs script was given to me by a senior colleague. Please leverage it as per your requirement
Instruction:
1: In order to run this script on a windows platform, you need to have Microsoft Excel and BusinessObjects Client Tools installed on that box.
2: You will need to change CMS name, port and Administrator password.
3: In case you do not have write access on C: Drive, change the path of the output excel file to the location where your User credential has access on that box.
4: Copy this highlighted content in a text file and rename it to UserGroupList.vbs and run it (right click >> Open) from the box as per prerequisites mentioned in
#Point 1
And Last but not the least, if you are planning to run it in a production environment and if you have a lot of Users and Groups in your environment, you might want to run it in a non-load time to avoid any unnecessary load on the CMS.
----------------------------------------------------------------------------------------------------------------------------------------------------
' **********************************************************
' ***********Script written By Manish Singh ****************
' ***************manishsingh2k@gmail.com*********************
' *********************************************************
' This script generates a list of users and the groups
' associated to these users on an MS Excel file
' This script will run on Window Server 2003 or a Windows XP
' computer with BO Server/Client application and MS Office
' installed
' ***********************WARNING****************************
' This script is not part of any product from Business Objects
' and is merely a result of hit and trials while working on an
' issue for a specific customer.
' since this script has been written for testing purpose only,
' it is not entitled to receive support.
' ************ USE THIS SCRIPT AT YOUR OWN RISK ************
' **********************************************************
Option Explicit
Const CMS = "boe3:6400"
Const UserId = "Administrator"
Const Pwd = ""
Const Auth = "secEnterprise"
Dim oSessionMgr
Dim oEnterpriseSession
Dim oInfoStore
Dim oInfoObjects
Dim listOfGroups
' For MS Excel file
Dim strXlsFileName
Dim objWorkbook
Dim objExcel
Dim objSheet
Dim intRow
Dim intSwitchColorIndex
Dim intColumn
intSwitchColorIndex = 0
Set oSessionMgr = CreateObject("CrystalEnterprise.SessionMgr")
Set oEnterpriseSession = oSessionMgr.Logon(UserId, Pwd, CMS, Auth)
Set oInfoStore = oEnterpriseSession.Service("","InfoStore")
strXlsFileName = "c:\BusinessObjects Users.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
' Get list of groups only in memory
Set listOfGroups = oInfoStore.Query("SELECT TOP 100000 * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='UserGroup'")
Set oInfoObjects = oInfoStore.Query("SELECT TOP 100000 * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='User'")
' We will start writing user alias data from row 2 in MS Excel file
Dim user
Dim iterator
objSheet.Cells(1, 1).Value = "username"
intRow = 2
for iterator = 1 to oInfoObjects.count
Set user = oInfoObjects.Item(iterator).PluginInterface("")
objSheet.Cells(intRow, 1).Value = user.title
Dim group
Set group = user.Groups
Dim clause
clause = "("
Dim i
for i = 1 to group.count
clause = clause & group.item(i)
If i = group.count Then
clause = clause & ")"
Else
clause = clause & ","
End If
next
Dim groups
Set groups = oInfoStore.Query("SELECT SI_NAME FROM CI_SYSTEMOBJECTS " &_
"WHERE SI_KIND='UserGroup' AND SI_ID IN " & clause)
Dim groupString
groupString = ""
intColumn = 2
Dim j
' Write group names for the user to the MS Excel row
for i = 1 to groups.count
For j = 1 To listOfGroups.Count
If groups.item(i).title = listOfGroups.item(j).title Then
objSheet.Cells(intRow, j+1).Value = groups.item(i).title
intColumn = intColumn + 1
End If
Next
Next
intRow = intRow + 1
next
oEnterpriseSession.logoff()
' To fit the columns
For intColumn = 1 to listOfGroups.Count+1
objSheet.columns(intColumn).AutoFit()
Next
' Save the Excel file
objWorkbook.SaveAs(strXlsFileName)
----------------------------------------------------------------------------------------------------------------------------------------------------
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 | |
2 |