logging user logins for the purpose of reporting that the client is exceeding number of licenses

Question

Hello

I am the lead developer on a commercial Windows app (c#). A new requirement is to track customers who abuse the license.

For example: Let's say a customer purchases a 10 user license agreement, i.e. 10 simultaneous users at any given time.

I need to be able to report, looking back at history, all the times that customer had more than 10 users logged in at the same time.

I already have a User table with columns: userid (primary key), pw, lastLogin, lastLogout.

I was thinking about creating a a new 'logging' table in which a new row is added each time a user logs out...columns might include:

LogId, UserId, LoginDateTime, LogoutDateTime

...and then I would have a history of every time a user logs in/out of the app...

but I'm not sure if this table design will lend to efficient calculations for reporting...whether I use SQL or c# to perform the calculations does not matter to me, as long as it is reasonably fast...

Hoping someone might have a good idea about how to better design this table so that I can quickly calculate any/all points in time when the customer exceeded the license limit.

Note: I do not want to block the 11t, 12th etc. user from using the app...the requirement is to display a warning message to the user but to allow him to continue working...

Answer 1

The problem with having a table structure of (LogId, UserId, LoginDateTime, LogoutDateTime) is that it becomes difficult to aggregate - queries to see everyone using the system at a specified time are easy, but getting the number of users at all times is hard.

One possible approach would be to denormalise the data a bit - and record units of use, instead of spans. You could track usage in 30 minute blocks with the table structure (UserId, BlockDateTime).

If BettyR used the system from 10:05am until 11:45am, create four records in your table:

BettyR, 10:00am
BettyR, 10:30am
BettyR, 11:00am
BettyR, 11:30am

You can then use a normal SQL query with a group by clause to find the number of distinct users who made use of the system during each half hour period.

select ...
from UsageBlocks
group by BlockDateTime
having count(*) > 10

Depending on your requirements, you could then perform deeper analysis on those specific time periods using a reporting application.

Answer 2

A different option is to create a user sessions table. When a user logs in, insert their session into the table. When they log out, delete it. That way you always have an easy way to find out how many users are connected.

Then you can create a trigger on insert (and maybe delete) and you can log from there based on the session count. If you log delete as well you have an idea of the length of time they used an excessive amount of users.

Also keep in mind that you'll have to clean up orphaned connections appropriately.

Answer 3

Think about starting and ending sessions as events that need to be recorded. You create a single table to record these events.

So, a session that starts and ends will have two entries in the table - one for the session start and one for the session end. You only ever add records to the table, never modifying previous records. The table can keep track of the number of open sessions very simply now by adding a "session count" field to the record that is incremented from the previous record's session count value when a session start event occurs and decremented when a session end event occurs.

The "session count" column now gives us a piece-wise continuous funtion over time of the number of concurrent sessions.

Example data:

    SessionId  EventType  .... your session data here ... SessionCount   
1.     1         Login         ................                 1
2.     2         Login         ................                 2
3.     3         Login         ................                 3
4.     1         Logout        ................                 2
5.     4         Login         ................                 3
6.     4         Logout        ................                 2
7.     2         Logout        ................                 1
8.     3         Logout        ................                 0
9.     5         Login         ................                 1
10.    6         Login         ................                 2

Things to worry about:

1. You must make sure you pair begin/end events, so in the event of any failure, session end events must still be generated.
2. Do you need the table to be tamper proof? If so, I have a technique for that also.

EDIT: please note that where I put "your session data here", I really meant "your session event data here". Information such as a time stamp would go in here. Another table should be used to track session information common to both events, such as the identity of the user that owns the session (use the same SessionId key for both tables).

Answer 4

guys

I am very impressed with the 4 potential solutions so far...very creative and none of them I had thought of...I cant vote them up though since I am a new user....is there some way I can give you 3 guys a thumbs up ?

Daniel...tampering could be an issue as this database lives on the each customer's box...the db is in SQL Server 2005...what do you suggest?

Answer 5

I'd recommend a phone home feature, something like alerting you or logging via a web method so you can track who is doing what...but only when they exceed their limit. (research WCF for a couple ways to do this)

That way you could keep track of their usage. Charging them extra or renegotiating their contract becomes meaningful with real data. If you are good at customer service then this shouldn't ruffle their feathers much, especially if you point out that you need an infrastructure for providing service on an elavated user base. Presumably you make it quite clear to them about your licensing scheme too...

Answer 6

I would be wary to implement something like license checking this way, because (as noted in Daniel's answer already, you don't get logout event in case your app crashes, or possibly even if user quits it in a specific way (End Process from Task Manager, ALT+F4, instead of going through File | Exit, etc).

So you could conceivably have a growing portion of dead sessions as users use the application. Not only you could get wrong data and impression about your users, but more importantly, they would grow frustrated by receiving warning messages they don't deserve. And if you try to tell them they are violating the license when they are actually not, they could become quite affronted. A good way to lose customers.

I would say a reasonable license checking scheme requires central server outside customer control, and for customer to have Internet access. It could go something like this:

1. on starting application, program informs license server of a new session;
2. during application work, program periodically (let's say 1x 5mins or so) sends a status signal to license server;
3. on exiting program normally, it sends session end signal to license server;
4. if no status signal is received in 10mins from program, session is considered terminated by license server anyway.

Answer 7

Note: This is a problem that's been dealt with before. The best option I've seen is to maintain an MRU stack of user activity by unlogged-out users (in this case depth 10), and derived from database transaction activity - SELECT TOP 10 DISTINCT usercode FROM atable WHERE (most recent transaction type wasn't "log off") ORDER BY timestamp DESC. As soon as someone accesses the system that isn't among the most recent 10, you have two options. You allow them to log in, and bump the oldest to logged out status (which creates hassle, but lets them continue their work); or you make them wait until one of the top 10 explicitly logs out. Somtimes the user gets to make the choice. Sometimes it requires an administrator. But that's a policy distinction.

Then you bill them for every time they log in (using the first scheme) or you just enforce the limit (with the second scheme).