WP Security Audit Log plugin is a WordPress activity log and monitoring plugin that keeps an audit trail of everything that is happening on your WordPress and WordPress multisite networks. For every activity the plugin identifies it creates a WordPress security alert in the audit log. These WordPress security alerts and the plugin settings are stored in three tables in the WordPress database.
This document explains how the WordPress security alerts are stored in the WordPress database and how you can use the Premium Database & Integration Tools to store the WordPress audit trail in an external database so to improve your WordPress’ security posture and performance, and ensure it meets all regulatory compliance requirements.
Does WP Security Audit Log Affect the WordPress Database & Website Performance?
In short no. Read the Does keeping a WordPress activity log slow down my website for a more detailed explanation.
WP Security Audit Log Database Structure
Upon installing the WP Security Audit Log plugin it creates three tables in the WordPress database where it stores the WordPress activity log and the plugin’s settings;
Note: wp_ is the default WordPress database prefix. If you have changed your the plugin’s table will used your configured prefix.
What Data is Stored in the WP Security Audit Log Tables?
In this table the metadata for each WordPress security alert is stored. For example the name of the post that was changed, the user’s source IP address, the date and time etc. This table is made up of 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
Once WP Security Audit Log identifies a WordPress change, such as when a user creates a new blog post is created, the following information is written in the table:
- Client IP: the IP address of the user accessing WordPress
- CurrentUserID: the ID of the WordPress user
- CurrentUserRoles: the user’s role or roles on WordPress
- Post ID: 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
- UserAgent: the User-Agent string of the web browser
Below is a screenshot of the metadata stored in the wp_wsal_metadata table when a WordPress user creates a new blog post:
From the above screenshot you can see that the WordPress user with ID 5 that has an Editor role created a new post which has an ID of 47. The post’s title is WP Security Audit Log Update and the post type is a blog post (post). The URL of the post is http://www.local.com/plugins/wp-security-audit-log-update.
The metadata stored in the WordPress database can differ depending on the type of WordPress security alert. For example if the alert is reporting a post URL change, instead of a PostURL row the plugin will create OldURL and NewURL rows to store both the old URL and new URL which are shown in the WordPress security alert, as highlighted in the below screenshot.
For a complete list of possible metadata entries in the WP Security Audit Log tables refer to the defaults.php file in the root of the plugin installation directory.
From the above screenshots you can notice that the occurrence_id of each row entry for each WordPress security alert is the same. This occurrence_id is used to correlate the metadata to the alert ID stored in the wp_wsal_occurrences table as explained below.
In this table the alert IDs are stored. This table contains the following columns:
- id – a unique ID assigned to every row in the table
- site_id – used in a WordPress multisite installation, this column contains the ID of the site to which the WordPress security alert is related to.
- Alert_id – the ID of the WordPress security alert. For more information refer to the complete list of WordPress security alerts.
- Created_on – the data and time in milliseconds of when the change happened
- is_read – this is not used (for future features)
- is_migrated – this is not used (for future features)
Below is a screenshot of how the WordPress security alerts IDs are stored in the wp_wsal_occurrences table:
From the above screenshot you can see that the highlighted occurrences have an ID of 366 and 367 which are the sample WordPress security alerts we used in the first and second screenshot of this article. Hence we can conclude that all the metadata in the wp_wsal_metadata table that has an occurrence_id of 366 is an Alert 2001, which means A user created a new blog post. All the metadata in the wp_wsal_metadata table that has an occurrence_id of 367 is an Alert 2017, which means A user changed the URL of a blog post.
This table is where all the plugin settings are saved, such as the WordPress security alerts pruning settings, list of excluded objects from monitoring, list of plugin editors etc. The WordPress email notifications triggers which you can create with the Email Notifications premium feature are also stored in this table.
Note: When you use the External DB add-on to store the WordPress audit trail in an external database this table is not moved because it contains the connection string that is used to connect to the external database.
Using the Audit (activity) Log Viewer
The Audit Log viewer allows you to view all the security alerts stored in your WordPress audit trail. Once you access the Audit Log viewer the plugin retrieves the metadata and alert IDs from the WordPress database and displays the data as WordPress security alerts, as shown in the below screenshot.
To ensure database access is kept to a minimum the WordPress Audit Log Viewer only retrieves the data for the WordPress security alerts it needs to display. Therefore when you switch between the pages in the Audit Log Viewer the plugin retrieves only a small amount of data from the WordPress database, thus ensuring efficient database access.
Integrating the WordPress Security Audit Log in Your Central Logging System
With all this information in hand you can easily integrate the WordPress security audit log in your centralized logging system, such as Splunk. All you need to do is allow remote access to the WordPress database and configure MySQL connectors to read the data from the WP Security Audit Log tables in the WordPress database.