SAP BusinessObjects User and Group list in Excel

[former_member202789]

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)

----------------------------------------------------------------------------------------------------------------------------------------------------