Skip to Content
Author's profile photo Former Member

SAP BusinessObjects User and Group list in Excel

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)

—————————————————————————————————————————————————-

Assigned tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Animesh,

      We are trying to run this code in BO 4 but not able to run,could you please check your script file again.

      Thanks

      Swati

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Swati,

      It works for me in BI 4.

      Have you checked the pre-requisites.

      What is the error message you get?

      Regards,

      Animesh Kumar

      Author's profile photo Former Member
      Former Member

      Hi,

      For 1 of my requirement, this query is very useful. I am able to run this query successfully.

      Need format changes to the expected output excel and additional field values (Last Logon time).

      Below is the format i am looking for, could you please help me with the modified or new script to get the output in excel.

      Your assistance is much appreciated. Thank you very much.

      User Group Administrator Last Access Date
      prakash core_service Administrator 7/30/2014
      harish limited_service 7/20/2014
      kumar

      core_service

      limited_service

      Administrator 7/21/2014
      Author's profile photo Former Member
      Former Member

      Hi Animesh,

      When I save the document as directed and try to run the script it throws up an error on line 89, which is the following line on the code

      set oSessionMgr =  CreateObject("CrystalEnterprise.SessionMgr")

      Error Message says " ActiveX component can't create object CrystalEnterprise.SessionMgr

      Code: 800A01AD

      Any ideas on how to resolve this ?

      Author's profile photo Former Member
      Former Member

      Hi Rajsekhar,

      it looks like you are in the 64bit machine and you need to run the command like this:

      C:\Windows\SysWOW64\cscript.exe c:\UserGroupList.vbs

      Regards

      Michal

      Author's profile photo Former Member
      Former Member

      Hi Animesh,

      Getting an error saying that Invalid Character line1 char 1

      Author's profile photo Yogesh Patel
      Yogesh Patel

      This is working perfect..

      
      ' 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 = "hostname:6400"
      Const UserId = "Administrator"
      Const Pwd = "Password"
      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)
      Author's profile photo MARCO GIOIA
      MARCO GIOIA

      Hi,

      using this one I'm only able to view the list of users and the group EVERYONE and ADMINISTRATORS (the standard ones).. why i'm not getting also my custom groups?

      Thank you

      Author's profile photo Former Member
      Former Member

      Hi All,

      I cant understand why you query the usergroups in the cycle. This is time and server resource consuming process. You already have the user and user group infoobjects. You can join them in the cycle without querying the groups again.

      Regards,

      surika

       

      Author's profile photo VINOTH RAVI
      VINOTH RAVI

      Hi All, I am searching for the scripts which gives details about Last access date of universe Universes and last date access of Webi reports and Related universe name.I trying to achieve my requirement through Excel Macro(scripts) not with Query Builder.Could you help in this?