Show
Prerequisites
Postgres server or MySQL/MariaDB
User details for it
Setup
You only need to do one of POstgres or MySQL
Postgres
Create a new DB
createdb -O [username] -Eutf8 mylogs
Create a new table in it (postgres):
psql mylogs
CREATE TABLE newlogs (
id SERIAL,
eventname character varying(255) DEFAULT ''::character varying NOT NULL,
component character varying(100) DEFAULT ''::character varying NOT NULL,
action character varying(100) DEFAULT ''::character varying NOT NULL,
target character varying(100) DEFAULT ''::character varying NOT NULL,
objecttable character varying(50),
objectid bigint,
crud character varying(1) DEFAULT ''::character varying NOT NULL,
edulevel smallint NOT NULL,
contextid bigint NOT NULL,
contextlevel bigint NOT NULL,
contextinstanceid bigint NOT NULL,
userid bigint NOT NULL,
courseid bigint,
relateduserid bigint,
anonymous smallint DEFAULT 0 NOT NULL,
other text,
timecreated bigint NOT NULL,
origin character varying(10),
ip character varying(45),
realuserid bigint
);
MySQL/MariaDB
Create a new mysql database:
CREATE DATABASE mylogs DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON mylogs.* TO [username] @'localhost';
FLUSH PRIVILEGES;
Create a new table in it:
CREATE TABLE `newlogs` (
`id` bigint( 10 ) NOT NULL AUTO_INCREMENT,
`eventname` varchar( 255 ) NOT NULL DEFAULT '' ,
`component` varchar( 100 ) NOT NULL DEFAULT '' ,
`action` varchar( 100 ) NOT NULL DEFAULT '' ,
`target` varchar( 100 ) NOT NULL DEFAULT '' ,
`objecttable` varchar( 50 ) DEFAULT NULL,
`objectid` bigint( 10 ) DEFAULT NULL,
`crud` varchar( 1 ) NOT NULL DEFAULT '' ,
`edulevel` tinyint( 1 ) NOT NULL,
`contextid` bigint( 10 ) NOT NULL,
`contextlevel` bigint( 10 ) NOT NULL,
`contextinstanceid` bigint( 10 ) NOT NULL,
`userid` bigint( 10 ) NOT NULL,
`courseid` bigint( 10 ) DEFAULT NULL,
`relateduserid` bigint( 10 ) DEFAULT NULL,
`anonymous` tinyint( 1 ) NOT NULL DEFAULT 0 ,
`other` longtext DEFAULT NULL,
`timecreated` bigint( 10 ) NOT NULL,
`origin` varchar( 10 ) DEFAULT NULL,
`ip` varchar( 45 ) DEFAULT NULL,
`realuserid` bigint( 10 ) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `mdl_logsstanlog_tim_ix` (`timecreated`),
KEY `mdl_logsstanlog_couanotim_ix` (`courseid`,`anonymous`,`timecreated`),
KEY `mdl_logsstanlog_useconconcr_ix` (`userid`,`contextlevel`,`contextinstanceid`,`crud`,`edulevel`,`timecreated`),
KEY `mdl_logsstanlog_con_ix` (`contextid`)
) ENGINE=InnoDB AUTO_INCREMENT= 1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT COMMENT= 'Standard log table external db server' ;
Tests
Navigate to site admin -> plugins -> loggins -> manage log stores
Open settings for the "External database" log store
Fill with:
Driver: Postgresql or MySQL/MariaDB
Host: Your db host
User: Your DB user
Database name: mylogs
Database table: newlogs
Save changes
Enable the store by clicking on the eye on the overview page
Disable the standard log store by clicking on the eye on the overview page
Log in as a student
Browse to a course and perform some actions - look at the course, activities, etc.
Log out
As a teacher navigate to Reports -> Logs
Choose the user you have performed the actions as
"Get these logs"
Confirm that the logs are shown
Improvement
Minor
MDL-81327 Resolve log and loglive report issues when external database are used to store logs
