The more that you dig into this, the more you will see a need to define "every activity." For example, in my courses, I like to define "activity" as a student doing something on a quiz, an assignment, a forum, and even a
URL. But I don't want to could "activity" as clicking on the grade book, logging into Moodle, clicking on the Calendar, etc. But this is up to you.
Here is the code that I use to get (what I define) quiz activity for one course. You can explore this code and customize it. Using it literally will not produce any results for you. It must be modified.
# QUIZ
SELECT cm.course AS CourseID,
c.shortname AS Course_Shortname,
c.idnumber AS Course_IDNumber,
cm.section AS TopicID,
t.name AS TopicName,
cm.id AS ActivityID,
q.name AS ActivityName,
cm.module AS ActTypeNum,
m.name AS ActTypeName,
cm.completion AS ActCompType,
cm.visible AS ActivityVisible,
if(q.timeopen=0, 0, from_unixtime(q.timeopen, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeOpen,
if(q.timeclose=0, 0, from_unixtime(q.timeclose, "%m/%d/%Y %h:%i:%s %p")) AS ActivityTimeClosed,
cm.instance AS LogInstance,
from_unixtime(ls.timecreated, "%m/%d/%Y %h:%i:%s %p") AS LogDate,
ls.action AS LogAction,
ls.target as LogTarget,
ls.origin AS LogOrigin,
ls.userid AS StudentID,
ls.ip AS StudentIP,
u.lastname AS StudentLastName,
u.firstname AS StudentFirstName,
qa.quiz AS AttemptID,
qa.id AS StudentItem,
qa.attempt AS ItemAttemptNumber,
if(qa.timestart=0, 0, from_unixtime(qa.timestart, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemStartTime,
if(qa.timefinish=0, 0, from_unixtime(qa.timefinish, "%m/%d/%Y %h:%i:%s %p")) AS StudentItemEndTime,
qa.sumgrades AS StudentItemGrade,
gg.rawgrade AS StudentActivityGrade,
q.grade AS ActivityMaxGrade
FROM mdl_course_modules cm
JOIN mdl_modules m ON m.id = cm.module
JOIN mdl_course c ON c.id = cm.course
JOIN mdl_course_sections t ON t.id = cm.section
JOIN mdl_quiz q ON q.id = cm.instance
LEFT JOIN mdl_grade_items gi ON (gi.iteminstance = cm.instance
AND cm.course = gi.courseid)
JOIN mdl_logstore_standard_log ls ON (ls.contextinstanceid = cm.id
AND ls.courseid = cm.course)
LEFT JOIN mdl_quiz_attempts qa ON (qa.id = ls.objectid
AND qa.userid = ls.userid)
JOIN mdl_grade_grades gg ON (gg.itemid = gi.id
AND ls.userid = gg.userid)
JOIN mdl_user u ON u.id = ls.userid
WHERE cm.module = 16
AND cm.visible = 1
AND ls.component = "mod_quiz"
AND ls.action in ("submitted", "viewed", "reviewed", "started")
AND c.idnumber > 200
AND cm.course IN (93610)
