Killing Stale User Sessions Automatically

[former_member476148]

Problem

While working in my client's SAP BI 4.2 environment, we discovered that the BI platform did not always close several user sessions.  Even though those users had closed their browser hours or even days earlier, the BI platform didn't terminate those stale user sessions.  After conducting several tests, and talking with SAP support.  We realized that there are just tricky cases in which the BI platform does not kill all user sessions.

Solution

To prevent a situation where my client’s BI platform becomes a burden by too many orphan user sessions.  I used the SAP BI Platform Java SDK to automate the deletion of old sessions based on what my customer identified as a fair length of time an average session should live.

Putting the Solution to Work

• Since I wanted to scan the BI platform for stale user sessions on a daily basis, I wrote the program in Java and built it as a program object. The program object allowed me to use built-in schedule capabilities of the BI platform to have the Program Job Server execute the program object on a daily basis.


• Within the run() method of the program object, it reads in the following arguments from the program object’s arguments parameter textbox in the CMC:

-ageinminutes is the argument used to pass the number of minutes any session can be alive.  This value is used to calculate the designated kill date for any stale user session.

-excludeids, which is optional, is the argument used to pass a comma-delimited string of CUIDs.  These values are used to exclude active user sessions from being deleted if they are active.  By default, excluded from deletion is the Administrator (which is CUID 12).

Executed is a CMS Query with the derivative of the -ageinminutes argument value,  along with the optional -excludeids argument shown below:

SELECT SI_ID, SI_NAME, SI_CREATION_TIME FROM CI_SYSTEMOBJECTS WHERE SI_KIND = 'Connection' AND SI_NAME != 'System Account' AND SI_AUTHEN_METHOD != 'server-token' AND SI_PARENTID = 41 AND SI_USERID NOT IN (<EXCLUDED CUIDs>) AND SI_CREATION_TIME <= <TARGET KILL DATE> ORDER BY SI_NAME

To find all the targeted stale user sessions to be deleted, which is placed into an InfoObjects collection.

• Then I iterate through the collection of InfoObjects (i.e., the stale user sessions) to execute the delete() command on each one of them.

 

• Then finally I commit the deleted InfoObjects from the InfoStore.

We are done of course until the next day the program object is run again!  😉

Source Code

Unfortunately, due to file limitations of the blog site, below is the source code for the program object along with the list of JARs you need to execute it:

KillUserSessions.java

/*****************************************************************************************************

 * Author : Jeffrey Jonathan Jennings                                                                *

 * Module : KillUserSessions.java                                                                    *

 * Purpose: The purpose of this program object's class is to delete all non-excluded active user     *

 *          sessions that is equal or passed its designated kill age.                                *

 *****************************************************************************************************/



import com.crystaldecisions.sdk.exception.SDKException;

import com.crystaldecisions.sdk.framework.IEnterpriseSession;

import com.crystaldecisions.sdk.plugin.desktop.program.IProgramBase;

import com.crystaldecisions.sdk.occa.infostore.*;

import java.text.*;

import java.util.*;



public class KillUserSessions implements IProgramBase

{

	private static final String _CONST_STR_DEFAULT_EXCLUDE_USERIDS = "12";		//--Default always exclude the original BI platform Administrator



	private int _AgeInMinutes;

	private String _ExcludeUserIDs;

	private boolean _NoKill;



	// --- App Log Level Enumerator

	private enum enumAppLogLevel

	{

		INFO("Info"),

		WARNING("Warning"),

		ERROR("Error");



		private final String _strText;



		private enumAppLogLevel(final String strText)

		{

			this._strText = strText;

		}



		public String toString()

		{

			return _strText;

		}

	}



    /* ----------------------------------------------------------------------------------------------

       | Name     : getAgeInMinutes()                                                               |

       | Purpose  : This accessor method returns the value of the _AgeInMinutes private variable.   |

       ---------------------------------------------------------------------------------------------- */

    private int getAgeInMinutes()

    {

        return this._AgeInMinutes;

    }



    /* ----------------------------------------------------------------------------------------------

       | Name     : SetAgeInMinutes(intAgeInMinutes)                                                |

       | Purpose  : This mutator method sets the value of the _AgeInMinutes private variable.       |

       ---------------------------------------------------------------------------------------------- */

    private void SetAgeInMinutes(int intAgeInMinutes)

    {

        this._AgeInMinutes = intAgeInMinutes;

    }



    /* ----------------------------------------------------------------------------------------------

       | Name     : getExcludeUserIDs()                                                             |

       | Purpose  : This accessor method returns the value of the _ExcludeUserIDs private variable. |

       ---------------------------------------------------------------------------------------------- */

    private String getExcludeUserIDs()

    {

        return this._ExcludeUserIDs.trim();

    }



    /* ----------------------------------------------------------------------------------------------

       | Name     : SetExcludeUserIDs(strExcludeUserIDsString)                                      |

       | Purpose  : This mutator method sets validates that this is comma delimited numeric value   |

       |            string.  Then it rebuilds the string and stores the string in the               |

       |            _ExcludeUserIDs private variable.  However, if the entire string value is       |

       |            invalidate the _CONST_STR_DEFAULT_EXCLUDE_IDS value is substituted instead.     |

       ---------------------------------------------------------------------------------------------- */

    private void SetExcludeUserIDs(String strExcludeUserIDsString)

    {

    	if((strExcludeUserIDsString != null) && (strExcludeUserIDsString != ""))

    	{

    		this._ExcludeUserIDs = _CONST_STR_DEFAULT_EXCLUDE_USERIDS + ",";

    		String[] strExculedIDsBrokenOut = strExcludeUserIDsString.trim().split(",");

    		for(int intIndex = 0; intIndex < strExculedIDsBrokenOut.length; intIndex++)

    		{

    			try

    			{

    				Long.parseLong(strExculedIDsBrokenOut[intIndex].trim());

    				this._ExcludeUserIDs += strExculedIDsBrokenOut[intIndex] + ",";

    			}

    			catch(NumberFormatException e)

    			{

    			}

    		}

    		this._ExcludeUserIDs = this._ExcludeUserIDs.substring(0, this._ExcludeUserIDs.length() - 1);

    	}

    	else

    		this._ExcludeUserIDs = _CONST_STR_DEFAULT_EXCLUDE_USERIDS;

    }



    /* ----------------------------------------------------------------------------------------------

       | Name     : getNoKillFlag()                                                                 |

       | Purpose  : This accessor method returns the value of the _NoKill private variable.         |

       ---------------------------------------------------------------------------------------------- */

    private boolean getNoKillFlag()

    {

        return this._NoKill;

    }



    /* ----------------------------------------------------------------------------------------------

       | Name     : SetNoKillFlag(blnReportOnly)                                                    |

       | Purpose  : This mutator method sets the value of the _NoKill private variable.             |

       ---------------------------------------------------------------------------------------------- */

    private void SetNoKillFlag(boolean blnNoKill)

    {

    	this._NoKill = blnNoKill;

    }



	/* ----------------------------------------------------------------------------------------------

       | Name     : setClassPropertiesBasedOnCmdLineArgs(strCmdLineArgs)                            |

       | Purpose  : Set class properties based on reading arguments from the Argument textbox in the|

       |            Program Parameters dialog box page.  Moreover, the method returns true as long  |

       |            as the '-ageinminutes' (mandatory) and if the '-excludeuserids' (optional) are  |

       |            supplied.  Otherwise, false is returned.                                        |

       ---------------------------------------------------------------------------------------------- */

	private boolean setClassPropertiesBasedOnCmdLineArgs(String[] strCmdLineArgs)

	{

		boolean blnMandatoryArgSupplied = false;



		// ---Set default value(s)

		SetExcludeUserIDs("");

		SetNoKillFlag(false);



		if(strCmdLineArgs.length > 0)

			for(String strArgument : strCmdLineArgs)

				if(strArgument.contains("-ageinminutes"))

				{

					SetAgeInMinutes(getIntegerValue(strArgument));

					blnMandatoryArgSupplied = true;

				}

				else

					if(strArgument.contains("-nokill"))

						SetNoKillFlag(true);

				else

					if(strArgument.contains("-excludeuserids"))

						SetExcludeUserIDs(getArgumentValue(strArgument));

					else

					{

						AppLogEntry(enumAppLogLevel.ERROR, "Invalid argument supplied to the program object.  Only '-ageinminutes' (mandatory), '-excludeuserids' (optional), and '-nokill' (optional) are allowed.");

						return false;

					}





		if(!blnMandatoryArgSupplied)

			AppLogEntry(enumAppLogLevel.ERROR, "'-ageinminutes' is a mandatory argument and must be supplied to the program object.");

		else

		{

			AppLogEntry(enumAppLogLevel.INFO, "-ageinminutes  : " + getAgeInMinutes());

			AppLogEntry(enumAppLogLevel.INFO, "-excludeuserids: " + getExcludeUserIDs());

			AppLogEntry(enumAppLogLevel.INFO, "-nokill        : " + getNoKillFlag());

			AppLogEntry(enumAppLogLevel.INFO, "---------------------------------------");

		}



		return blnMandatoryArgSupplied;

	}



	/* ----------------------------------------------------------------------------------------------

       | Name     : getArgumentValue(strArgument)                                                   |

       | Purpose  : Parses out the argument value.                                                  |

       ---------------------------------------------------------------------------------------------- */

	private String getArgumentValue(String strArgument)

	{

		return strArgument.substring(strArgument.indexOf(":") + 1, strArgument.length());

	}



	/* ----------------------------------------------------------------------------------------------

       | Name     : getIntegerValue(strValue)                                                       |

       | Purpose  : Parses out the argument value.                                                  |

       ---------------------------------------------------------------------------------------------- */

	private int getIntegerValue(String strValue)

	{

		try

		{

			return Integer.parseInt(getArgumentValue(strValue).trim());

		}

		catch(NumberFormatException e)

		{

			return 0;

		}

	}



	/* ------------------------------------------------------------------------------------------------------------

       | Name     : AppLogEntry(AppLogLevel, strEntry)                                                            |

       | Purpose  : This method inserts an app log entry into the Program Object log file.                        |

       ------------------------------------------------------------------------------------------------------------ */

	private void AppLogEntry(enumAppLogLevel AppLogLevel, String strEntry)

	{

		String strLogLevel = "";



		try

		{

			strLogLevel = (AppLogLevel.toString() != null) ?  AppLogLevel.toString() : "Unknown";

		}

		catch(Exception e)

		{

			strLogLevel = "Unknown";

		}

		System.out.println("[" + strLogLevel + "] " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + " " + strEntry);

	}



	/* ------------------------------------------------------------------------------------------------------------

       | Name     : AppLogEntry(strEntry)                                                                         |

       | Purpose  : This method inserts an app log entry into the Program Object log file.                        |

       ------------------------------------------------------------------------------------------------------------ */

	private void AppLogEntry(String strEntry)

	{

		System.out.println(strEntry);

	}



    /* ----------------------------------------------------------------------------------------------

       | Name     : run(iesSession, iisInfoStore, strCmdLineArgs)                                   |

       | Purpose  : The BI platform Program Job Server calls this method when executing this class  |

       |            as a schedulable BI platform Java Program Object.  In which, the objective of   |

       |            this method is to delete all non-excluded active user sessions that is equal or |

       |            passed its designated kill age.                                                 |

       ---------------------------------------------------------------------------------------------- */

	public void run(IEnterpriseSession iesSession, IInfoStore iisInfoStore, String[] strCmdLineArgs) throws SDKException

	{

		if(setClassPropertiesBasedOnCmdLineArgs(strCmdLineArgs))

			try

			{

				boolean blnGetMoreResults = true;

				SimpleDateFormat simpleDateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");

				DecimalFormat df2Digits = new DecimalFormat("##"), df4Digits = new DecimalFormat("####");



				// --- Setup column headers for the text-based report

				AppLogEntry("status|user_name|current_date|kill_date|created_date|age_in_minutes|");



				while(blnGetMoreResults)

				{

					// -- Get the timestamp that the session should die on

					Calendar calendar = Calendar.getInstance();

					Date dteCurrent = calendar.getTime();

					calendar.add(Calendar.MINUTE, 0 - getAgeInMinutes());

					String strAgeTimestamp = df4Digits.format(calendar.get(Calendar.YEAR)) + "." + df2Digits.format(calendar.get(Calendar.MONTH) + 1) + "." + df2Digits.format(calendar.get(Calendar.DAY_OF_MONTH)) + "." + df2Digits.format(calendar.get(Calendar.HOUR_OF_DAY)) + "." + df2Digits.format(calendar.get(Calendar.MINUTE));



					// -- Query the CMS for any sessions that should be killed based on the value of 'strAgeTimestamp

					IInfoObjects iioObjects = iisInfoStore.query("SELECT SI_ID, SI_NAME, SI_CREATION_TIME " +

			                              						 "FROM CI_SYSTEMOBJECTS " +

			                              						 "WHERE SI_KIND = 'Connection' AND " +

			                              						 	   "SI_NAME != 'System Account' AND " +

			                              						 	   "SI_AUTHEN_METHOD != 'server-token' AND " +

			                              						 	   "SI_PARENTID = 41 AND " +

			                              						 	   "SI_USERID NOT IN (" + getExcludeUserIDs() + ") AND " +

			                              						 	   "SI_CREATION_TIME <= '" + strAgeTimestamp + "' " +

			                              						 	   "ORDER BY SI_NAME");



					if (iioObjects != null)

						if (iioObjects.size() != 0)

						{

							for(int intIndex = 0; intIndex < iioObjects.size(); intIndex++)

							{

								IInfoObject iioObject = (IInfoObject) iioObjects.get(intIndex);

								if(iioObject != null)

									try

				                	{

										String strRow = "|" + ((String)iioObject.properties().getProperty("SI_NAME").getValue()) + "|" + simpleDateFormat.format(dteCurrent) + "|" + simpleDateFormat.format(calendar.getTime()) + "|" + simpleDateFormat.format((Date)iioObject.properties().getProperty("SI_CREATION_TIME").getValue()) + "|" + String.format("%,d", (dteCurrent.getTime() - ((Date)iioObject.properties().getProperty("SI_CREATION_TIME").getValue()).getTime()) / 1000L / 60L) + "|";

										/*

										 * If '-nokill' argument was passed to the program object, the user session will not be killed.

										 */

										if(!getNoKillFlag())

										{

											iioObjects.delete(iioObject);

											AppLogEntry("killed" + strRow);

										}

										else

											AppLogEntry("active" + strRow);

				                	}

				                	catch(SDKException e)

				                	{

				                		AppLogEntry(enumAppLogLevel.WARNING, "There was a problem with the InfoObject just retrieved from the CMS database because of " + e.getErrorCodeString() + " " + e.getDetailMessage() + ".  However, continuing execution of the program object.");

				                	}

							}



							/*

							 * If '-nokill' argument was passed to the program object, the user session will not be killed.  Therefore, nothing to commit.

							 * Otherwise, all deleted user sessions (if any) are committed to the CMS System database

							 */

							if(!getNoKillFlag())

								iisInfoStore.commit(iioObjects);

						}

					blnGetMoreResults = (iioObjects.getResultSize() > iioObjects.size()) ? true : false;

				}

			}

			catch(SDKException e)

			{

				AppLogEntry(enumAppLogLevel.ERROR, "User Session(s) to Kill CMS query failed to execute because of " + e.getErrorCodeString() + " " + e.getDetailMessage());

			}

	}

}

JARs to include with the Program Object

The JARs below are supplied by SAP, and can be found in your SBOP installation subfolder ../java/lib:

• Boconfig.jar


• Cecore.jar


• Celib.jar


• Ceplugins_core.jar


• Cesession.jar


• Corbaidl.jar


• Ebus405.jar

About me

My name is Jeffrey Jonathan Jennings, a BusinessObjects consultant since 1996.  With specialization in Data Analytics, Customization, and Training.

Disclaimer  🙂

I am releasing this work as is.  Nevertheless, please feel free to leave a comment if you have any questions.