VBS script for when you do NOT have Query Builder Installed

[former_member213977]

Good Afternoon Folks,

After the frustration of trying to find out users who are set up as 'Named' on our system with the use of query builder being installed, I was given a script that would pull this information through running a visual basic script. Our install of Business Objects was customized and unfortunately we have run into difficult and at times lengthy troubleshooting steps in order to diagnose certain problems. Well we have determined but using this script something as simple as running a query that exports to an excel file through this vbs script has saved us countless hours of hop skipping and banging our heads off the desk so I wanted to share this script with those who may not be able to pull information through query builder or do not have experience using the java sdk kit!

Please note I did NOT write this script!!!!!!! I have left the original creators name in the file to give due credit where I believe it should be given. Also please edit this file at your OWN RISK. I take no responsibility and will give support for it.

This script as it will run and export what users you have in your BO db listed as 'Named' users. I have tried to edit the descriptions to the best of my knowledge to describe what they do. Im not very familiar currently with vb scripting, so again edit at your own risk but I found this helpful and wanted to share.

Enjoy...

Directions on how to use:

• Save to your desktop of the machine where Business Obejects is installed
• Edit the file with your specific data requirements
  
• Run the script

*Should you encounter any errors, you can also open 32bit Internet explorer browser and simply drag this file into the browser.

Note: You will also need to open the file and edit with your server information under:

    'For the logon, Authenticate as Following

Below is the code for the script. (Just save as .vbs )

Option Explicit

' **********************************************************

' ***********Script written By Manish Singh ****************

' ****************************************************************

' **********************************************************

' This script lists the User and their Aliases in BOE XI 3.1

' ***********************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 ************

' **************************************************************************

' BOE XI R2 Object variables

Dim objSessionManager

Dim objEnterpriseSession

Dim objSession

Dim iStore

Dim objMyUserInfo

Dim strMyname

Dim UserProperties

Dim strUserName

'For the logon, Authenticate as Following

Const APS = "SERVER:PORT"

Const UserID="USERID"

Const Password = "PASSWORD"

Const Auth = "AUTHENTICATION"

' For MS Excel file

Dim strXlsFileName

Dim objWorkbook

Dim objExcel

Dim objSheet

Dim intRow

Dim intSwitchColorIndex

Dim intColumn

intSwitchColorIndex = 0

'Create an Enterprise objSessionManager

Set objSessionManager = CreateObject("CrystalEnterprise.SessionMgr")

Set objSession = objSessionManager.Logon(UserID, Password, APS, Auth)

'Create a new Variable for the login token

Set iStore = objSession.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)

Dim i, strTotal, intTotal, strAliasNum, arrString

' This query will return all the users within the system marked as Named

' This query statement to be edited in order to pull the data from the system that your after

Set objMyUserInfo = iStore.Query("SELECT TOP 20000 SI_ID, SI_NAME, SI_KIND, SI_ALIASES from CI_SYSTEMOBJECTS where SI_NAMEDUSER=1 AND SI_KIND='User'")

' We will start writing user alias data from row 2 in MS Excel file

intRow = 2

' Write headings to the MS Excel file

objSheet.Cells(1, 1).Value = "COLUMN HEADING 1"

objSheet.Cells(1, 2).Value = "COLUMN HEADING 2"

objSheet.Cells(1, 3).Value = "COLUMN HEADING 3"

objSheet.Cells(1, 4).Value = "COLUMN HEADING 4"

objSheet.Cells(1, 5).Value = "COLUMN HEADING 5"

' Here you can finish editing as many column names to fit your report

' Format the headings

For intColumn = 1 To 5

          objExcel.Cells(1, intColumn).Font.Bold = True

          objExcel.Cells(1, intColumn).Interior.ColorIndex = 40

Next

' For every object belonging to this uder perform the following

For Each UserProperties In objMyUserInfo

          strUserName = UserProperties.Properties.Item("SI_NAME").value

          strTotal = UserProperties.Properties.Item("SI_ALIASES").Properties.Item("SI_TOTAL").value

          intTotal = 0

          intTotal = CInt(strTotal)

          For i = 1 To intTotal

                    strAliasNum = CStr(i)

                    strMyname = UserProperties.Properties.Item("SI_ALIASES").Properties.Item(strAliasNum).Properties.Item("SI_ID").value

                    arrString = Split(strMyname, ":")

                    objSheet.Cells(intRow, 1).Value = strUserName

                    'Set objRange = objExcel.ActiveCell.EntireColumn

                    'objRange.AutoFit()

                    If arrString(0) = "secEnterprise" Then

                              objSheet.Cells(intRow, 2).Value = "Yes"

                    End If

                    If arrString(0) = "secWinAD" Then

                              objSheet.Cells(intRow, 3).Value = "Yes"

                    End If

                    If arrString(0) = "secLDAP" Then

                              objSheet.Cells(intRow, 4).Value = "Yes"

                    End If

                    If arrString(0) = "secWindowsNT" Then

                              objSheet.Cells(intRow, 5).Value = "Yes"

                    End If

          Next

          ' Coloring the rows for readability

          For intColumn = 1 To 5

                    If (intSwitchColorIndex = 0) Then

                              objExcel.Cells(intRow, intColumn).Interior.ColorIndex = 24

                    ElseIf (intSwitchColorIndex = 1) Then

                              objExcel.Cells(intRow, intColumn).Interior.ColorIndex = 2

                    End If

          Next

          ' Turn color switch on / off

          If intSwitchColorIndex = 0 Then

                    intSwitchColorIndex = 1

          Else

                    intSwitchColorIndex = 0

          End If

          ' Go to next row

          intRow = intRow + 1

Next

' Update the changes to DB

iStore.Commit objMyUserInfo

' To fit the columns

For intColumn = 1 to 5

          objSheet.columns(intColumn).AutoFit()

Next

' Save the Excel file

objWorkbook.SaveAs(strXlsFileName)

' Uncomment the below line to close the MS Excel File automatically

' objExcel.Quit