Is it possible not to delete user session on logout?

[vicn1222]

Hi,

I am trying to calculate a list of users log into the site in last 30 minutes. However, when user logouts, the user session is deleted immediately.

Is there a way not to delete the registered user's session when he logout, instead let garbage collector delete it after a certain time period?

Thank you.

[xfsgpr]

Does it not show in Users >> User Actions Logs?

Or tell us where are you seeing these logs from.

[vicn1222]

Does it not show in Users >> User Actions Logs?

Or tell us where are you seeing these logs from.

Hi, thank you for your reply.

I am looking at #__session table, this table's time column is updated in real time whenever the user clicks something, which tell he is active at the time. This is what is needed to calculate who is online.

#__action_logs table only records login and logout time. If the user login but then does nothing and never logout, he is really not active.

I wish to have "last active time", not really "last login time" or "last logout time".

"Who is online" module gives wrong #, which I think is caused by looking at wrong table (#__action_logs). If this table has "time" column like the one in #__session table, then "who is online" module will be very accurate.

[vicn1222]

Hmm, after some thought, it may be interesting to combine both #__session table and #__user_action_logs table to get accurate user status, including his last active time: logout is his last active time, or if not logout, time column in #__session table is his last active time, along with his current login/logout status from the action logs table.

But this combination will take some brain…will see…

[AMurray]

I know your question is about J3 but the User Action Log for J4 has an option to delete the log after a number of days; to me the user 'session' and the action log is two different aspects of the system.
Using #_users in the same table as action logs may not work if the Joomla site owner is not using the 'database' session handler and instead using the file system method.

[vicn1222]

I made the calculation. I wish to combine all sql queries in one go, but can't think of a way.

They will return a list of users within specified $minutes, with "user_id", "log_date" (should be last active time), "username", "email", "online" (1 is online, 0 is offline).


Welcome to use the codes.

Code: Select all

class WhosOnline {
  /**
   * calc number of online guests
   *
   * @param int     $minutes
   * @return unknown
   */
  static public function calcGuestsOnline( int $minutes ) : int {
    $db = JFactory::getDbo();
    $query = $db->getQuery( true );

    $query->select( "COUNT( * ) AS cnt" )
    ->from( $db->quoteName( "#__session" ) )
    ->where( "userid = 0" )
    ->where( "time > UNIX_TIMESTAMP() - ${minutes}*60" );

    $db->setQuery( $query );
  
    $cnt = $db->loadResult();

    return $cnt;
  }


  // return user list from #__action_logs table with activity within $minutes
  /**
   * NOTE, log_date is in UTC regardless what time zone has been set in the server!
   *
   * @param unknown $db      (reference)
   * @param unknown $minutes
   * @return unknown
   */
  static private function calcUsersFromActionLogs( &$db, $minutes ) : array {

    $userTable = $db->quoteName( "#__users" );
    $userActionTable = $db->quoteName( "#__action_logs" );

    $subQuery = $db->getQuery( true );
    $subQuery->select( "x.user_id, x.log_date, x.message_language_key, email, username" )
    ->from( $userTable )
    ->from( $userActionTable . " x" )
    ->join( "LEFT", $userActionTable . " y ON x.user_id = y.user_id AND x.log_date < y.log_date" )
    ->where( "y.log_date IS NULL" )
    ->where( "x.user_id = ${userTable}.id" );

    $query = $db->getQuery( true );
    $query->select( "*" )
    ->from( "(" . $subQuery . ") z" )
    ->where( "z.log_date > DATE_SUB( UTC_TIMESTAMP(), INTERVAL ${minutes} MINUTE )" );

    $db->setQuery( $query );

    // arrange by user_id
    $actionUsers = [];

    foreach ( $db->loadObjectList() as &$actionUser ) {

      // convert UTC to local
      $utc_log_date = $actionUser->log_date . " UTC";
      $actionUser->log_date = date( "Y-m-d G:i:s", strtotime( $utc_log_date ) );

      if ( $actionUser->message_language_key == "PLG_ACTIONLOG_JOOMLA_USER_LOGGED_OUT" ) {
        $actionUser->online = 0;
      } else {
        $actionUser->online = 1;
      }
      unset( $actionUser->message_language_key );

      $actionUsers[ $actionUser->user_id ] = $actionUser;
    }

    return $actionUsers;
  }


  /**
   *
   * @param unknown $db         (reference)
   * @param unknown $userIdList
   * @return unknown
   */
  static private function calcUsersFromSession( &$db, $userIdList ) : array {

    $sessionTable = $db->quoteName( "#__session" );
    $userTable = $db->quoteName( "#__users" );

    $idStr = implode( ",", $userIdList );

    $query = $db->getQuery( true );
    $query->select( "userid, time, email" )
    ->from( $userTable )
    ->from( $sessionTable )
    ->where( "userid IN ( ${idStr} ) " )
    ->where( "id = userid" );
    $db->setQuery( $query );

    return $db->loadObjectList();
  }


  /**
   *
   * @param unknown $db         (reference)
   * @param unknown $userIdList
   * @param unknown $minutes
   * @return unknown
   */
  static private function calcUsersNotInActionTable( &$db, $userIdList, $minutes ) : array {

    $sessionTable = $db->quoteName( "#__session" );
    $userTable = $db->quoteName( "#__users" );

    $query = $db->getQuery( true );
    $query->select( "userid AS user_id, time, email, ${userTable}.username" )
    ->from( $userTable )
    ->from( $sessionTable )
    ->where( "userid > 0" )
    ->where( "time > UNIX_TIMESTAMP() - ${minutes}*60" )
    ->where( "id = userid" );

    if ( count( $userIdList ) > 0 ) {
      $idStr = implode( ",", $userIdList );
      $query->where( "userid NOT IN ( ${idStr} ) " );
    }

    $db->setQuery( $query );

    return $db->loadObjectList();
  }


  /**
   * calc a list of online users, convert all time from UTC to local time
   *
   * @param int     $minutes
   * @return unknown
   */
  static public function calcUsersOnline( int $minutes ) : array {

    $timeZone = "US/Central";
    date_default_timezone_set( $timeZone );

    $db = JFactory::getDbo();

    $sessionTable = $db->quoteName( "#__session" );
    $userTable = $db->quoteName( "#__users" );

    $actionUsers = self::calcUsersFromActionLogs( $db, $minutes );

    $userIdList = array_keys( $actionUsers );

    $dateTime = new DateTime;
    $dateTime->setTimezone( new DateTimeZone( $timeZone ) );

    if ( count( $userIdList ) > 0 ) {
      $sessionUsers = self::calcUsersFromSession( $db, $userIdList );

      // reset last active time, convert all time to local time
      foreach ( $sessionUsers as $sessionUser ) {
        $user = $actionUsers[ $sessionUser->userid ];

        $dateTime->setTimestamp( $sessionUser->time );

        $activeTime = $dateTime->format( 'Y-m-d G:i:s' );
        $user->log_date = max( $user->log_date, $activeTime );
      }
    }

    // for whatever reason, some users are listed in session table, but not in action logs table!
    $notInActionUsers = self::calcUsersNotInActionTable( $db, $userIdList, $minutes );
    foreach ( $notInActionUsers as $user ) {
      $dateTime->setTimestamp( $sessionUser->time );
      $user->log_date = $dateTime->format( 'Y-m-d G:i:s' );
      $user->online = "1";
      unset( $user->time );
      $actionUsers[ $user->user_id ] = $user;
    }

    // remove id key
    return array_values( $actionUsers );

  }
  }
  
  /*
$users = WhosOnline::calcUsersOnline( 300 );
print_r( $users );
*/

  

[vicn1222]

There is a typo, should be below

Code: Select all

    foreach ( $notInActionUsers as $user ) {
      $dateTime->setTimestamp( $user->time );  // <-- should be $user here
      $user->log_date = $dateTime->format( 'Y-m-d G:i:s' );
      $user->online = "1";
      unset( $user->time );
      $actionUsers[ $user->user_id ] = $user;
    }

[vicn1222]

Just put the above code into live server, it looks very bad. It appears all activities go into the #__session table, including crawlers and robots.

Is there a way to filter out robots activities so they don't go into the #__session table? Otherwise the online counter is a fake number.