Collect logs from WordPress, PrestaShop, and Drupal
Most popular Content Management Systems (CMS) use a MySQL database to store content, and in many cases, audit logs as well. Some logging is external to the CMS, such as the access and error logs that web servers like Nginx or Apache generate while serving the content and handling client requests (see how to collect Nginx logs or Apache logs using NXLog Agent). Audit log data, or any type of logs, can be retrieved from a CMS database and processed with NXLog Agent using the im_odbc module.
The primary focus of this guide is on collecting audit logs. However, the principles presented here can be applied to other CMS systems and log types.
Database location and structure
Each CMS presented here has a built-in logging feature that stores logs in its database. This guide covers the audit logging capabilities of three commonly used CMS systems: WordPress, PrestaShop, and Drupal.
WordPress
WordPress contains an activity log plugin called WP Activity Log, which is installed separately. It logs user behavior patterns for easy troubleshooting if suspicious activity is detected. From the admin dashboard, the Log Viewer option under the plugin name shows all events by retrieving event IDs and metadata from the database.
The plugin creates two tables in the WordPress database that store the audit log:
-
wp_wsal_metadata -
wp_wsal_occurrences
| Column | Type | Description |
|---|---|---|
id |
|
Primary Key - a unique ID assigned to every row in the table |
occurence_id |
|
The ID that correlates the metadata in this table to the alert ID stored in the |
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 |
| Column | Type | Description |
|---|---|---|
id |
|
Primary Key - a unique ID assigned to every row in the table |
site_id |
|
The ID of the website in a WordPress multi-site network |
alert_id |
|
The ID of the event in the activity log |
created_on |
|
The date and time in milliseconds of when the change happened |
PrestaShop
PrestaShop has its built-in logging feature accessible from the admin dashboard. To access, click on Advanced Parameters under the Configure tab, then the Logs option. This page shows all the actions performed in the shop.
The ps_log table stores the audit log in the PrestaShop database.
| Column | Type | Description |
|---|---|---|
id_log |
|
Primary Key - a unique ID assigned to every row in the table |
severity |
|
The level of severity between 1-4 |
message |
|
The action that the user performed on the site |
id_lang |
|
The language ID used |
in_all_shops |
|
The shop context used |
id_employee |
|
The ID of the employee making the change |
date_add |
|
The date and time in milliseconds of when the change happened |
date_upd |
|
The date and time in milliseconds of when the change was updated |
Drupal
Drupal also has its built-in logging feature accessible from the admin dashboard. To access, click on the Manage tab, navigate to the Reports drop-down option, then select Recent log messages. This page displays all the logged system events.
The watchdog table stores the audit log in the Drupal database.
| Column | Type | Description |
|---|---|---|
wid |
|
Primary Key - a unique ID assigned to every row in the table |
uid |
|
The user ID of the user making the change |
type |
|
The error type |
message |
|
The action that the user performed on the site |
severity |
|
The level of severity between 1-8 |
location |
|
The URL where that was accessed |
hostname |
|
The IP Address of the host accessing the site |
timestamp |
|
The date and time in milliseconds of when the change happened |
Collecting CMS systems audit logs
This example configuration uses the im_odbc module to read data from the wp_wsal_metadata and wp_wsal_occurences tables. JOINS is used in the SQL query to return one column for every value of wp_wsal_metadata.name.
<Extension json>
Module xm_json
</Extension>
<Input odbc>
Module im_odbc
# This is the connection string containing the ODBC data source name
ConnectionString DSN=wordpress; database=wordpress;
# This is the SQL statement that the module will execute to query data from the data source
SQL SELECT o.id AS id, \
o.alert_id AS AlertID, \
FROM_UNIXTIME(o.created_on) AS EventTime, \
ma.value AS Attempts, \
mcl.value AS ClientIP, \
mcui.value AS CurrentUserID, \
mcur.value AS CurrentUserRoles, \
met.value AS EventType, \
mfn.value AS FirstName, \
mln.value AS LastName, \
mli.value AS Login, \
mnr.value AS NewRole, \
mnud.value AS NewUserData, \
mnui.value AS NewUserID, \
mnv.value AS NewVersion, \
mo.value AS Object, \
mor.value AS OldRole, \
mov.value AS OldVersion, \
mpld.value AS PluginData, \
mplf.value AS PluginFile, \
mpod.value AS PostDate, \
mpoi.value AS PostID, \
mpos.value AS PostStatus, \
mpti.value AS PostTitle, \
mpty.value AS PostType, \
mpou.value AS PostUrl, \
mr.value AS roles, \
ms.value AS Severity, \
mtui.value AS TargetUserID, \
mtun.value AS TargetUsername, \
mthm.value AS Theme, \
mua.value AS UserAgent, \
muc.value AS UserChanger, \
mun.value AS Username, \
mus.value AS Users \
FROM wp_wsal_occurrences o \
LEFT JOIN wp_wsal_metadata ma ON ( \
ma.occurrence_id = o.id \
AND ma.name = 'Attempts' \
)\
LEFT JOIN wp_wsal_metadata mcl ON ( \
mcl.occurrence_id = o.id \
AND mcl.name = 'ClientIP' \
)\
LEFT JOIN wp_wsal_metadata mcui ON ( \
mcui.occurrence_id = o.id \
AND mcui.name = 'CurrentUserID' \
)\
LEFT JOIN wp_wsal_metadata mcur ON ( \
mcur.occurrence_id = o.id \
AND mcur.name = 'CurrentUserRoles' \
)\
LEFT JOIN wp_wsal_metadata met ON ( \
met.occurrence_id = o.id \
AND met.name = 'EventType' \
)\
LEFT JOIN wp_wsal_metadata mfn ON ( \
mfn.occurrence_id = o.id \
AND mfn.name = 'firstname' \
)\
LEFT JOIN wp_wsal_metadata mln ON ( \
mln.occurrence_id = o.id \
AND mln.name = 'lastname' \
)\
LEFT JOIN wp_wsal_metadata mli ON ( \
mli.occurrence_id = o.id \
AND mli.name = 'login' \
)\
LEFT JOIN wp_wsal_metadata mnr ON ( \
mnr.occurrence_id = o.id \
AND mnr.name = 'NewRole' \
)\
LEFT JOIN wp_wsal_metadata mnud ON ( \
mnud.occurrence_id = o.id \
AND mnud.name = 'NewUserData' \
)\
LEFT JOIN wp_wsal_metadata mnui ON ( \
mnui.occurrence_id = o.id \
AND mnui.name = 'NewUserID' \
)\
LEFT JOIN wp_wsal_metadata mnv ON ( \
mnv.occurrence_id = o.id \
AND mnv.name = 'NewVersion' \
)\
LEFT JOIN wp_wsal_metadata mo ON ( \
mo.occurrence_id = o.id \
AND mo.name = 'Object' \
)\
LEFT JOIN wp_wsal_metadata mor ON ( \
mor.occurrence_id = o.id \
AND mor.name = 'OldRole' \
)\
LEFT JOIN wp_wsal_metadata mov ON ( \
mov.occurrence_id = o.id \
AND mov.name = 'OldVersion' \
)\
LEFT JOIN wp_wsal_metadata mpld ON ( \
mpld.occurrence_id = o.id \
AND mpld.name = 'PluginData' \
)\
LEFT JOIN wp_wsal_metadata mplf ON ( \
mplf.occurrence_id = o.id \
AND mplf.name = 'PluginFile' \
)\
LEFT JOIN wp_wsal_metadata mpod ON ( \
mpod.occurrence_id = o.id \
AND mpod.name = 'PostDate' \
)\
LEFT JOIN wp_wsal_metadata mpoi ON ( \
mpoi.occurrence_id = o.id \
AND mpoi.name = 'PostID' \
)\
LEFT JOIN wp_wsal_metadata mpos ON ( \
mpos.occurrence_id = o.id \
AND mpos.name = 'PostStatus' \
)\
LEFT JOIN wp_wsal_metadata mpti ON ( \
mpti.occurrence_id = o.id \
AND mpti.name = 'PostTitle' \
)\
LEFT JOIN wp_wsal_metadata mpty ON ( \
mpty.occurrence_id = o.id \
AND mpty.name = 'PostType' \
)\
LEFT JOIN wp_wsal_metadata mpou ON ( \
mpou.occurrence_id = o.id \
AND mpou.name = 'PostUrl' \
)\
LEFT JOIN wp_wsal_metadata mr ON ( \
mr.occurrence_id = o.id \
AND mr.name = 'roles' \
)\
LEFT JOIN wp_wsal_metadata ms ON ( \
ms.occurrence_id = o.id \
AND ms.name = 'Severity' \
)\
LEFT JOIN wp_wsal_metadata mtui ON ( \
mtui.occurrence_id = o.id \
AND mtui.name = 'TargetUserID' \
)\
LEFT JOIN wp_wsal_metadata mtun ON ( \
mtun.occurrence_id = o.id \
AND mtun.name = 'TargetUsername' \
)\
LEFT JOIN wp_wsal_metadata mthm ON ( \
mthm.occurrence_id = o.id \
AND mthm.name = 'Theme' \
)\
LEFT JOIN wp_wsal_metadata mua ON ( \
mua.occurrence_id = o.id \
AND mua.name = 'UserAgent' \
)\
LEFT JOIN wp_wsal_metadata muc ON ( \
muc.occurrence_id = o.id \
AND muc.name = 'UserChanger' \
)\
LEFT JOIN wp_wsal_metadata mun ON ( \
mun.occurrence_id = o.id \
AND mun.name = 'Username' \
)\
LEFT JOIN wp_wsal_metadata mus ON ( \
mus.occurrence_id = o.id \
AND mus.name = 'Users' \
)\
WHERE o.id > ?
<Exec>
If not defined($FirstName) or $FirstName == '' delete($FirstName);
to_json();
</Exec>
</Input>{
"id": 29,
"AlertID": 4002,
"EventTime": "2022-02-02T07:22:18.923007+00:00",
"Attempts": 1,
"ClientIP": "192.168.100.28",
"CurrentUserID": "1",
"CurrentUserRoles": "a:1:{i:0;s:13:\"administrator\";}",
"EventType": "modified",
"LastName": " ",
"Login": "johndoe",
"NewRole": "Administrator",
"NewUserData": null,
"NewUserID": null,
"NewVersion": null,
"Object": "user",
"OldRole": "Subscriber",
"OldVersion": null,
"PluginData": null,
"PluginFile": null,
"PostDate": null,
"PostID": null,
"PostStatus": null,
"PostTitle": null,
"PostType": null,
"PostUrl": null,
"roles": null,
"Severity": "500",
"TargetUserID": "2",
"TargetUsername": "johndoe",
"Theme": null,
"UserAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36",
"UserChanger": null,
"Username": null,
"Users": null,
"EventReceivedTime": "2022-02-11T16:00:51.030759+00:00",
"SourceModuleName": "odbc",
"SourceModuleType": "im_odbc"
}This example configuration uses the im_odbc module to read data from the ps_log table.
<Extension json>
Module xm_json
</Extension>
<Input odbc>
Module im_odbc
# This is the connection string containing the ODBC data source name
ConnectionString DSN=presta; database=presta;
# This is the SQL statement that the module will execute to query data from the data source
SQL SELECT id_log AS id, \
severity AS SeverityLevel, \
message AS ActionTaken, \
id_lang AS Language, \
in_all_shops AS ShopContext, \
id_employee AS EmployeeID, \
date_add AS DateAdded , \
date_upd AS DateUpdated \
FROM ps_log WHERE id_log > ?
<Exec>
rename_field($id, $id_log);
to_json();
</Exec>
</Input>{
"id_log": 325,
"SeverityLevel": 1,
"ActionTaken": "Back office connection from 192.168.100.28",
"Language": 1,
"ShopContext": 1,
"EmployeeID": 1,
"DateAdded": "2022-01-05T15:35:29.000000+00:00",
"DateUpdated": "2022-01-05T15:35:29.000000+00:00",
"EventReceivedTime": "2022-02-11T16:44:25.261108+00:00",
"SourceModuleName": "odbc",
"SourceModuleType": "im_odbc"
}This example configuration uses the im_odbc module to read data from the watchdog table.
<Extension json>
Module xm_json
</Extension>
<Input odbc>
Module im_odbc
# This is the connection string containing the ODBC data source name
ConnectionString DSN=drupal; database=drupal;
# This is the SQL statement that the module will execute to query data from the data source
SQL SELECT wid AS id, \
uid AS UserID, \
type AS ErrorType, \
message AS ActionTaken, \
severity AS SeverityLevel, \
location AS Location, \
hostname AS HostName, \
timestamp AS Time \
FROM watchdog WHERE wid > ?
<Exec>
rename_field($id, $id_log);
to_json();
</Exec>
</Input>{
"wid": 109,
"UserID": 1,
"ErrorType": "user",
"ActionTaken": "Deleted user: %name %email.",
"SeverityLevel": 5,
"Location": "http://192.168.100.149/batch?_format=json&id=2&op=do",
"HostName": "192.168.100.28",
"Time": "2022-02-14T4:14:56.799020+00:00",
"EventReceivedTime": "2022-02-14T14:14:56.799020+00:00",
"SourceModuleName": "odbc",
"SourceModuleType": "im_odbc"
}