Strategy for tracking user recent activity

Question

Our customer would like to know who is online and currently using the custom application we wrote for them. I discussed it with them and this does not need to be exact, more of a guestimate will work.

So my thought is a 15 minute time interval to determine user activity. Some ideas I have for doing this are as follows:

1. Stamp their user record with a date and time of their last activity every time they do something that hits the database, or requests a web page ... this though could be quite database intensive.

2. Send out a "who is online request" from our software, looking for responses, this could be done at a scheduled interval, and then stamp the user record with the current date and time for each response I received.

What are your thoughts? And how would you handle this situation?

Clarification

I would like to use the same architecture for both Windows or the Web if possible. I have a single business logic layer that multiple user interfaces interact with, could be Windows or the Web.

By Windows I would mean client-server.

Clarification

I am using an n-tier architecture so my business objects handle all the interaction with the presentation layer. That presentation layer could be feeding a client-server Windows application, Web application, Web Service and so on.

It is not a high traffic application, as it was developed for a customer of ours, maybe 100 users at most.

Answer 1

[DISCLAIMER 1 --- Java solution]

If each meaningful user is given a Session, then you could write your own SessionListener implementation to track each session that has been created and destroyed.

[DISCLAIMER 2 --- Code not tested or compiled]

public class ActiveSessionsListener implements HttpSessionListener {
    public void sessionCreated(HttpSessionEvent e) {
        ServletContext ctx = e.getSession().getServletContext();
        synchronized (ctx) {
            Integer count = ctx.getAttribute("SESSION_COUNT");
            if (count == null) { count = new Integer(0); }
            ctx.setAttribute("SESSION_COUNT", new Integer(count.intValue() + 1);
        }
    }
    public void sessionDestroyed(HttpSessionEvent e) {
        ... similar for decrement ...    
    }
}

And register this in your web.xml:

<listener-class>com.acme.ActiveSessionsListener</listener-class>

Hope this helps.

Answer 2

I'd just drop a log record table in the db.

UserId int FK
Action char(3) ('in' or 'out')
Time DateTime

You can drop a new record in the table when somebody logs in or out or alternatively update the last record for the user.

Answer 3

If you have session data just use that. Most session systems already have timestamps so they can expire sessions not used for x minutes.

Answer 4

I've seen strategy 1 work before. Of course the site was a small one.

Answer 5

The only problem with a web application solution is you often don't know when someone signs out. Obviously, if you have a login / authentication requirement, you can capture when a person signs on, and as part of your data access code, you can log when a person hits the database. But you will have to accept that there will be on reliable way of capturing when a person logs off - many will just move away from the site without taking the "log off" action.

Answer 6

I've just implemented a last seen system for my website. Your first option is similar, but I only update every +-5 minutes. It works for my situation, but larger scale websites might require something a little extra.

<?php
function updateLastSeen($user_ref, $session_id, $db) { /*Parameters: The user's primary key, the user's session id, the connection to the database*/
  $timestamp = date('Y-m-d H:i:s');
  if ($session_id !== '') {
    /*logged in*/
    $sql_check = "SELECT user_id FROM user_last_seen WHERE user_id = ?";
    $stmt_check = $db->prepare($sql_check);
    $stmt_check->bind_param('s', $user_ref);
    $result_check = $stmt_check->execute();
    $stmt_result_check = $stmt_check->get_result();
    if ($stmt_result_check->num_rows > 0) { /*If the user's last seen was previously recorded, update his record*/
      $sql = "UPDATE user_last_seen SET last_seen = ? WHERE user_id = ?"; 
    } else { /*Otherwise, insert a record for him*/
      $sql = "INSERT INTO user_last_seen (last_seen, user_id) VALUES (?,?)";
    }
    $stmt = $db->prepare($sql);
    $stmt->bind_param('ss', $timestamp, $user_ref);
    $result = $stmt->execute();
  }
}
if( !isset($_SESSION['lastSeen']) ){ /*User logs into the website or lands on the current page, create a lastSeen variable*/
  $_SESSION['lastSeen'] = time();
  updateLastSeen($user_ref, $session_id, $db);
} else {
  $last_seen_time_difference = (time() - $_SESSION['lastSeen']) / 60;
  if ($last_seen_time_difference > 5) { //if the difference between now and the lastSeen is 5 minutes or more, record his last seen.
    updateLastSeen($user_ref, $session_id, $db);   
    $_SESSION['lastSeen'] = time(); /*after updating the database, reset the lastSeen time to now.*/
  }/* else {
    //do nothing. Don't update database if lastSeen is less than 5 minutes ago. This prevents unnecessary database hits.
  }*/
}