Skip to Content

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)

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

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Prakash Baskaran

    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
    (0) 
  2. Rajsekhar Patlola

    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 ?

    (0) 
    1. Michal Jursa

      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

      (0) 
  3. 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)
    (1) 
    1. 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

      (0) 
  4. Karoly Suri

    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

     

    (0) 

Leave a Reply