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.

Description

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.

  1. Log into the Central Management Console (CMC) as Administrator
  2. From the home page, click on Promotion Management
  3. Click on the Import dropdown menu and choose the option, Import File
  4. The Import from File dialog box appears.  Ensure the ‘File System‘ radio button is selected and click on ‘Browse
  5. Unzip the .lcmbiar file you previously downloaded.  In this box, navigate to the .lcmbiar file and click OK.
  6. 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.
  7. Click Create.
  8. Now that the import job is created, you will need to run the promotion.  Click on the Promote button in the toolbar.
  9. 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.
  10. 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:

  1. Navigate to the ‘Admin Scripts‘ folder and right click on the Program File underneath called ‘DisableInactiveUsers‘ and choose Properties.
  2. Under the ‘Schedule‘ option on the left side, choose the Program Parameters option.
  3. Version 1 or 2:
    1. 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. )
  4. Version 3:
    1. 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.
  5. 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.

Other Info

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.

Examples:

0 0

No changes made. Full list of users will be output to csv file.

365 0

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.

365 365

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.


0 365

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.

/wp-content/uploads/2014/11/historywindow_593788.jpg

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:

/wp-content/uploads/2014/11/screenshot1_593781.jpg

If you have a larger user list, opening the csv within excel can make the data easier to work with.

/wp-content/uploads/2014/11/screenshot2_593782.jpg

To report this post you need to login first.

42 Comments

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

    1. Joshua Kuhn Post author

      Internally the status shows as ‘released to customer’ but through SMP it’s showing as waiting for a “technical review”.  The update should be immediate, but there might be something else happening in the Note system that’s preventing it from updating publicly. I’ll follow up on it.  Thanks for letting me know.

      (0) 
      1. Joshua Kuhn Post author

        The note is definitely public.  Out of 3 times clicking the sap note link above, twice it worked and once it showed a stale status.  There’s probably just a syncing issue happening on the SAP Note server unrelated to this specific note.  For now, try again and hopefully it will work.

        (0) 
        1. Jawahar Konduru

          Thanks. I ran the program with ‘0’ to output the total users. It worked well. But in the output, for some of the users ‘last logon time’ listed as ‘1969’ year. Do you know why?

          (0) 
          1. Joshua Kuhn Post author

            I’ll update the blog. I probably should have mentioned that part.  But here’s the short of it.  Any user that has not logged in before, will not have a SI_LASTLOGONTIME property associated with their user infoobject.  This property gets created by the CMS when the user logs in for the first time. For this reason, on line 162 I set the variable ‘formattedLastLogonTime’ to Date(0) to prevent a null pointer exception.  Depending on your locale, Date(0) can be either 1969 or 1970 but you should only see this set when the last column in the output has a value of ‘no’

            (0) 
  1. Swapnil Yavalkar

    Hi Josh,

    This is going to be a great utility for our environments. We have integrated BO with our internal product and for this integration we use the BO enterprise user(which we specify in the integration batch files) so this user never logs into the BO system directly. I believe this user will be treated as an inactive user by BO. If I check the last logon time of this user then it shows me the creation time of this user because we never use this user to login to BO server, however we use it in the integration scripts.

    Apart from specifying this user in the integration batch files, we use them to schedule BO reports from our application.

    I was wondering if this user has logged into the system once then this program will disable this user as well after 90 days which will affect the functionality of integration.

    Is there any way we can skip few users from getting disabled by this program?

    Appreciate your inputs.

    ~SwapnilY

    (0) 
  2. Swapnil Yavalkar

    Hi Josh,

    I have checked the source code of this program and it seems that it is going to check only the SI_LASTLOGONTIME property.

    Could you please let me know whether there is any way to check the user sessions(for scheduling a report) before disabling the users?

    ~SwapnilY

    (0) 
    1. Joshua Kuhn Post author

      Correct, the SI_LASTLOGONTIME property will either have a datetime value if the user has ever logged in one or more times.  Or it will not have a value if the user alias exists, but has never once logged in. 


      I don’t think I fully understand the 2nd part to your question?  Are you concerned about a user that has logged in once and scheduled a few recurring reports? or something else?  When a report is scheduled, the CMS will actually create a session for the user to check permissions, but it uses a server-token to do this… not the same as when a user logs into BI Launchpad directly.  Its been a while since i’ve looked at this but I’m pretty sure a scheduled instance will not update the SI_LASTLOGONTIME for a user.

      (0) 
      1. Swapnil Yavalkar

        Hi Josh,

        Thank you so much for your looking into this. ๐Ÿ™‚

        You have already answered my second question. If it is going to update “server-token” not the “SI_LASTLOGONTIME” then this script will disable this user and it will eventually break our integration with the third party application.

        Is there any way we can add this “server-token” in this script so that this property will also get checked by this script and it will not disable our user which is being used for integration?

        Appreciate your inputs !

        ~SwapnilY

        (0) 
        1. Joshua Kuhn Post author

          The server-token is type of session object, not a user alias.  The alias is what is being disabled.  But if you have a few users that you know you want the script to ignore, then just look for the line:

          String query2 = “select top ” + top + ” si_name, si_id, SI_LASTLOGONTIME, SI_ALIASES from ci_systemobjects where si_kind = ‘user’ and si_id > ” + lowVal + ” and si_name not in (‘QaaWSServletPrincipal’, ‘SMAdmin’, ‘Administrator’, ‘Guest’) order by si_id”;

          and alter the last part of the query that says si_name not in (…).  The script can only analyze what the query returns.  If you really want to cycle through all users in the system to first see if they have a recurring scheduled instance, that will seriously impact total runtime, but it could be done.  There’s a link for the source code in the blog.  Feel free to download and customize it to your liking.

          (0) 
          1. Swapnil Yavalkar

            Hi Josh,

            Thank you for all your inputs. ๐Ÿ™‚

            I have already downloaded the source code and as suggested I will modify this script.

            Appreciate your help.

            ~SwapnilY

            (0) 
  3. Brian Hughes

    Hi Josh,

    Is it correct that the program has a limit of checking first 250 users “int top =250” ?. we have run the program but it does not seem to check all users. Is there any way to increase the number of users that can be checked?.

    Brian

    (0) 
    1. Joshua Kuhn Post author

      no, it should loop through all user aliases in the system.  “top” is set 250 but this only controls the amount of records returned in each loop.  In other words, it will query for the first 250 aliases, commit, query for the next 250, commit, etc.  The script should query for the top 250 aliases and while looping through each of these 250, it sets another variable “lowVal” that keeps track of where the next query should start.  Look for this line:

      String query2 = “select top ” + top + ” si_name, si_id, SI_LASTLOGONTIME, SI_ALIASES from ci_systemobjects where si_kind = ‘user’ and si_id > ” + lowVal + ” and si_name not in (‘QaaWSServletPrincipal’, ‘SMAdmin’, ‘Administrator’, ‘Guest’) order by si_id”;

      (0) 
      1. Brian Hughes

        Thanks Josh,

        When we run with 0 we get a list of the users however when we run with value for example 80 we find it’s not deactivating users that have not logged in over 80 days and I don’t mean those that have never logged on we are using BI4.0 SP6. Is there any special access rights requirements for the program? or any other idea why it might not work?..

        Cheers, Brian

        (0) 
        1. Joshua Kuhn Post author

          I’m not sure if you’re scheduling it or running the script standalone. But sometimes running it standalone can be a bit easier to debug since any output or error would appear in the cmd window.  As for access rights, you would need to be running it as a user that has the ability to disable another user via the CMC. If your user can do that, then you should have all the permissions needed.  Would it be possible to send me the output from when you last ran it?  If so, email it to joshua.kuhn@sapns2.com and i’ll take a look.  Please include your contact info as well.

          (0) 
    2. Joshua Kuhn Post author

      I just tested it with 600+ users and it looped through all users fine for me.  Try running it with the  argument set to 0 when scheduling from the CMC.  This should output the list of users and their corresponding values without changing anything.

      (0) 
  4. Joshua Kuhn Post author

    Thanks everyone for all the feedback.  About a month ago, someone reported a formatting issue in the output when multiple aliases exist for a single user.  Although the script should still run correctly, reading the output was a bit annoying for larger record sets.  I’ve corrected this and will have the new code uploaded to the SAP Note shortly.  I’ve added a version stamp to the top of the DisableInactiveUsers.java source code and i’ll rename the .zip file to v2.0 as well (the original script wasn’t versioned).

    (0) 
  5. John Clark

    Josh, is it correct then that this utility will not disable any users who have not logged in?

    We use the Active Directory interface to link AD accounts to Aliases.  We have lots of users who get added to the Active Directory groups but never log in to Business Objects.  We want to be able to disable these also if it has been longer than 90 days since the account was created.

    Would this require additional changes to code to implement?

    (0) 
    1. Brian Hughes

      Hi John,

      See the other info section under running the program you can see this is the case. This is because the program only references the last log on time information and does not also cross reference to creation time which I’m guessing would be more complex to do.

      If the program was to deactivate users with no last logon not referencing creation date then it’s would end up deactivating newly created users causing some unhappy folks.

      It’s a shame that the program does not also cross reference creation date data so that if user has never logged on and also creation date is older than X days then it deactivates them. Then is would be perfect.

      At the moment the tool is helpful but it still leaves a lot of manual action having to check users created and never logged on which can often be the case when business request optimistic mass user creations where only a fraction of users really need.

      (0) 
      1. Joshua Kuhn Post author

        Hi Brian.  I never heard back from you on your last post.  Were you able to resolve it? 

        I’ll try to look into what it would take to cross reference each alias w/ the user si_creation_time field next week.  I’d likely have to load all userIDs and their dates into a hashmap when the script starts and then do a lookup for every user that meets the first criteria.  It’ll add to the total execution time, but it should definitely be doable.

        (0) 
        1. Brian Hughes

          Hi Josh, when I checked with our support folks it turns put they had misunderstood the point about the tool not including users that had never logged on and it turns out the tool is working as designed. So all is good, but as above it would be great if the tool looked at user creation date if it finds no log on and deactivates users with condition of no log on date and user created more than X days ago.

          (0) 
        2. John Clark

          Thanks Josh.  If you could add in the comparison to si_creation_time, that would be great. ๐Ÿ™‚ I’m not a Java programmer so I don’t know that I would be able to make the modifications or not.

          The tool has already been useful to us as it is though.  It has shown that by our definition of “active users”, only about 10% of the users that have access are actively using our installation.  This will hopefully help us to get our user access cleaned up.

          (0) 
  6. Srinivasa Rao Chintha

    Hi Joshus

    When we run with 0 we get a list of the users however when we run with value for example 700 we find it’s not disabling users that have not logged in over 700 days and we are using BI 4.1 SP3. I can have  all the rights in CMC as i am part of administartor group.Is there any special access rights requirements for the program? or any other idea why it might not work?..

    (0) 
    1. Joshua Kuhn Post author

      Hi Srini / John / Brian,

      As some of the posts above mention, ver2 of the script does not disable any user that hasn’t logged in at least once.  I’m just revised the script this morning  to also disable users based on creation date (if they’ve never logged in).  If you, Brian or John above could help validate the new version for me, that would be great.  I’ve run a few tests here and it cycled through 8000 users pretty quickly.  I just want to make sure all scenarios are tested before releasing to the general public.  Email me directly if either of you will be able to assist.  If all goes well, I should be able to post the new version by the end of the week.

      Srini, if you know that some of your users which have logged in but beyond the 700 days argument, and these users are still not getting disabled, then let me know.  i’ll need to see the output file in that case.

      Regards,

      Joshua

      joshua.kuhn@sapns2.com

      (0) 
      1. Brian Hughes

        Hi Joshua,

        This is excellent news ๐Ÿ™‚

        Can you give an understanding about how the deactivation of users have never logged in works? does it check if account created date and never logged in is older than the days value we input and deactivates on that basis?

        Cheers, Brian

        (0) 
        1. Joshua Kuhn Post author

          The new version takes an additional argument which represents the number of days old (since creation).  The script first queries for all users in the system, writes each si_id and creation_date into a hashmap and refers to this in memory when needed, rather than querying the cms each time. 

          The rest of the logic is basically the same as it was before, except now when it finds a user that has never logged in, it will compare the value in the new argument against the user’s creation date (SI_CREATION_TIME) held in the hashmap.  In other words, this new argument should only come into play for those users that have never logged in, otherwise creation time is only logged, not evaluated.  I’ve also added 2 new columns (days since creation) and (creation date), as well as reordered the columns to make the two dates a little easier to read.  I’ll update the notes in the blog with the details if a couple of you can help test.  I’ll need an email from each of you outside of the comments section so i can forward you the new script.

          (0) 
  7. Srinivasa Rao Chintha

    HI Joshua,

    Thanks for your prompt response.

    I will try to validate from my end. Can you please share the updated version of script(Jar file). i will test and share the results.

    Thanks & Regards

    Srini

    (0) 
  8. Srinivasa Rao Chintha

    HI Joshua,

    Thanks for sharing the latest version of script.

    I would be able to disable the inactive users with new version of script.I have tested all the authentications(WINAD,Enterprise,SAP).its working fine.

    Many thanks for helping on this.

    Thanks & Regards

    Srini

    (0) 
  9. Brian Hughes

    Hi Joshua, would it be possible to add the ability to put in an exclusion list to the tool so that certain users can be ignored from the deactivation?. I think after that it would be pretty much the perfect tool for deactivation.

    (0) 
      1. Joshua Kuhn Post author

        Two people would need to ‘follow’ each other on the SCN before it allows one to send a private message to the other.  Add me and you should be able to do that.

        (0) 
    1. Joshua Kuhn Post author

      The link still seems to work for me and the SAP Note shows as externally customer facing still. ย Maybe try searching by the note # instead – 2097401? ย Since you’re getting a 500 error, check your login and make sure you’re able to log into Service MarketPlace.

      (0) 

Leave a Reply