Deletion of users who's last log on date is greater than 180 days

[Former Member]

Hi Team,

We are currently on BI4.1 Sp3 and BI4.2, during our audit we found out 15K users(AD, SAP and enteprise users)who did not login in last 180 days, we would like to delete these users.

From the BI4.1 product we do not see any way to delete these users in bulk . Deleting users one by one is a very time consuming process.

Could you please let me know if there is already a Java script ( or .jar) which we can use a program object and schedule from BI4.x to delete these users in bulk ?

Regards,

Rohit Vamsi

[Former Member · Accepted solution]

You can use the attached code.

Change the query in the code to the one you have which gives you list of users who have not logged in for last 180 days. Currently the code has a query which brings all the users except Adminstrator and Guest, so make sure to change the query before you run it.

Save the file as .jsp extension and place it under AdminTools application.

Run the code by running the url

http://server:host/AdminTools/deleteUsers.jsp

Thanks,

Prithvi

deleteUsers.txt.zip

[Former Member · Accepted solution]

Hi Prithvi,

Thank you for the script, we will test this in out test box.

Regards,

Rohit Vamsi

[Former Member]

Hi Prithvi,

When we are testing the code in test box we are getting http 500 error.

1. Modified the code to include user last logon condition, saved jsp in Admintools

2. Restarted tomcat and launched the URL https://xxxx:port/AdminTools/DeleteUsers.jsp

[Former Member]

This is a compilation error. Make sure all the jsp tags are properly in place. Look for the start of the code it seems you have not included < .

You can compare you file with the code attached in the above post.

Thanks,

Prithvi

[Former Member]

Hi Prithviraj,

This is srini, part of rohit's team. We are trying to execute the below code and we are getting above error which rohit has mentioned.Can you please check the below code is correct or not. Please let us know if i miss any thing to add.

<%@ page import = "com.crystaldecisions.sdk.framework.*,

   com.crystaldecisions.sdk.exception.*,

   com.crystaldecisions.sdk.occa.infostore.*,

   com.crystaldecisions.sdk.plugin.desktop.user.*,

   java.util.*";

%>

<%

IEnterpriseSession oEnterpriseSession=null

try

{

oEnterpriseSession = CrystalEnterprise.getSessionMgr().logon("username","wpassword","cms name","secEnterprise");

IInfoStore oInfoStore = (IInfoStore) oEnterpriseSession.getService("","InfoStore");

IInfoObjects userInfoObjects = oInfoStore.query ("select si_id,si_name from ci_systemobjects where si_kind='user' and SI_LASTLOGONTIME <=2015.01.01 and si_name not in ('Administrator','Guest')");

for(int i=0;i<userInfoObjects.size();i++)

{

  IInfoObject userInfoObject=(IInfoObject)userInfoObjects.get(i);

  IUser userObject=(IUser)userInfoObject;

  userObject.deleteNow();

}

oInfoStore.commit(userInfoObjects);

}

catch(Exception e)

{

  out.println(e);

}

finally

{

  oEnterpriseSession.logoff();

}

%>

Appreciated for your prompt response.

Thanks & Regards

Srini

[Former Member]

use below , in your code you have a ';' before closing the packages import statements.

<%@ page import = "com.crystaldecisions.sdk.framework.*,

   com.crystaldecisions.sdk.exception.*,

   com.crystaldecisions.sdk.occa.infostore.*,

   com.crystaldecisions.sdk.plugin.desktop.user.*,

   java.util.*"

%>

Restart application server once and test.

Thanks,

Prithvi

[Former Member]

HI Prithvi,

i tried to remove a ';' and tested it. we are getting below error after removing semicolon.

Please advise on this.

Thanks & Regards

Srini

[Former Member]

The error itself says what needs to be done.

Add a semi colon after initializing IEnterpriseSession like

IEnterpriseSession oEnterpriseSession = null;

These are compilation errors and any java resource can help.

[Former Member]

Hi Prithvi,

Thanks for your prompt response.

The script is working fine.

When we are running with the https://xxxx:port/AdminTools/DeleteUsers.jsp. we are not getting any success or failed.

Is there any code to be implemented to show the the message like successfully completed..etc.

Thanks & Regards

Srini

[Former Member]

You can put your comments in out.println().

In the code above if you want to display sucessful message, put and out.print after commint statement, something like

out.println("sucessfull");

Its best to write the name of all the users you are deleting in a log or print the usernames on the screen.

[Former Member]

Hi Prithvi,

Thank you so much for your help on this script.

Could you please provide the code to display the deleted users and their deletion date in to excel or notepad..etc.?

I tried to put the below code however it seems to be not readable format.

out.println("userInfoObjects")

Thanks & Regards

Srini

[Former Member]

You can do something like below.

for(int i=0;i<userInfoObjects.size();i++)

{

  IInfoObject userInfoObject=(IInfoObject)userInfoObjects.get(i);

  IUser userObject=(IUser)userInfoObject;

  userObject.deleteNow();

out.println("User ID :"+userObject.getID()+"<br>User Name : "+ userObject.getTitle()+"<br>");

out.println("=====================================<br>");

}

Refer to the api guide for more details about the interfaces used. Guides available at help.sap.com