This document explains how the plugin stores the activity log in the WordPress database. You can also use the external database feature to save the WordPress activity log in an external database, thus improving your WordPress’ security posture while ensuring it meets all regulatory compliance requirements.
Does WP Activity Log affect the website database performance?
In short no. Read the Does keeping a WordPress activity log slow down my website for a more detailed explanation.
WP Activity Log database tables structure & documentation
The WP Activity Log plugin creates three tables in the WordPress database. It uses these table to store the activity log and the plugin’s settings. The names of these three tables are;
Note: wp_ is the default WordPress database prefix. If you have changed your the plugin’s table will used your configured prefix.
The plugin saves the metadata of each event in this table. The name of a post, post ID, the user’s IP address, username, role and such data are all metadata. This table has the following columns:
- id – a unique ID which is assigned to every row in the table
- occurrence_id – the ID that correlates the meta data 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
How is data stored in wp_wsal_metadata
When the plugin records a change on the website, it stores the following information in this table. This information is about the actual change:
- Client IP address: the IP address of the user accessing WordPress
- CurrentUserID: the ID of the WordPress user
- CurrentUserRoles: the user’s role or roles on WordPress
- UserAgent: the user agent string of the user’s browser
- Severity: the severity level of the activity log event (more on activity log severity levels)
- Object: the object about which the event is about (more on object metadata in the activity log)
- EventType: the type of event (more on activity log event types)
Additionally, the plugin also saves data about the actual change. So for example, if the change is about a post, the plugin also saves the following:
- PostID: the unique ID of that post on WordPress
- PostTitle: the title of the post
- PostType: the type of post (such as post, custom post type or page)
- PostStatus: the status of the post (published, draft, future, pending etc)
- PostURL: the URL of the post
If the change is about a plugin, or a theme, the plugin saves the following:
- PluginFile: the path to the plugin’s main file and the filename
- PluginData: the name of the plugin
- Theme File: the path to the theme’s main file and the filename
- ThemeData: the name of the them
Example of the data stored in wp_wsal_metadata
The below screenshot shows the metadata stored in the wp_wsal_metadata table when a WordPress user creates a new blog post:
From the above screenshot we can see that the WordPress user with ID 1. The user’s role is administrator and the post ID is 7. The post’s title is Activity logs for all websites and the post type is post (blog post). The URL of the post is http://wsal.wpwhitesecurity.net/techincal.activity-logs-for-all-websites.
The metadata stored in the database can differ depending on the type event. For example, if the event is reporting a post URL change, instead of the PostURL row the plugin creates OldURL and NewURL rows. These entries are used to stores both the old and new URL, which are shown in the activity log.
For a complete list of possible metadata entries in the WP Activity Log tables refer to the defaults.php file in the root of the plugin installation directory.
The occurrence_id column
In the above screenshots you will notice that the occurrence_id of each row for each activity log event is the same. That is because all that data belongs to the same event / change on the website. In the next section we will explain the correlation between the occurrence_id and the Alert_id in the wp_wsal_occurrences table.
The plugin saves the event ID, the data and other similar data. This table has the following columns:
- id – a unique ID is assigned to every row in the table
- site_id – the ID of the website in a WordPress multisite network
- 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 milliseconds of when the change happened
- is_read – this is not used (for future features)
- is_migrated – this is a marker used by the archiving feature
The below screenshot shows the data stored in the wp_wsal_occurrences table. It also highlights the event IDs of the two example events we used in the previous section:
From the above screenshot we can see that the highlighted occurrences have an event ID 2016 and 2017. These are the event IDs of the sample events we used earlier in this article.
With this information we now know that all the metadata in the wp_wsal_metadata table that has an occurrence_id of 23 is for an event with ID 2016 (user published a post), and an event with ID 2017 (user changed the URL of a post).
The plugin saves the settings in this table. For example it saves the retention policies, list of excluded objects, the email and SMS notifications triggers etc. This table will remain in the WordPress database even when you store the activity logs in an external database. It contains the connection string of the external database, so it cannot be moved.
Note: we are currently in the process of migrating the plugin settings from he wp_wsal_options table to the default WordPress options table; wp_options. So if you do not find a setting in this table, look for it in the default WordPress settings table.
The plugin saves the users sessions data in this database. 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. So in this table you can see who is logged in to your website at this very moment. The plugin uses this data for the WordPress users sessions management features, such as to terminate idle WordPress users sessions.
This table has the following columns:
- id – a unique ID is assigned to every row in the table
- user_id – the ID of the logged in user
- session_token – the token assigned to that session by WordPress
- 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 role(s) of the user
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
With all this information in hand you can easily integrate the WordPress activity logs with your centralized logging system, such as Splunk. All you need to do is allow access to the database where the logs are and configure MySQL connectors to read the data.