WP Activity Log plugin & activity log database documentation

WP Activity Log stores the activity log data in the WordPress database by default. This knowledge base entry explains how data is stored in the database.

Do note that select WP Activity Log Premium plans offer the option to save data in an external database, Syslog file, or a Log Management System.

WP Activity Log database tables structure & documentation

WP Activity Log creates three tables in the WordPress database. It uses these tables to store the activity log and the plugin’s settings. These three tables are;

• wp_wsal_metadata
• wp_wsal_occurrences
• wp_wsal_sessions

Do note that the wp_ prefix is the default WordPress database prefix. If you have changed yours, the plugin’s table will use your configured prefix.

Table: wp_wsal_metadata

Table structure

The plugin saves the metadata of each event in this table. Metadata includes data such as post titles, post URLs, order status, SKUs, etc. This table has the following columns:

• id: a unique ID that is assigned to every row in the table
• occurrence_id: the ID that correlates the metadata in this table to the alert ID stored in the wp_wsal_occurrences table
• name: the name of the row that describes the data stored in that row 
• value: the actual metadata value, such as post title, URL, etc

You can view event metadata through the Event data inspector by clicking on the More details… button in the Log Viewer. Learn more about WordPress activity log data and metadata.

Table: wp_wsal_occurrences

The plugin saves the event data in this table

• id: A unique ID is assigned to every row in the table
• site_id: The ID of the website in a WordPress multisite network (in non-multisite environments, this is set to 1)
• alert_id: The ID of the event in the activity log (refer to the complete list of event IDs in the WordPress activity log for more information)
• created_on: The date and time in Unix time of when the change happened
• client_ip: The IP address of the entity actioning the activity
• severity: The severity measures the potential impact of the recorded activity (more on activity log severity levels)
• object: The object of the activity
• event_type: The type of activity that took place (more on activity log event types)
• user_agent: The browser user agent
• user_roles: The role of the user actioning the activity
• username: The username of the user that actioned the activity
• user_id: The ID of the user
• session_id: The ID of the session during which the activity took place
• post_status: If the activity concerns a post, the post status (published, draft, future, pending, etc) is recorded here
• post_type: The type of post (such as post, custom post type, or page)
• post_id: The unique WordPress ID of that post

Table: wp_wsal_sessions

The plugin saves users’ session data in this table. Whenever someone logs in to the WordPress website, the plugin keeps a record of this login. When the user logs out, that session data is deleted from the table. As such, in this table, you can see who is logged in to your website at any point in time. The plugin uses this data for the WordPress users’ session management features, such termination of idle WordPress users’ sessions.

This table has the following columns:

• user_id: The ID of the logged-in user
• session_token: The token assigned to that session by WordPress (this is the unique ID)
• creation_time: The time when the session was created (when the user logged in)
• expire_time: The time when the session’s cookies expire (by default, WordPress cookies are valid for 48 hours)
• ip: The IP address from where the user logged in
• roles: The user’s role(s)
• sites: The sites the user is logged in to on a multisite network

Examples of how WordPress activity log data is stored in the database

The wp_wsal_occurences table holds the data that we can see in the Log Viewer. The default view does not show all data. However, you can click the More details… button to view all information pertaining to a single event (including metadata as discussed in the previous section). 

In the next sections, we will look at a couple of examples to showcase actual WordPress activity data as it is stored in the wp_wsal_occurences table.

User login

In this example, we can see an administrator with the username ‘joel’ logging in from a local machine. The alert ID is 1000, which can be cross-referenced with the list of activity log event IDs. The created_on shows us a Unix timestamp of 1690899814.031139, which tells us that the event happened on Tuesday, the 1st of August 2023, at 4:23:34.031 PM.

Plugin installation

In the example below, we see the same user (he likes to keep himself busy) installing a plugin. We know this because the alert ID is listed as 5000.

We can cross-reference this with wp_wsal_metadata table by looking for entries where the occurrence_id field in wp_wsal_metadata matches the id in wp_wsal_occurrences. This tells us that the user installed the CAPTCHA 4WP plugin.

Using the activity log viewer

Use the log viewer to see all the events in the WordPress activity log. When you open the viewer, the plugin retrieves the metadata and event IDs from the WordPress database and uses this data to show the events, as shown in the below screenshot.

To keep database access to a minimum, the plugin only retrieves the data it needs for that view.

Integrating the activity logs with your central logging system

WP Activity Log plugin has out-of-the-box support for mirroring the activity log to Log Management Services such as AWS, CloudWatch, and Loggly. However, if you use a service that we do not support, you can easily integrate the WordPress activity log with your logs management system of choice using the information provided in this document. All you need to do is allow access to the database where the logs are and configure MySQL connectors to read the data.

Alternatively, you can mirror the WordPress website activity log to a log file and configure your logs management system to read directly from the log file.