Overview

Openfire plugin to save the user status to the database.

This plugin automatically saves the last status (presence, IP address, logon and logoff time) per user and resource to userStatus table in the Openfire database.

Optionally you can archive user status entries (IP address, logon and logoff time) for a specified time. History entries are stored in the userStatusHistory table. The settings for history archiving can be configured on the "User Status Settings" page that you'll find on the "Server" tab of the Openfire Admin Console.

Limitations

Of the databases supported by Openfire, this plugin does not support Oracle. This is not due to any technical reason, just nobody has submitted the needed sql script to initialize the userStatus plugin database schema for Oracle.

Database Schema

The userStatus table contains the last status per user and resource:

CREATE TABLE userStatus (
    username        VARCHAR(64)         NOT NULL,
    resource        VARCHAR(64)         NOT NULL,
    online          TINYINT             NOT NULL,
    presence        CHAR(15),
    lastIpAddress   CHAR(45)            NOT NULL,
    lastLoginDate   CHAR(15)            NOT NULL,
    lastLogoffDate  CHAR(15),
    PRIMARY KEY pk_userStatus (username, resource)
);

The userStatusHistory table contains the archived status entries if enabled:

CREATE TABLE userStatusHistory (
    historyID       BIGINT              NOT NULL,
    username        VARCHAR(64)         NOT NULL,
    resource        VARCHAR(64)         NOT NULL,
    lastIpAddress   CHAR(45)            NOT NULL,
    lastLoginDate   CHAR(15)            NOT NULL,
    lastLogoffDate  CHAR(15)            NOT NULL,
    PRIMARY KEY pk_userStatusHistory (historyID)
);