Over the past few years, I’ve been asked to create or assist in creating various scripts to automate a task in SAP Business Intelligence 4.x (BI4). Most of these scripts were loose files in the form of .jsp or .vbs that had to be run manually. Lately, I’ve taken a personal liking for Program Files which are uploaded and stored within the BI environment. The last request was for a script that would disable any user that hadn’t logged into the CMS within the past 90 days. Rather than hard-coding the 90 days into the program file, I created it to take the number of days as an argument (or input parameter). And if the argument is set to 0, then the script will only display the user info without committing any changes.
If you’re interested in creating your own Program File and want more information on how to do this yourself, I wrote another blog titled How to Create a Program File in BI4. The blog describes the steps and requirements for creating a java program in Eclipse as well as provides a project template to start with.
The following script can be used to disable users in bulk that have not logged into the BI system for the past X number of days. This is equivilant to logging into the CMC manually, navigating to a user’s property page and clicking the ‘Account is Disabled‘ checkbox under the ‘Attribute Binding‘ section.
Where to Download
The source code and .lcmbiar file can be downloaded from the following SAP Note#: http://service.sap.com/sap/support/notes/2097401
*** Important Note: The script has been updated a couple times. All revisions are attached to the SAP Note above.
- Version 2 was released to fix a formatting problem in the output when multiple aliases exist for a single user.
- Version 3 was just updated ( as of 10/16/2015 ) to now include an additional argument which evaluates the number of days since the user object was created. I’ve added more details about this change below in the ‘Other Info’ section. In each revision, the zip file includes the corresponding updated source code, biar file as well as the loose jar. ***
How to import the script into BI 4.x
The script can be imported into your BI environment using Promotion Management. The zip downloaded from the kbase has the .lcbiar file within it. Follow the steps below to import it using Promotion Management.
- Log into the Central Management Console (CMC) as Administrator
- From the home page, click on Promotion Management
- Click on the Import dropdown menu and choose the option, Import File
- The Import from File dialog box appears. Ensure the ‘File System‘ radio button is selected and click on ‘Browse‘
- Unzip the .lcmbiar file you previously downloaded. In this box, navigate to the .lcmbiar file and click OK.
- There will be a ‘new job’ tab opened with some of the information from the .lcmbiar file automatically filled out (such as Name, Description, etc). Select the Destination menu and choose the CMS you’d like to import the Program File into. You will be prompted to provide permissions for this CMS.
- Click Create.
- Now that the import job is created, you will need to run the promotion. Click on the Promote button in the toolbar.
- The summary page opens showing exactly which objects will be promoted. You should see 2 items. A folder object and a Program object. Click the Promote button at the bottom of this page.
- When the job is finished running, the Status column will show ‘Success’ as a result. The job should take less than a minute to run, so if you don’t see it succeed within a few minutes, make sure you have an Adaptive Job Server running that contains a Promotion Management Scheduling Service.
Running the Program File
Once the .lcmbiar file is imported (see the steps above), you will see a folder called “Admin Scripts” under the top level root folder. Be sure to set permissions on this folder accordingly so its not accessible for non-Admin users. To schedule, follow the steps below:
- Navigate to the ‘Admin Scripts‘ folder and right click on the Program File underneath called ‘DisableInactiveUsers‘ and choose Properties.
- Under the ‘Schedule‘ option on the left side, choose the Program Parameters option.
- Version 1 or 2:
- Only one argument is required. Set the Arguments field to the number of days since last logon. This value must be a positive integer. ( In other words, 10.5 days will not work. )
- Version 3:
- Two arguments are required. Set the Arguments field to 1.) the number of days since last logon and 2.) the number of days since the user was created. In both cases, the value must be an integer as in the previous two versions. The arguments must be separated by a space.
- Click Schedule.
RECOMMENDATION: The first time you run the program file, run it with the argument of 0 (or 0 0 if using version 3). This will give a full output for all users and how recently each user has logged in. Version 3 now also includes the creation date for each user and will output two additional columns in the output file: the creation date and the number of days since the user was created. In all versions, the output can be read as-is in text or easily copied into Excel and sorted as needed. Once you know the value of days old you want to run the job under, set the ‘Argument’ value accordingly.
As with any script, there can be unexpected results. Test the script first in a lower level environment such as a Test or Development system. Be sure to have a backup of your CMS system database in case you need to quickly revert the changes. And lastly, if you’d like to see the source code, the Eclipse project files are included in the zip file attached to the same SAP Note.
The Arguments field in version 1 & 2 is set to 0 by default. In version 3 the default is: 0 0. The first value represents the minimum number of days since last successful login. The second value represents the minimum number of days since the user was created. The script will query for the SI_LASTLOGONTIME value stored in the CMS repository for each user. It will determine how many days ago this SI_LASTLOGONTIME was (based on current timewhere the Adaptive Job Server is running).
In versions 1 & 2: any user that has not logged in within this number of days will be disabled when the script is run except for one condition. Any user that has never logged at least once will not have a SI_LASTLOGONTIME property and thus will be left unaltered. For these users, the output file will show the equivalent of the Java Date(0) value which is either 1969 or 1970 depending on your locale settings.
New to version 3: The SI_LASTLOGONTIME is still evaluated first for users that have logged in at least once ( argument #1 ). The second argument (number of days since the user was created) is only evaluated when a user has never logged into the CMS at least once. In this scenario, the user will have the equivalent of Java Date(0) because they’ve never logged in but the creation date will determine if the user is disabled or not.
If you leave the days value set to 0 0 (when scheduling, set the arguments field to 0 0), then the script will not commit any changes. In this case it will only output the full user list with their last login date/time and if that user is currently disabled or not. If any value greater than 0 is used, then the script will attempt to disable any users that match the criteria.
No changes made. Full list of users will be output to csv file.
All users who have not logged in within 365 days will be disabled. Users who have never logged in will be included in the output file, but not altered.
All users who have not logged in within 365 days will be disabled. Users who have never logged in and were created over 365 days ago will be disabled.
No changes made. Full list of users will be output to csv file. The 2nd argument is never evaluated because the first argument = 0. Same results as the first example.
Once the program file is scheduled, clicking on the successful instance within the history window will show the output results similar to viewing a report instance.
Except for the header info, the data in the output file is in a comma separated value format. Below is an example of the output file:
If you have a larger user list, opening the csv within excel can make the data easier to work with.