Moodle 4.0, trying to get a custom relational report that crosses users and course

Moodle 4.0, trying to get a custom relational report that crosses users and course

by zenit_ istud -
Number of replies: 1

Good morning everyone,

on Moodle 4.0, we are trying to get a custom report (example: for the activity of reading pdf file, Person 1, when he did activity start log and activity close log, so the total duration in which he was active on that activity is X minutes)

the information we want to see contained in the downloadable report is:

1) login date and time

2) logout date and time

3) session duration 

4) title of the course

5) name of the activity

6) name of participant 

7) email address

8) identification code


the Report Builder plugin has been installed to allow us to display custom reports by querying the database with the information we need, but apparently it does not seem possible to relate the Users database to the Courses database, so we need a relational report that crosses identifiable users and course with related activities + session duration for each activity (for each individual).

How can this be done to resolve this issue?

Sorry if this may be a topic already covered, but I have not yet found answers to our needs.

Thank you very much.

Average of ratings: -
In reply to zenit_ istud

Re: Moodle 4.0, trying to get a custom relational report that crosses users and course

by Motasem Al Haj Ali -

Hi Zenit, 


YOu can use the following approach to connect users with their courses. 

FROM prefix_course_categories AS cat 

JOIN prefix_course AS mc ON mc.category = cat.id 

JOIN prefix_context AS context ON context.instanceid = mc.id 

 JOIN prefix_role_assignments AS ra ON ra.contextid = context.id 

 JOIN prefix_user mu ON ra.userid = mu.id


But I am unsure if this way of approaching the time students spend in the course or activity will give you accurate results. 


Best wishes

Mo

Average of ratings: -