Postgresql Report from log table for Moodle or Totara LMS
From w3cyberlearnings
I work on generate report for the entire log for Moodle or Totara from Postgresql database. The log table provides cruel information such as how many course users access,...
Table log
id time userid ip course module cmid action url info
GET ALL LOGS For the Current Date
List all logs and format the time (FROM bigint To date format) according to the current date.
SELECT *,
('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval) as time
FROM mdl_log
WHERE
('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval)::date=CURRENT_DATE
COUNT THE TOTAL Logs based on the current date
The result will include the user logs as well as the system log that run by cron.
SELECT COUNT(id) as total,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date as time2
FROM mdl_log
GROUP BY time2
HAVING
('1970-01-01 00:00:00 GMT'::timestamp +((time)::text)::interval)::date=CURRENT_DATE ;
Get all logs but exclude the system process userid=0 and the cron job
SELECT *,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval) as time
FROM mdl_log
WHERE ('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
userid <> 0
AND
url !='cron'
List all the users include the system process (cron)
SELECT
COUNT(DISTINCT userid) as total,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date AS time2
FROM
mdl_log
GROUP BY
time2
HAVING
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
Get all the log that run cron
SELECT * FROM mdl_log
WHERE
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
url='cron'
Similar to the previous one, but this one only list the information
SELECT
info
FROM
mdl_log
WHERE
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
url='cron'
List only the valid email address for the error
SELECT
info
FROM
mdl_log
WHERE
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
url='cron'
AND
info LIKE '%@%';
COUNT THE TOTAL log that processed by cron job
SELECT
COUNT(userid) as total,
url,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date as mytime
FROM
mdl_log
GROUP BY
url,
mytime
HAVING
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
url='cron'
List everything accept the userid equal to 0
SELECT *
FROM
mdl_log
WHERE
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
userid !=0
Uniquely return the total of users from the log
SELECT
COUNT(DISTINCT userid) AS TOTAL_USERS
FROM
mdl_log
WHERE
('1970-01-01 00:00:00 GMT'::timestamp + ((time)::text)::interval)::date=CURRENT_DATE
AND
userid !=0
List all the valid course
SELECT
COURSE
FROM
mdl_log
WHERE
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
COURSE !=1
AND
COURSE !=0
Count course base on the course ID, and excluded the COURSE=1 and COURSE=0
SELECT
COUNT(COURSE) AS TOTAL,
COURSE,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date as mydate
FROM
mdl_log
GROUP BY
COURSE,
mydate
HAVING
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
COURSE !=1
AND
COURSE !=0
Get all the logs for user failed to login for the current date
SELECT *,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval) as TIME
FROM
mdl_log
WHERE
module='login'
AND
action='error'
AND
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
Count all the users that failed to login for the current date
SELECT
COUNT(action) as TOTAL,
module,
action,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date AS mdate
FROM
mdl_log
GROUP BY
module,
mdate,
action
HAVING
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
module='login'
AND
action='error'
Get all the current upload course
SELECT *,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval) as TIME
FROM
mdl_log
WHERE
module='upload'
AND
action='upload'
AND
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
Count all the upload course of the current date
SELECT
COUNT(action) as TOTAL,
module,
action,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date AS mdate
FROM
mdl_log
GROUP BY
module,
mdate,
action
HAVING
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
AND
module='upload'
AND
action='upload'
List all the certificate received today
SELECT *,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval) as TIME
FROM
mdl_log
WHERE
module='certificate'
AND
action='received'
AND
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
List all the quiz attempt or access
SELECT *,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval) as TIME
FROM
mdl_log
WHERE
module='quiz'
AND
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=CURRENT_DATE
List all course that have updated
SELECT *,
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval) as TIME
FROM
mdl_log
WHERE
module='course'
AND
action='update'
AND
('1970-01-01 00:00:00 GMT'::timestamp +
((time)::text)::interval)::date=DATE 'today'
