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.

Dementia

The strategy for this can be quite different depending on whether it's a web application/client-server etc - I think we need a bit more detail on the stack/architecture involved – Victuals

agree, is this a web app or not? – Kenelm

If I could make it work for client-server and a web application that would be the best. – Dementia

Answer 1

Our solution is to maintain a "Transaction" table (which follows what was done), in addition to our "Session" table (which follows who was here). UPDATE, INSERT and DELETE instructions are all managed through a "Transaction" object and each of these SQL instruction is stored in the "Transaction" table once it has been successfully executed on the database (depending on tables updated: we have the possibility to specifically follow some tables and ignore others). This "Transaction" table has other fields such as transactiontType (I for INSERT, D for DELETE, U for UPDATE), transactionDateTime, etc, and a foreign key "sessionId", telling us finally who sent the instruction. It is even possible, through some code, to identify who did what and when (Gus created the record on monday, Tim changed the Unit Price on tuesday, Liz added an extra discount on thursday, etc).

Pros for this solution are:

1. you're able to tell "what who and when", and to show it to your users! (you'll need some code to analyse SQL statements)
2. if your data is replicated, and replication fails, you can rebuild your database through this table

Cons are

1. 100 000 data updates per month mean 100 000 records in Tbl_Transaction
2. Finally, this table tends to be 99% of your database volume

Our choice: all records older than 90 days are automatically deleted every morning

Mildew

A way to implement this idea decoupling services is using a Publish/Subscribe message queue like RabbitMQ. Every time an event changes the state of your system you publish a message in the queue and all subscriber will receive the event and update their DBs. For a high traffic site, Kafka is probably the best option to handle thousand of events per second. – Embody

Answer 2

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

Strew

Answer 3

I wonder how a site like stackoverflow does it?

They must target a specific event, as I just tooled around the site, take a look at my profile, and still says something like last seen 8 minutes ago.

Dementia

The results from my answer produces this, except that my allowance is set to 5 minutes. – Arbour

Answer 4

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.

Uniliteral

Answer 5

I have worked with many systems that have utilized the first method you listed, with a little careful planning it can be done in a manner that really doesn't have much of an effect.

It all depends on exactly when/how/what you are trying to track. If you need to track multiple sessions I'll typically see people that use a session system tied to a user account, and then by a specific elapsed time that session is consiered dead.

If you are truly looking for currently online, your first option is the best.

Peep

Answer 6

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

Raney

Answer 7

You can increment a global variable everytime a user session is created, and decrement it when it is destroyed. This way you will always know how many users are online at any given moment.

If you want to monitor it over time, on the other hand, I think logging session start and end to the database is the best option, and you calculate user activity after the fact with a simple query.

Gallman

Answer 8

[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.

Babby

Answer 9

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.

Hauberk

Answer 10

I would imagine that using a trigger would be a reasonable option that would preclude you from having to mess with any logic differences between the web and the non-web environment (or any other environment for that matter). However, this only captures changes to the environment and doesn't do anything when select statements are made. This, however, can be overcome if all your commands from your apps are run through stored procedures.

Prang

Answer 11

With a web app, the concept of "online" is a little nebulous. The best you can really do is "made a request in the last X minutes" or maybe "authenticated in the last X minutes".

Choose a set of events (made request, performed update, authenticated, ...), and log them to a DB table.

Log them to a table in a separate DB

Observant

Answer 12

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.
  }*/
}

Arbour