アドホック寄稿レポート
このページは現在作成中です！
ユーザとロールのレポート
カテゴリごとに登録された個別の学習者と教師の数を数えます（そのすべてのサブカテゴリを含む）
SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) as teachers
FROM prefix_course AS c #, mdl_course_categories AS cats
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS lra ON lra.contextid = ctx.id
JOIN prefix_role_assignments AS tra ON tra.contextid = ctx.id
JOIN prefix_course_categories AS cats ON c.category = cats.id
WHERE c.category = cats.id
AND (
cats.path LIKE '%/CATEGORYID/%' #Replace CATEGORYID with the category id you want to count (eg: 80)
OR cats.path LIKE '%/CATEGORYID'
)
AND lra.roleid=5
AND tra.roleid=3
各ロールの詳細なアクション（教師、編集権限のない教師、および学生）
SELECT r.name, l.action, COUNT( l.userid ) AS counter
FROM prefix_log AS l
JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid AND ra.contextid = context.id
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid IN ( 3, 4, 5 )
GROUP BY roleid, l.action
各コースの学生（ユーザ）COUNT
年（コースのフルネームに含まれている場合）による（オプションの）フィルタを含みます。
SELECT CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course.id,'">',course.fullname,'</a>') AS Course
,CONCAT('<a target="_new" href="%%WWWROOT%%/user/index.php?contextid=',context.id,'">Show users</a>') AS Users
, COUNT(course.id) AS Students
FROM prefix_role_assignments AS asg
JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50
JOIN prefix_user AS user ON user.id = asg.userid
JOIN prefix_course AS course ON context.instanceid = course.id
WHERE asg.roleid = 5
- AND course.fullname LIKE '%2013%'
GROUP BY course.id
ORDER BY COUNT(course.id) DESC
各コースの登録数
各コースのすべてのロールの登録ユーザの総数を表示します。 コース名でソート。
SELECT c.fullname, COUNT(ue.id) AS Enroled
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
GROUP BY c.id
ORDER BY c.fullname
コース登録別のすべてのサイトユーザのリスト（Moodle 2.x）
SELECT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
course.fullname AS Course
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) as Role
,(SELECT name FROM prefix_role WHERE id=en.roleid) as RoleName
FROM prefix_course as course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS user2 ON ue.userid = user2.id
一度もコースにログインしなかった登録ユーザ（Moodle2）
Moodle 2テーブル構造用に設計され、特別なプラグインフィルタを使用します：%%FILTER_SEARCHTEXT:table.field%%
SELECT
user2.id as ID,
ul.timeaccess,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
user2.idnumber AS IDNumber,
user2.phone1 AS Phone,
user2.institution AS Institution,
IF (user2.lastaccess = 0,'never',
DATE_FORMAT(FROM_UNIXTIME(user2.lastaccess),'%Y-%m-%d')) AS dLastAccess
,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id and courseid=c.id) as CourseLastAccess
,(SELECT r.name
FROM prefix_user_enrolments AS uenrol
JOIN prefix_enrol AS e ON e.id = uenrol.enrolid
JOIN prefix_role AS r ON e.id = r.id
WHERE uenrol.userid=user2.id and e.courseid = c.id) AS RoleName
FROM prefix_user_enrolments as ue
JOIN prefix_enrol as e on e.id = ue.enrolid
JOIN prefix_course as c ON c.id = e.courseid
JOIN prefix_user as user2 ON user2 .id = ue.userid
LEFT JOIN prefix_user_lastaccess as ul on ul.userid = user2.id
WHERE c.id=16 AND ul.timeaccess IS NULL
%%FILTER_SEARCHTEXT:user2.firstname%%
カテゴリのロールの割り当て
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.id,'</a>') AS id,
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.name,'</a>') AS category,
cc.depth, cc.path, r.name AS role,
concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',usr.id,'">',usr.lastname,'</a>') AS name,
usr.firstname, usr.username, usr.email
FROM prefix_course_categories cc
INNER JOIN prefix_context cx ON cc.id = cx.instanceid
AND cx.contextlevel = '40'
INNER JOIN prefix_role_assignments ra ON cx.id = ra.contextid
INNER JOIN prefix_role r ON ra.roleid = r.id
INNER JOIN prefix_user usr ON ra.userid = usr.id
ORDER BY cc.depth, cc.path, usr.lastname, usr.firstname, r.name, cc.name
パーミッションはカテゴリをオーバーライドします
(作成者: Séverin Terrier)
SELECT rc.id, ct.instanceid, ccat.name, rc.roleid, rc.capability, rc.permission,
DATE_FORMAT( FROM_UNIXTIME( rc.timemodified ) , '%Y-%m-%d' ) AS timemodified, rc.modifierid, ct.instanceid, ct.path, ct.depth
FROM `prefix_role_capabilities` AS rc
INNER JOIN `prefix_context` AS ct ON rc.contextid = ct.id
INNER JOIN `prefix_course_categories` AS ccat ON ccat.id = ct.instanceid
AND `contextlevel` =40
"完全に開かれたコース" のリスト（表示、ゲストに公開、パスワードなし）
(作成者: Séverin Terrier )
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS id,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS 'Course',
concat('<a target="_new" href="%%WWWROOT%%/enrol/instances.php?id=',c.id,'">Méthodes inscription</a>') AS 'Enrollment plugins',
e.sortorder
FROM prefix_enrol AS e, prefix_course AS c
WHERE e.enrol='guest' AND e.status=0 AND e.password= AND c.id=e.courseid AND c.visible=1
過去120日間の "ログインユーザ" のリスト
SELECT id,username,FROM_UNIXTIME(`lastlogin`) as days
FROM `prefix_user`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
および同じ母集団のユーザ数:
SELECT COUNT(id) as Users FROM `prefix_user`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
過去7日以内にログインしたユーザ
SELECT
l.* FROM mdl_logstore_standard_log l
WHERE
l.eventname = '\\core\\event\\user_loggedin'
AND FROM_UNIXTIME(l.timecreated, '%Y-%m-%d') >= DATE_SUB(NOW(), INTERVAL 7 DAY)
SELECT l.eventname FROM mdl_logstore_standard_log l
GROUP BY l.eventname
サイトに一度だけログインしたユーザを一覧に表示
SELECT id, username, firstname, lastname, idnumber
FROM prefix_user
WHERE prefix_user.deleted = 0
AND prefix_user.lastlogin = 0
AND prefix_user.lastaccess > 0
一部の機関のすべてのコースの学生
一部の機関のすべてのコースのすべての学生（roleid = 5）のステータス（削除されたかどうか）は何ですか
SELECT c.id, c.fullname, u.firstname, u.lastname, u.deleted
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND u.institution = 'please enter school name here'
完全なユーザ情報（削除されたユーザの場合）
追加のカスタムプロファイルフィールドを含む（prefix_user_info_dataから）
SELECT *
FROM prefix_user as u
JOIN prefix_user_info_data as uid ON uid.userid = u.id
JOIN prefix_user_info_field as uif ON (uid.fieldid = uif.id AND uif.shortname = 'class')
WHERE `deleted` = "1" and `institution`="your school name" and `department` = "your department" and `data` = "class level and number"
ユーザのコース
"u.id = 2" を新しいユーザIDに変更します。
SELECT u.firstname, u.lastname, c.id, c.fullname
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE u.id = 2
現在のコースの追加情報（メール）でユーザを一覧表示する
ブロック/configurable_reportsは%%COURSEID%%をコースIDに置き換えます。
SELECT u.firstname, u.lastname, u.email
FROM prefix_role_assignments AS ra
JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
JOIN prefix_user AS u ON u.id = ra.userid
現在のコースの登録日と完了日を持つ学生を一覧表示します
これは、以下を含む構成可能レポートの "グローバル" レポートであることを意味します。
名、姓、ID番号、教育機関、部門、電子メール、学生の登録日、学生の修了日
注: PGSQLの場合、FROM_UNIXTIME()の代わりにto_timestamp()を使用してください
Moodle HQのElizabeth Daltonによる寄稿
SELECT
u.firstname
, u.lastname
, u.idnumber
, u.institution
, u.department
, u.email
, FROM_UNIXTIME(cc.timeenrolled)
, FROM_UNIXTIME(cc.timecompleted)
FROM prefix_role_assignments AS ra
JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_completions AS cc ON cc.course = c.id AND cc.userid = u.id
特別なロール
SELECT ra.roleid,r.name
,concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',ra.userid,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_role_assignments AS ra
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
JOIN prefix_course AS c ON ctx.instanceid = c.id
WHERE ra.roleid > 6
教師のいないコース
実際には、コース内の教師の数を示しています。
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Teachers
FROM prefix_course AS c
ORDER BY Teachers ASC
4週間以上登録されているユーザのリスト
Moodle 2.2の場合、Isuru Madushanka Weerarathnaによる
SELECT uenr.userid As User, IF(enr.courseid=uenr.courseid ,'Y','N') As Enrolled,
IF(DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated))>=28,'Y','N') As EnrolledMoreThan4Weeks
FROM prefix_enrol As enr, prefix_user_enrolments AS uenr
WHERE enr.id = uenr.enrolid AND enr.status = uenr.status
言語を持つユーザのリスト
寄稿者：Randy Thornton
デフォルトの言語が適切に設定されていないシステムの問題は、すべてのユーザに対してローカリゼーションへの一括変更を行う必要があることです。 一般的なケース（米国、カナダ、南北アメリカ）は、デフォルトの英語を米国英語に変更することです。
これにより、すべてのユーザの言語設定が表示されます。
SELECT username, lang from prefix_user
注: UPDATEコマンドには、AdminerやPHPMyAdminなどのツールやその他のdbツールを介してデータベースを直接変更する機能が必要です。
このコードは、すべてのユーザーの設定を 'en' から 'en_us' に変更します。
UPDATE prefix_user SET lang = 'en_us' WHERE lang = 'en'
特定の国が設定されているユーザに対してのみこれを行うには、例としてこれを使用します。
UPDATE prefix_user SET lang = 'en_us' WHERE country = 'US' AND lang = 'en'
認証を持つユーザのリスト
寄稿者: Randy Thornton
認証方法を大幅に変更する必要がある場合があります。 一般的なケースは、デフォルトのマニュアルをLDAPに変更することです。
これにより、すべてのユーザーの認証設定が表示されます。
SELECT username, auth from prefix_user
注: UPDATEコマンドには、AdminerやPHPMyAdminなどのツールやその他のdbツールを介してデータベースを直接変更する機能が必要です。
このコードは、GuestとAdminの最初の2つのアカウントを除くすべてのユーザの設定を 'manual' から 'ldap' に変更します。 （警告: 管理者アカウントを手動から外部メソッドに変更することは悪い習慣です。その外部メソッドに失敗すると、管理者としてのMoodleからロックアウトされます。）
UPDATE prefix_user SET auth = 'ldap' WHERE auth = 'manual' AND id > 2
ロールのパーミッションとケイパビリティを比較する
互換性: MySQLとPostgreSQL
SELECT DISTINCT mrc.capability
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '1' AND rc.contextid = '1') AS Manager
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '2' AND rc.contextid = '1') AS Course_Creator
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '3' AND rc.contextid = '1') AS Teacher
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '4' AND rc.contextid = '1') AS Assistant_Teacher
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '5' AND rc.contextid = '1') AS Student
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '6' AND rc.contextid = '1') AS Guest
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '7' AND rc.contextid = '1') AS Authenticated
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '8' AND rc.contextid = '1') AS Auth_front
FROM prefix_role_capabilities AS mrc
コースで費やされたユーザの累積時間
2時間のセッション制限を考慮した、logstore_standard_logユーザのレコード間の時間デルタの合計。
用途: 現在のユーザーのID %%USERID%% および現在のコースのID %%COURSEID%%
また、日付フィルタを使用します（無視できます）
追加の "ユーザ" フィールドは、折れ線グラフのシリーズフィールドのダミーフィールドとして使用されます。ここでは、X=id、Series=Type、Y=deltaを使用します。
SELECT
l.id,
l.timecreated,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%Y') AS dTime,
@prevtime := (SELECT max(timecreated) FROM mdl_logstore_standard_log
WHERE userid = %%USERID%% and id < l.id ORDER BY id ASC LIMIT 1) AS prev_time,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS sumtime,
l.timecreated-@prevtime AS delta,
"User" as type
FROM prefix_logstore_standard_log as l,
(SELECT @delta := 0) AS s_init
- Change UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
参加率の低い学生レポート
Granite State College / MoodleHQのElizabethDaltonによる寄稿
このレポートは、指定されたカテゴリの短い名前のテキストマーカ（この場合は "OL-"）でフィルタリングされたコースに登録されているが、指定された期間中のコースへの参加が非常に少ない学生のリストを返します。（活動モジュールへの "編集" が2つ未満であり、コースへのアクティブな貢献がほとんどないことを示します）"編集" の数は、指定された期間の各学生に提供されます。
"編集" は、コンテンツの表示以外のコース活動として定義されます。 "ログ" リンクをクリックして、学生の活動を確認します。 ログには、"表示" 活動と "編集" 活動を確認するオプションがあります。
このレポートには "目に見える" コースのみが含まれています。 レポートはExcelスプレッドシートとしてダウンロードできます。
構成可能なレポートのフィルタタブで、フィルタを設定することを忘れないでください: "開始日/終了日フィルタ" および "フィルターカテゴリ"。
SELECT u.lastname AS Last, u.firstname AS First, u.idnumber AS IDnumber, u.email AS email, c.shortname AS CourseID, count(l.id) AS Edits, CONCAT('<a target="_new" href="https://learn.granite.edu/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=-view&logformat=showashtml','">','Logs','</a>') AS Link
FROM prefix_user AS u
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action NOT LIKE "view%" %%FILTER_STARTTIME:l.TIME:>%% %%FILTER_ENDTIME:l.TIME:<%%
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND c.visible=1
- This prefix filter allows the exclusion of non-online courses at the original institution. Alter this to fit your institution, or remove it.
AND c.shortname LIKE '%OL-%'
%%FILTER_CATEGORIES:c.category%%
GROUP BY u.idnumber
HAVING Edits < 2
特定のコースのすべてのユーザのメッセージ
寄稿者: Randy Thornton
注: メッセージデータベースの構造が変更されたため、このクエリは3.5ではおそらくまったく機能しません。 -RT
このクエリは、コースID番号を指定して、特定のコースのユーザ間の個人的なメッセージを表示します。 正しく言えば、個人的なメッセージはユーザにのみ関係し、コースの一部ではありませんが、コース内のロールの登録をフィルタリングすることで、これを示すことができます。
このレポートは、WHEREステートメントにAND ((...))) セクションが含まれているため、教師と学生の間のメッセージのみが表示され、このレポートは教師（ロールID = 3）と学生（ロールID = 5）。 すべてのユーザー間で_all_メッセージを表示する場合は、ステートメントのその部分を削除します。 教師から教師へ、学生から学生へ。
また、カスタムロールを作成した場合は、デフォルトのID番号をカスタムのID番号に置き換えて、レポートをさらに強化できます。
SELECT
u.username AS 'From',
CONCAT(u.firstname ,' ',u.lastname) AS 'From Name',
u2.username AS 'To',
CONCAT(u2.firstname ,' ',u2.lastname) AS 'To Name',
DATE_FORMAT(FROM_UNIXTIME(me.timecreated), '%Y-%m-%d %H:%i') AS 'When',
me.subject AS 'Subject',
me.smallmessage AS 'Message'
FROM prefix_message me
JOIN prefix_role_assignments AS ra ON ra.userid = me.useridfrom AND ra.roleid IN (3,4,5)
JOIN prefix_role_assignments AS ra2 ON ra2.userid = me.useridto AND ra2.roleid IN (3,4,5)
JOIN prefix_context AS ctx ON ra.contextid = ctx.id AND ra2.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_user u ON u.id = me.useridfrom
JOIN prefix_user u2 ON u2.id = me.useridto
WHERE c.id=##
AND ((ra.roleid = 3 AND ra2.roleid = 5) OR (ra.roleid = 5 AND ra2.roleid = 3))
ORDER BY me.useridfrom, me.useridto, me.timecreated
活動レポートログ
コースカテゴリ（そのすべてのサブカテゴリを含む）のROLEですべてのアクティブユーザをカウントします
SELECT COUNT(DISTINCT l.userid) as active
FROM mdl_course as c
JOIN mdl_context AS ctx ON ctx.instanceid=c.id
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user_lastaccess as l ON ra.userid = l.userid
JOIN mdl_course_categories AS cats ON c.category = cats.id
WHERE c.category=cats.id AND (
cats.path LIKE '%/80/%'
OR cats.path LIKE '%/80'
)
AND ra.roleid=3 AND ctx.contextlevel=50 #ra.roleid= TEACHER 3, NON-EDITING TEACHER 4, STUDENT 5
AND l.timeaccess > (unix_timestamp() - ((60*60*24)*NO_OF_DAYS)) #NO_OF_DAYS change to number
各ロールの詳細な "表示" アクション（教師、編集権限のない教師、および学生）
SELECT l.action, count( l.userid ) as counter , r.name
FROM `prefix_log` as l
JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid
JOIN `prefix_role` AS r ON ra.roleid = r.id
WHERE (ra.roleid IN (3,4,5)) AND (l.action LIKE '%view%' )
GROUP BY roleid,l.action
order by r.name,counter desc
ロールの合計活動: 日付別および時間別の "教師" および "編集権限のない教師"
このレポートテーブルの出力列は、ピボットテーブルのベースとして使用できます。
これは、3Dグラフビューで 日 あたり 時間 あたりの アクティビティ の量を表示します。
SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y-%m-%d' ) AS grptimed ,
DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%k' ) AS grptimeh , count( l.userid ) AS counter
FROM `prefix_log` AS l
JOIN prefix_user AS u ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON r.id = ra.roleid
WHERE ra.roleid IN (3,4)
GROUP BY grptimed,grptimeh
ORDER BY grptimed,grptimeh
ユーザおよびユーザの活動ごとのログイン数
+ユーザ名をユーザ活動グラフレポートにリンクする
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') as Username
,count(*) as logins
,(SELECT count(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) as Activity
FROM prefix_log as l JOIN prefix_user as u ON l.userid = u.id
WHERE `action` LIKE '%login%' group by userid
ORDER BY Activity DESC
1か月あたりの個別のユーザログイン
寄稿者: Randy Thornton
以下に、現在の暦年の月と、1か月あたりの個別の一意のユーザログインの総数を示します。 WHERE句の年を必要な年に変更します。
SELECT
COUNT(DISTINCT l.userid) AS 'DistinctUserLogins',
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS 'Month'
FROM prefix_logstore_standard_log l
WHERE l.action = 'loggedin' AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2017'
GROUP BY MONTH(FROM_UNIXTIME(l.timecreated))
過去24時間のユニークユーザごとコースごとの合計活動
SELECT
COUNT(DISTINCT userid) AS countUsers
, COUNT(l.courseid) AS countVisits
, CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">', c.fullname, '</a>') AS Course
FROM mdl_logstore_standard_log AS l
JOIN mdl_course AS c ON c.id = l.courseid
WHERE l.courseid > 0
AND FROM_UNIXTIME(l.timecreated) >= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND c.fullname LIKE '%תשעו%'
GROUP BY l.courseid
ORDER BY countVisits DESC
毎週のインストラクタのオンライン参加
Granite State CollegeのElizabeth Daltonによる寄稿
学期前および学期後の編集を含む、学期の1週間あたりのすべてのコースへのインストラクタの参加を表示します。編集とは、ディスカッションポスト、課題の評定、添付ファイルのアップロードなどのコースの変更、およびコースの内容の変更として定義されます。
- 科目やコース番号を指定するには、ワイルドカードとして%を使用します。例: ARTS% または ARTS501%
- 姓の一部と一致させるには、%を使用します。例: Smi%は、"Smith"、"Smile" などと一致します。
私たちの教育機関では、コース名またはカテゴリにフィルタを含めて、用語で制約します。これらは、私たちの教育機関でコース名とカテゴリがどのように構築されているかに非常に固有であるため、このコードからこれらの要素を削除しました。また、私たちの任期は12週間です。長期の場合は追加の "SUM" 行を挿入するか、短期の場合は行を削除する必要があります。
注: このレポートの実行には長い時間がかかる場合があります。オンデマンドで構成可能なレポートで実行できますが、スケジュールされたレポートとしてアドホッククエリプラグインに実装する方が適切な場合があります。
注: このバージョンはレガシー（2.7より前）のログを使用します。 2.7以降の標準ログのバージョンについては、以下を参照してください。
SELECT
c.shortname AS CourseID
, cc.name AS Category
, CONCAT(u.firstname ,' ',u.lastname) AS Instructor
, (SELECT COUNT( ra2.userid ) AS Users2 FROM prefix_role_assignments AS ra2
JOIN prefix_context AS ctx2 ON ra2.contextid = ctx2.id
WHERE ra2.roleid = 5 AND ctx2.instanceid = c.id) AS Students
, c.startdate AS Course_Start_Date
, c.visible AS Visible
, COUNT(l.id) AS Edits
, SUM(IF(WEEK(FROM_UNIXTIME(l.time)) - WEEK(FROM_UNIXTIME(c.startdate))<0,1,0)) AS BeforeTerm
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=0,1,0)) AS Week1
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=1,1,0)) AS Week2
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=2,1,0)) AS Week3
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=3,1,0)) AS Week4
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=4,1,0)) AS Week5
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=5,1,0)) AS Week6
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=6,1,0)) AS Week7
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=7,1,0)) AS Week8
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=8,1,0)) AS Week9
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=9,1,0)) AS Week10
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=10,1,0)) AS Week11
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=11,1,0)) AS Week12
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))>=12,1,0)) AS AfterTerm
, CONCAT('<a target="_new" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS Link
FROM prefix_user AS u
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories as cc ON c.category = cc.id
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action NOT LIKE "view%"
WHERE ra.roleid =3
AND ctx.instanceid = c.id
AND c.shortname LIKE :course
AND u.lastname LIKE :last_name
GROUP BY u.idnumber, c.id
HAVING students > 0
ORDER BY c.shortname
注: 2.7以降のログバージョン:
SELECT
c.shortname AS CourseID
, cc.name AS Category
, CONCAT(u.firstname ,' ',u.lastname) AS Instructor
, (SELECT COUNT( ra2.userid ) AS Users2 FROM prefix_role_assignments AS ra2
JOIN prefix_context AS ctx2 ON ra2.contextid = ctx2.id
WHERE ra2.roleid = 5 AND ctx2.instanceid = c.id) AS Students
, FROM_UNIXTIME(c.startdate) AS Course_Start_Date
, c.visible AS Visible
, COUNT(DISTINCT l.id) AS Edits
, COUNT(DISTINCT IF((l.timecreated-c.startdate)<0,l.id,NULL)) AS 'Before Term'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=0,l.id,NULL)) AS 'Week 1'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=1,l.id,NULL)) AS 'Week 2'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=2,l.id,NULL)) AS 'Week 3'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=3,l.id,NULL)) AS 'Week 4'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=4,l.id,NULL)) AS 'Week 5'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=5,l.id,NULL)) AS 'Week 6'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=6,l.id,NULL)) AS 'Week 7'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=7,l.id,NULL)) AS 'Week 8'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=8,l.id,NULL)) AS 'Week 9'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=9,l.id,NULL)) AS 'Week 10'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=10,l.id,NULL)) AS 'Week 11'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=11,l.id,NULL)) AS 'Week 12'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))>=12,l.id,NULL)) AS 'After Term'
, CONCAT('<a target="_new" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS Link
FROM prefix_user AS u
LEFT JOIN prefix_role_assignments AS ra ON u.id = ra.userid
LEFT JOIN prefix_context AS ctx ON ra.contextid = ctx.id
LEFT JOIN prefix_course AS c ON c.id = ctx.instanceid
LEFT JOIN prefix_course_categories as cc ON c.category = cc.id
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id AND l.crud IN ('c','u')
WHERE ra.roleid =3
AND ctx.instanceid = c.id
AND c.shortname LIKE '%OL-%'
AND cc.idnumber LIKE '%current%'
GROUP BY u.idnumber, c.id
- HAVING students > 0
ORDER BY RIGHT(c.shortname,2), c.shortname
毎週の学生のオンライン参加
Granite State CollegeのElizabeth Daltonによる寄稿
学期前および学期後の編集を含む、現在のコースへの学生の参加を週ごとに表示します。編集とは、ディスカッションポスト、課題の提出、小テストの完了などのコースの変更、およびデータベースエントリ（許可されている場合）などのコースコンテンツの変更として定義されます。
他の3つのレポートへのリンクも提供されています。
- ログ: コース内の学生の完全なログエントリ。日付順に整理されています
- 活動の概要: ユーザ活動レポートの "概要レポート"。コース内の学生の活動を要約し、コースの内容ごとに整理します。
- 統合活動レポート: ユーザ活動レポートの "完全なレポート"。コースのコンテンツごとに整理された、コース内の学生の活動の詳細（フォーラム投稿のテキストを含む）
注: これは "グローバル" レポートとして定義する必要があります（すべてのコース内から表示されます）。私たちの教育機関では、任期は12週間です。長期の場合は追加の "SUM" 行を挿入するか、短期の場合は行を削除する必要があります。構成の一部として、アドバイザ名を学生のユーザプロファイルに取り込みます。これらの行は以下のコードに存在しますが、Moodle設定に非常に固有であるためコメントアウトされています。
注: このバージョンのレポートは、レガシー（2.7より前）のログを使用します。 2.7以降の標準ログバージョンについては、以下を参照してください。
SELECT
u.lastname AS 'Last Name'
, u.firstname AS 'First Name'
, COUNT(l.id) AS 'Edits'
, SUM(IF((l.time-c.startdate)/7<0,1,0)) AS 'Before Term'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=0,1,0)) AS 'Week 1'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=1,1,0)) AS 'Week 2'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=2,1,0)) AS 'Week 3'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=3,1,0)) AS 'Week 4'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=4,1,0)) AS 'Week 5'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=5,1,0)) AS 'Week 6'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=6,1,0)) AS 'Week 7'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=7,1,0)) AS 'Week 8'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=8,1,0)) AS 'Week 9'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=9,1,0)) AS 'Week 10'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=10,1,0)) AS 'Week 11'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=11,1,0)) AS 'Week 12'
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))>=15,1,0)) AS 'After Term'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS 'Logs'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=outline">','Outline','</a>') AS 'Activity Outline'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=complete">','Activity','</a>') AS 'Consolidated Activity'
FROM prefix_user AS u
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories as cc ON c.category = cc.id
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action NOT LIKE "view%"
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND c.id = %%COURSEID%%
GROUP BY u.idnumber
ORDER BY u.lastname, u.firstname
注: 2.7以降（標準ログ）バージョン
SELECT
u.lastname AS 'Last Name'
, u.firstname AS 'First Name'
, COUNT(l.id) AS 'Edits'
, COUNT(DISTINCT IF((l.timecreated-c.startdate)<0,l.id,NULL)) AS 'Before Term'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=0,l.id,NULL)) AS 'Week 1'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=1,l.id,NULL)) AS 'Week 2'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=2,l.id,NULL)) AS 'Week 3'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=3,l.id,NULL)) AS 'Week 4'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=4,l.id,NULL)) AS 'Week 5'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=5,l.id,NULL)) AS 'Week 6'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=6,l.id,NULL)) AS 'Week 7'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=7,l.id,NULL)) AS 'Week 8'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=8,l.id,NULL)) AS 'Week 9'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=9,l.id,NULL)) AS 'Week 10'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=10,l.id,NULL)) AS 'Week 11'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=11,l.id,NULL)) AS 'Week 12'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))>=12,l.id,NULL)) AS 'After Term'
- Our institution stores academic advisor names and emails in custom profile fields
- , CONCAT('<a href="mailto:',uce.data,'">',uid.data, '</a>') AS 'Academic Advisor'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS 'Logs'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=outline">','Outline','</a>') AS 'Activity Outline'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=complete">','Activity','</a>') AS 'Consolidated Activity'
, CONCAT('<a target="_blank" href="%%WWWROOT%%/mod/forum/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'">','Posts','</a>') AS 'Posts'
FROM prefix_user AS u
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories as cc ON c.category = cc.id
- student academic coach - you can include custom profile field data with these methods
- LEFT JOIN prefix_user_info_data as uid ON u.id = uid.userid AND uid.fieldid = '2'
- student academic coach email
- LEFT JOIN prefix_user_info_data as uce on u.id = uce.userid AND uce.fieldid = '6'
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id AND l.crud IN ('c','u')
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND c.id = %%COURSEID%%
GROUP BY u.idnumber
ORDER BY u.lastname, u.firstname
私の毎週のオンライン参加
Granite State CollegeのElizabeth Daltonによる寄稿
学期前および学期後の提出/編集を含む、週ごとの "現在のコース" への "現在のユーザ" の参加を表示します。 提出/編集は、ディスカッションポスト、課題の提出、小テストの完了などのコースへの変更、およびデータベースエントリや新しいコース活動やリソースなどのコースコンテンツの変更として定義されます（ 許可されている場合）。
このレポートは標準ログ（2.7以降）を使用します。
SELECT
l.component AS 'activity'
, COUNT(DISTINCT IF((l.timecreated-c.startdate)<0,l.id,NULL)) AS 'Before Term'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=0,l.id,NULL)) AS 'Week 1'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=1,l.id,NULL)) AS 'Week 2'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=2,l.id,NULL)) AS 'Week 3'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=3,l.id,NULL)) AS 'Week 4'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=4,l.id,NULL)) AS 'Week 5'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=5,l.id,NULL)) AS 'Week 6'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=6,l.id,NULL)) AS 'Week 7'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=7,l.id,NULL)) AS 'Week 8'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=8,l.id,NULL)) AS 'Week 9'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=9,l.id,NULL)) AS 'Week 10'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=10,l.id,NULL)) AS 'Week 11'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=11,l.id,NULL)) AS 'Week 12'
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))>=12,l.id,NULL)) AS 'After Term'
, COUNT(l.id) AS 'Total'
FROM prefix_user AS u
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories as cc ON c.category = cc.id
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id AND l.crud IN ('c','u')
WHERE 1
AND ctx.instanceid = c.id
AND c.id = %%COURSEID%%
AND u.id = %%USERID%%
GROUP BY l.component
ORDER BY l.component
教員/学生の相互作用
Granite State CollegeのElizabeth Daltonによる寄稿
指定された期間の学生の活動に対するインストラクタおよび他の学生の回答の数を返します。 このレポートは、学生のコメントが返信されているかどうかを示したり、指定された時間内の学生による投稿活動を要約したりするのに役立ちます。
注: このバージョンのレポートは、レガシー（2.7より前）のログを使用します。 2.7以降の標準ログバージョンについては、以下を参照してください。
注: これは "グローバル" レポートとして定義する必要があります（すべてのコース内から表示されます）。
注: このレポートの実行には長い時間がかかる場合があります。
SELECT
- Identify student
CONCAT('<a target="_blank" href="%%WWWROOT%%/message/index.php?id=' , allstu.id , '">' , allstu.firstname , ' ' , allstu.lastname , '</a>' ) AS 'Student - click to send message'
, IF((COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fps.id,NULL) )>0) OR (COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asb.id,NULL))>0) OR (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))),'YES','NO') AS 'Student Participated This week'
, IF((COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpi.id,NULL) )>0) OR (COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asg.id,NULL))>0) OR (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id AND mts.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))),'YES','NO') AS 'Student Contacted This week'
- Only posts within last 7 days
- Count posts by student
, COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fps.id,NULL)) AS 'Forum Stu Posts - 7 days'
- Count replies to student posts by instructors
, COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Instr Replies - 7 days'
- using link back to student posts on replies, get unique student IDs responded
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpsr.id,NULL)) - COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Stu Replies - 7 days'
- all replies
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpsr.id,NULL)) AS 'Forum All Replies - 7 days'
- add in count of graded assignments - 7 days
, COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asb.id,NULL)) AS 'Assign Submit - 7 days'
, COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asg.id,NULL)) AS 'Assign Grades - 7 days'
- Messages between students and instructors - 7 days
, (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))) AS 'Msg Stu to Instr - 7 days'
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id AND mts.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))) AS 'Msg Instr to Stu - 7 days'
- All posts in course so far
- Count posts by student
, COUNT(DISTINCT fps.id) AS 'Forum Stu Posts - to date'
- Count replies to student posts by instructors
, COUNT(DISTINCT fpi.id) AS 'Forum Instr Replies - to date'
- using link back to student posts on replies, get unique student IDs responded
, COUNT(DISTINCT fpsr.id) - COUNT(DISTINCT fpi.id) AS 'Forum Stu Replies - to date'
- all replies
, COUNT(DISTINCT fpsr.id) AS 'Forum All Replies - to date'
- add in count of graded assignments - whole course
, COUNT(DISTINCT asb.id) AS 'Assign Submit - to date'
, COUNT(DISTINCT asg.id) AS 'Assign Grades - to date'
- Messages between students and instructors - to date
, (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id ) AS 'Msg Stu to Instr - to date'
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id) AS 'Msg Instr to Stu - to date'
- JOINS
- Start by getting all the students in the course
FROM prefix_user AS allstu
JOIN prefix_role_assignments AS ras ON allstu.id = ras.userid AND ras.roleid = 5
JOIN prefix_context AS ctx ON ras.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories as cc ON c.category = cc.id
- Now we get the forums and forum discussions from this course only
LEFT JOIN prefix_forum AS frm ON frm.course = c.id AND c.id = %%COURSEID%%
LEFT JOIN prefix_forum_discussions AS fd ON fd.course = %%COURSEID%% AND fd.forum = frm.id
- These are forum discussion posts just by students within specified time
LEFT JOIN prefix_forum_posts AS fps ON fps.userid = allstu.id AND fps.discussion = fd.id
- Separately, we connect the instructors of the courses
- We can use the context we have already gotten for the students
LEFT JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
LEFT JOIN prefix_user AS instr ON instr.id = rai.userid AND rai.roleid =3
- Now we will connect to posts by instructors that are replies to student posts
- This is a left join, because we don't want to eliminate any students from the list
LEFT JOIN prefix_forum_posts AS fpi ON fpi.discussion = fd.id AND fpi.userid = instr.id AND fpi.parent = fps.id
- To get identities of only those students who were replied to:
- Connect from instr replies back up to parent posts by students again
- This has to be a LEFT JOIN, we know these posts exist but don't eliminate non-responded students
LEFT JOIN prefix_forum_posts AS fpir ON fpir.id = fpi.parent
- We also want to know if students are replying to one another
- These are posts that are replies to student posts
- Again, a left join
LEFT JOIN prefix_forum_posts AS fpsr ON fpsr.discussion = fd.id AND fpsr.parent = fps.id
- get the activity modules
LEFT JOIN prefix_course_modules AS cm ON c.id = cm.course
- get the assignments
LEFT JOIN prefix_assign AS a ON cm.instance = a.id
LEFT JOIN prefix_assign_submission AS asb ON a.id = asb.assignment AND asb.userid=allstu.id
LEFT JOIN prefix_assign_grades AS asg ON asg.assignment = a.id AND asg.userid = allstu.id AND asg.assignment = asb.assignment
- We care about messages that involve both the instructor and students of this course
- messages from instructor to students:
- LEFT JOIN prefix_message AS mts ON mts.useridfrom = instr.id AND mts.useridto = allstu.id
- LEFT JOIN prefix_message AS mfs ON mfs.useridfrom = instr.id AND mfs.useridto = allstu.id
WHERE
c.id = %%COURSEID%%
- GROUP BY c.shortname , allstu.id
GROUP BY allstu.id
ORDER BY allstu.lastname
注: 2.7以降の標準ログバージョン
SELECT
- Identify student
CONCAT('<a target="_blank" href="%%WWWROOT%%/message/index.php?id=' , allstu.id , '">' , allstu.firstname , ' ' , allstu.lastname , '</a>' ) AS 'Student - click to send message'
, IF((COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fps.id,NULL) )>0) OR (COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asb.id,NULL))>0) OR (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))),'YES','NO') AS 'Student Participated This week'
, IF((COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpi.id,NULL) )>0) OR (COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asg.id,NULL))>0) OR (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id AND mts.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))),'YES','NO') AS 'Student Contacted This week'
- Only posts within last 7 days
- Count posts by student
, COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fps.id,NULL)) AS 'Forum Stu Posts - 7 days'
- Count replies to student posts by instructors
, COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Instr Replies - 7 days'
- using link back to student posts on replies, get unique student IDs responded
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpsr.id,NULL)) - COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Stu Replies - 7 days'
- all replies
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpsr.id,NULL)) AS 'Forum All Replies - 7 days'
- add in count of graded assignments - 7 days
, COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asb.id,NULL)) AS 'Assign Submit - 7 days'
, COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asg.id,NULL)) AS 'Assign Grades - 7 days'
- Messages between students and instructors - 7 days
, (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))) AS 'Msg Stu to Instr - 7 days'
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id AND mts.timecreated > (UNIX_TIMESTAMP() - (7*24*60*60))) AS 'Msg Instr to Stu - 7 days'
- All posts in course so far
- Count posts by student
, COUNT(DISTINCT fps.id) AS 'Forum Stu Posts - to date'
- Count replies to student posts by instructors
, COUNT(DISTINCT fpi.id) AS 'Forum Instr Replies - to date'
- using link back to student posts on replies, get unique student IDs responded
, COUNT(DISTINCT fpsr.id) - COUNT(DISTINCT fpi.id) AS 'Forum Stu Replies - to date'
- all replies
, COUNT(DISTINCT fpsr.id) AS 'Forum All Replies - to date'
- add in count of graded assignments - whole course
, COUNT(DISTINCT asb.id) AS 'Assign Submit - to date'
, COUNT(DISTINCT asg.id) AS 'Assign Grades - to date'
- Messages between students and instructors - to date
, (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id ) AS 'Msg Stu to Instr - to date'
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id) AS 'Msg Instr to Stu - to date'
- JOINS
- Start by getting all the students in the course
FROM prefix_user AS allstu
JOIN prefix_role_assignments AS ras ON allstu.id = ras.userid AND ras.roleid = 5
JOIN prefix_context AS ctx ON ras.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories as cc ON c.category = cc.id
- Now we get the forums and forum discussions from this course only
JOIN prefix_forum AS frm ON frm.course = c.id AND c.id = %%COURSEID%%
JOIN prefix_forum_discussions AS fd ON fd.course = %%COURSEID%% AND fd.forum = frm.id
- These are forum discussion posts just by students within specified time
LEFT JOIN prefix_forum_posts AS fps ON fps.userid = allstu.id AND fps.discussion = fd.id
- Separately, we connect the instructors of the courses
- We can use the context we have already gotten for the students
JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
JOIN prefix_user AS instr ON instr.id = rai.userid AND rai.roleid =3
- Now we will connect to posts by instructors that are replies to student posts
- This is a left join, because we don't want to eliminate any students from the list
LEFT JOIN prefix_forum_posts AS fpi ON fpi.discussion = fd.id AND fpi.userid = instr.id AND fpi.parent = fps.id
- To get identities of only those students who were replied to:
- Connect from instr replies back up to parent posts by students again
- This has to be a LEFT JOIN, we know these posts exist but don't eliminate non-responded students
LEFT JOIN prefix_forum_posts AS fpir ON fpir.id = fpi.parent
- We also want to know if students are replying to one another
- These are posts that are replies to student posts
- Again, a left join
LEFT JOIN prefix_forum_posts AS fpsr ON fpsr.discussion = fd.id AND fpsr.parent = fps.id
- get the activity modules
JOIN prefix_course_modules AS cm ON c.id = cm.course
- get the assignments
JOIN prefix_assign AS a ON cm.instance = a.id
LEFT JOIN prefix_assign_submission AS asb ON a.id = asb.assignment AND asb.userid=allstu.id
LEFT JOIN prefix_assign_grades AS asg ON asg.assignment = a.id AND asg.userid = allstu.id AND asg.assignment = asb.assignment
WHERE
c.id = %%COURSEID%%
- GROUP BY c.shortname , allstu.id
GROUP BY allstu.id
ORDER BY allstu.lastname
学生のリソース使用量
Granite State CollegeのElizabeth Daltonによる寄稿
現在のコースのすべての活動とリソースの学生による使用状況を活動ごとに表示します。 コースに表示される活動とセクションのみが含まれます。 このバージョンには、Moodle2.7以降の新しい "標準ログ" が必要です。
注: これは "グローバル" レポートとして定義する必要があります（すべてのコース内から表示されます）。
SELECT
cs.section AS 'Week'
, cs.name AS 'Section Name'
, m.name AS 'item type'
, CONCAT(
COALESCE(a.name, ),
COALESCE(b.name,),
COALESCE(cert.name,),
COALESCE(chat.name,),
COALESCE(choice.name,),
COALESCE(data.name,),
COALESCE(feedback.name,),
COALESCE(folder.name,),
COALESCE(forum.name,),
COALESCE(glossary.name,),
COALESCE(imscp.name,),
COALESCE(lesson.name,),
COALESCE(p.name,),
COALESCE(questionnaire.name,),
COALESCE(quiz.name,),
COALESCE(cr.name,),
COALESCE(scorm.name,),
COALESCE(survey.name,),
COALESCE(url.name,),
COALESCE(wiki.name,),
COALESCE(workshop.name,),
COALESCE(kalvidassign.name,),
COALESCE(attendance.name,),
COALESCE(checklist.name,),
COALESCE(flashcard.name,),
COALESCE(lti.name,),
COALESCE(oublog.name,),
COALESCE(ouwiki.name,),
COALESCE(subpage.name,),
COALESCE(journal.name,),
COALESCE(lightboxgallery.name,),
COALESCE(elluminate.name,),
COALESCE(adaptivequiz.name,),
COALESCE(hotpot.name,),
COALESCE(wiziq.name,),
COALESCE(turnitintooltwo.name,),
COALESCE(kvr.name,)
) AS 'item name'
, SUM(IF(l.crud IN ('r'),1,0)) AS 'total views'
, SUM(IF(l.crud IN ('c','u'),1,0)) AS 'total submissions'
, COUNT(DISTINCT IF(l.crud IN ('r'),u.id,NULL)) AS 'count of students who viewed'
, COUNT(DISTINCT IF(l.crud IN ('c','u'),u.id,NULL)) AS 'count of students who submitted'
FROM prefix_user AS u
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories as cc ON c.category = cc.id
JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.section <= 14 #AND cs.section > 0
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
JOIN prefix_modules AS m ON m.id = cm.module AND m.name NOT LIKE 'label'
LEFT JOIN prefix_assign AS a ON a.id = cm.instance AND m.name = 'assign'
LEFT JOIN prefix_book AS b ON b.id = cm.instance AND m.name = 'book'
LEFT JOIN prefix_certificate AS cert ON cert.id = cm.instance AND m.name = 'certificate'
LEFT JOIN prefix_chat AS chat ON chat.id = cm.instance AND m.name = 'chat'
LEFT JOIN prefix_choice AS choice ON choice.id = cm.instance AND m.name = 'choice'
LEFT JOIN prefix_data AS data ON data.id = cm.instance AND m.name = 'data'
LEFT JOIN prefix_feedback AS feedback ON feedback.id = cm.instance AND m.name = 'feedback'
LEFT JOIN prefix_folder AS folder ON folder.id = cm.instance AND m.name = 'folder'
LEFT JOIN prefix_forum AS forum ON forum.id = cm.instance AND m.name = 'forum'
LEFT JOIN prefix_glossary AS glossary ON glossary.id = cm.instance AND m.name = 'glossary'
LEFT JOIN prefix_imscp AS imscp ON imscp.id = cm.instance AND m.name = 'imscp'
LEFT JOIN prefix_lesson AS lesson ON lesson.id = cm.instance AND m.name = 'lesson'
LEFT JOIN prefix_page AS p ON p.id = cm.instance AND m.name = 'page'
LEFT JOIN prefix_questionnaire AS questionnaire ON questionnaire.id = cm.instance AND m.name = 'questionnaire'
LEFT JOIN prefix_quiz AS quiz ON quiz.id = cm.instance AND m.name = 'quiz'
LEFT JOIN prefix_resource AS cr ON cr.id = cm.instance AND m.name = 'resource'
LEFT JOIN prefix_scorm AS scorm ON scorm.id = cm.instance AND m.name = 'scorm'
LEFT JOIN prefix_survey AS survey ON survey.id = cm.instance AND m.name = 'survey'
LEFT JOIN prefix_url AS url ON url.id = cm.instance AND m.name = 'url'
LEFT JOIN prefix_wiki AS wiki ON wiki.id = cm.instance AND m.name = 'wiki'
LEFT JOIN prefix_workshop AS workshop ON workshop.id = cm.instance AND m.name = 'workshop'
LEFT JOIN prefix_kalvidassign AS kalvidassign ON kalvidassign.id = cm.instance AND m.name = 'kalvidassign'
LEFT JOIN prefix_kalvidres AS kvr ON kvr.id = cm.instance AND m.name = 'kalvidres'
LEFT JOIN prefix_attendance AS attendance ON attendance.id = cm.instance AND m.name = 'attendance'
LEFT JOIN prefix_checklist AS checklist ON checklist.id = cm.instance AND m.name = 'checklist'
LEFT JOIN prefix_flashcard AS flashcard ON flashcard.id = cm.instance AND m.name = 'flashcard'
LEFT JOIN prefix_lti AS lti ON lti.id = cm.instance AND m.name = 'lti'
LEFT JOIN prefix_oublog AS oublog ON oublog.id = cm.instance AND m.name = 'oublog'
LEFT JOIN prefix_ouwiki AS ouwiki ON ouwiki.id = cm.instance AND m.name = 'ouwiki'
LEFT JOIN prefix_subpage AS subpage ON subpage.id = cm.instance AND m.name = 'subpage'
LEFT JOIN prefix_journal AS journal ON journal.id = cm.instance AND m.name = 'journal'
LEFT JOIN prefix_lightboxgallery AS lightboxgallery ON lightboxgallery.id = cm.instance AND m.name = 'lightboxgallery'
LEFT JOIN prefix_elluminate AS elluminate ON elluminate.id = cm.instance AND m.name = 'elluminate'
LEFT JOIN prefix_adaptivequiz AS adaptivequiz ON adaptivequiz.id = cm.instance AND m.name = 'adaptivequiz'
LEFT JOIN prefix_hotpot AS hotpot ON hotpot.id = cm.instance AND m.name = 'hotpot'
LEFT JOIN prefix_wiziq AS wiziq ON wiziq.id = cm.instance AND m.name = 'wiziq'
LEFT JOIN prefix_turnitintooltwo AS turnitintooltwo ON turnitintooltwo.id = cm.instance AND m.name = 'turnitintooltwo'
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND cs.visible = 1
AND cm.visible = 1
AND c.id = %%COURSEID%%
GROUP BY cm.id
ORDER BY cs.section
日付間の活動モジュール（ヒット）
SELECT module, COUNT( * )
FROM prefix_logstore_standard_log AS l
WHERE (FROM_UNIXTIME( l.`timecreated` ) BETWEEN '2018-10-01 00:00:00' AND '2019-09-31 00:00:00')
GROUP BY module
各学年度の活動モジュール（インスタンスとヒット）
SELECT name
,(SELECT COUNT(*)
FROM prefix_logstore_standard_log AS l
WHERE (FROM_UNIXTIME(l.`timecreated`) BETWEEN '2017-10-01 00:00:00' AND '2018-09-31 00:00:00')
AND l.module = m.name AND l.action = 'add'
) AS "Added 2017"
,(SELECT COUNT(*)
FROM prefix_logstore_standard_log AS l
WHERE (FROM_UNIXTIME(l.`timecreated`) BETWEEN '2017-10-01 00:00:00' AND '2018-09-31 00:00:00')
AND l.module = m.name
) AS "Used 2017"
,(SELECT COUNT(*)
FROM prefix_logstore_standard_log AS l
WHERE (FROM_UNIXTIME(l.`timecreated`) BETWEEN '2018-10-01 00:00:00' AND '2019-09-31 00:00:00')
AND l.module = m.name AND l.action = 'add'
) AS "Added 2018"
,(SELECT COUNT(*)
FROM prefix_logstore_standard_log AS l
WHERE (FROM_UNIXTIME(l.`timecreated`) BETWEEN '2018-10-01 00:00:00' AND '2019-09-31 00:00:00')
AND l.module = m.name
) AS "Used 2018"
,(SELECT COUNT(*)
FROM prefix_logstore_standard_log AS l
WHERE (FROM_UNIXTIME(l.`timecreated`) BETWEEN '2019-10-01 00:00:00' AND '2020-09-31 00:00:00')
AND l.module = m.name AND l.action = 'add'
) AS "Added 2019"
,(SELECT COUNT(*)
FROM prefix_logstore_standard_log AS l
WHERE (FROM_UNIXTIME(l.`timecreated`) BETWEEN '2019-10-01 00:00:00' AND '2020-09-31 00:00:00')
AND l.module = m.name
) AS "Used 2019"
FROM mdl_modules AS m
1日および月ごとのユニークユーザセッション + グラフ
"グラフ" 列は、グラフを表示するときに使用されます（選択するには少なくとも3つの列が必要です）
SELECT COUNT(DISTINCT userid) AS "Unique User Logins"
,DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y /%m / %d") AS "Year / Month / Day", "Graph"
FROM `mdl_logstore_standard_log`
WHERE action LIKE 'loggedin'
- AND timecreated > UNIX_TIMESTAMP('2015-01-01 00:00:00') # optional start date
- AND timecreated <= UNIX_TIMESTAMP('2015-01-31 23:59:00') # optional end date
GROUP BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
ORDER BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
そして...
1日あたりのユーザのグローバルでユニークなヒット数のカウント + 特定の活動とリソースの個々の使用量のカウント（その日）、
また、phpMyAdminの "グラフの表示" 機能（クエリの出力ページの下部）を使用しているため、グラフに合わせて "ユーザヒット数" を10倍に縮小しました。 それでおしまい。
SELECT DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y-%m-%d") AS "Datez"
,COUNT(DISTINCT userid) AS "Unique Users"
,ROUND(COUNT(*)/10) "User Hits (K)"
,SUM(IF(component='mod_quiz',1,0)) "Quizzes"
,SUM(IF(component='mod_forum' or component='mod_forumng',1,0)) "Forums"
,SUM(IF(component='mod_assign',1,0)) "Assignments"
,SUM(IF(component='mod_oublog',1,0)) "Blogs"
,SUM(IF(component='mod_resource',1,0)) "Files (Resource)"
,SUM(IF(component='mod_url',1,0)) "Links (Resource)"
,SUM(IF(component='mod_page',1,0)) "Pages (Resource)"
FROM `mdl_logstore_standard_log`
WHERE 1=1
AND timecreated > UNIX_TIMESTAMP('2015-03-01 00:00:00') # optional START DATE
AND timecreated <= UNIX_TIMESTAMP('2015-05-31 23:59:00') # optional END DATE
GROUP BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
ORDER BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
システム全体で過去7日間の毎日のユニークユーザヒット数
SELECT
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m%d') 'Day'
,COUNT(DISTINCT l.userid) AS 'Distinct Users Hits'
,COUNT( l.userid) AS 'Users Hits'
FROM prefix_logstore_standard_log AS l
WHERE l.courseid > 1
AND FROM_UNIXTIME(l.timecreated) >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DAY(FROM_UNIXTIME(timecreated))
コースモジュールでのユーザの詳細な活動
URL、リソース、フォーラム、小テスト、アンケートなど、いくつかのモジュールのみを検討します。
SELECT u.id, ra.roleid,
CONCAT(u.lastname, ' ', u.firstname) AS 'Student'
,COUNT(l.id) AS 'Actions'
,l.component "Module type"
,l.objectid "Module ID"
,CASE
WHEN l.component = 'mod_url' THEN (SELECT u.name FROM prefix_url AS u WHERE u.id = l.objectid )
WHEN l.component = 'mod_resource' THEN (SELECT r.name FROM prefix_resource AS r WHERE r.id = l.objectid )
WHEN l.component = 'mod_forum' THEN (SELECT f.name FROM prefix_forum AS f WHERE f.id = l.objectid )
WHEN l.component = 'mod_quiz' THEN (SELECT q.name FROM prefix_quiz AS q WHERE q.id = l.objectid )
WHEN l.component = 'mod_questionnaire' THEN (SELECT q.name FROM prefix_questionnaire AS q WHERE q.id = l.objectid )
END AS 'Module name'
,(SELECT GROUP_CONCAT(g.name) FROM prefix_groups AS g
JOIN prefix_groups_members AS m ON g.id = m.groupid WHERE g.courseid = l.courseid AND m.userid = u.id) "user_groups"
,(SELECT s.name
FROM prefix_course_modules AS cm
JOIN prefix_course_sections AS s ON s.course = cm.course AND s.id = cm.section
WHERE cm.id = l.contextinstanceid) AS "Section name"
FROM prefix_logstore_standard_log AS l
JOIN prefix_user AS u ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON ra.userid = l.userid
AND ra.contextid = (SELECT id FROM prefix_context WHERE instanceid = l.courseid AND contextlevel = 50)
WHERE l.courseid = %%COURSEID%%
AND l.component IN ('mod_url', 'mod_resource', 'mod_forum', 'mod_quiz', 'mod_questionnaire')
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
GROUP BY u.id, l.component
ORDER BY u.lastname, u.firstname
どの教師とコースがアクティブであると見なされましたか？
このレポートには、オンラインアカデミックトレーニングチームが、オンライン学習の教育方法論をよりサポートするコースを取得するために、より多くのサポートが必要な教師を見つけるのに役立ついくつかの計算とパラメータが表示されます。
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',
course.id,'">',course.fullname,'</a>') AS Course
- ,course.shortname
,CASE
WHEN course.fullname LIKE '%2012%' THEN '2012'
WHEN course.fullname LIKE '%2013%' THEN '2013'
WHEN course.fullname LIKE '%2014%' THEN '2014'
WHEN course.fullname LIKE '%2015%' THEN '2015'
END AS Year
,CASE
WHEN course.fullname LIKE '%semester a%' THEN 'Spring semester'
WHEN course.fullname LIKE '%semester b%' THEN 'Fall semester'
WHEN course.fullname LIKE '%semester s%' THEN 'Summer semester'
END AS Semester
,IF(course.startdate>0, DATE_FORMAT(FROM_UNIXTIME(startdate), '%d-%m-%Y'), 'no date') AS "Course Start Date"
,(SELECT COUNT( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = course.id
) AS Students
,(SELECT COUNT( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 4 AND ctx.instanceid = course.id
) AS "Assistant teacher"
,(SELECT COUNT( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = course.id
) AS Teachers
- Uncomment to use the new Moodle 2.8+ logstore
- ,(SELECT COUNT(*) FROM mdl_logstore_standard_log AS l WHERE l.courseid = course.id) AS Hits
- ,(SELECT COUNT(*)
- FROM mdl_logstore_standard_log AS l
- JOIN mdl_role_assignments AS ra ON ra.userid= l.userid AND ra.roleid = 5 AND ra.contextid = (SELECT id FROM mdl_context WHERE instanceid = l.courseid AND contextlevel = 50)
- WHERE l.courseid = course.id ) AS "Student HITs"
- ,(SELECT COUNT(*)
- FROM mdl_logstore_standard_log AS l
- JOIN mdl_role_assignments AS ra ON ra.userid= l.userid AND ra.roleid = 3 AND ra.contextid = (SELECT id FROM mdl_context WHERE instanceid = l.courseid AND contextlevel = 50)
- WHERE l.courseid = course.id ) AS "Teacher HITs"
,(SELECT COUNT(*) FROM mdl_log AS l WHERE l.course = course.id) AS Hits
,(SELECT COUNT(*)
FROM mdl_log AS l
JOIN mdl_context AS con ON con.instanceid= l.course AND con.contextlevel=50
JOIN mdl_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 5
WHERE l.course = course.id) AS "Students HITs"
,(SELECT COUNT(*)
FROM mdl_log AS l
JOIN mdl_context AS con ON con.instanceid= l.course AND con.contextlevel=50
JOIN mdl_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 3
WHERE l.course = course.id) AS "Teachers HITs"
,(SELECT GROUP_CONCAT( CONCAT( u.firstname, " ", u.lastname ) )
FROM prefix_course c
JOIN prefix_context con ON con.instanceid = c.id
JOIN prefix_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50
JOIN prefix_role r ON ra.roleid = r.id
JOIN prefix_user u ON u.id = ra.userid
WHERE r.id = 3 AND c.id = course.id
GROUP BY c.id
) AS Teachers
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = course.id) Modules
,(SELECT COUNT(DISTINCT cm.module) FROM prefix_course_modules cm
WHERE cm.course = course.id) UniqueModules
,(SELECT GROUP_CONCAT(DISTINCT m.name)
FROM prefix_course_modules cm
JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id) UniqueModuleNames
,(SELECT COUNT(*) FROM mdl_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id AND m.name IN ( 'ouwiki', 'wiki') ) "Num Wikis"
,(SELECT COUNT(*) FROM mdl_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id AND m.name IN ( 'oublog') ) "Num Blogs"
,(SELECT COUNT(*) FROM mdl_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id AND m.name IN ( 'forum', 'forumng') ) "Num Forums"
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id AND m.name IN ('resource', 'folder', 'url', 'tab', 'file', 'book', 'page') ) Resources
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id AND m.name IN ('forum', 'forumng', 'oublog', 'page', 'file', 'url', 'wiki' , 'ouwiki') ) "Basic Activities"
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id AND m.name IN ('advmindmap', 'assign', 'attendance', 'book', 'choice', 'folder', 'tab', 'glossary', 'questionnaire', 'quiz', 'label' ) ) "Avarage Activities"
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE cm.course = course.id AND m.name IN ('elluminate', 'game', 'workshop') ) "Advanced Activities"
FROM prefix_course AS course
- WHERE course.shortname LIKE '%2015%'
- WHERE 1=1
- %%FILTER_SEARCHTEXT:course.shortname:~%%
WHERE course.fullname LIKE '%2015%'
HAVING Modules > 2
ORDER BY UniqueModules DESC
毎週の出席レポート
このレポートは、週次レポートをHH：M：SS形式で表示します。このMySQLクエリはAttendace Registerモジュールと連携して機能し、Attendanceregister_logテーブルからログ情報を収集します。
SELECT u.username, SEC_TO_TIME (SUM(arsess.duration)) AS weekly_online_attendance, FROM_UNIXTIME (arsess.logout) AS Last_Logout
FROM prefix_attendanceregister_session AS arsess
JOIN prefix_user AS u ON arsess.userid = u.id
WHERE (((arsess.logout) BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AND UNIX_TIMESTAMP(CURDATE())))
GROUP BY arsess.userid
月ごとにアプリを使用してMoodleに接続した個別のユーザ数
https://moodle.org/mod/forum/discuss.php?d=336086#p1354194 by
Iñigo Zendegi Urzelai
SELECT
to_char(to_timestamp("timecreated"),'YYYY') as year,
to_char(to_timestamp("timecreated"),'MM') as month,
count(distinct userid) as distinct_users
FROM prefix_logstore_standard_log l
WHERE l.origin='ws'
GROUP BY to_char(to_timestamp("timecreated"),'YYYY'), to_char(to_timestamp("timecreated"),'MM')
ORDER BY to_char(to_timestamp("timecreated"),'YYYY'), to_char(to_timestamp("timecreated"),'MM');
コースレポート
最もアクティブなコース
SELECT count(l.userid) AS Views
FROM `mdl_logstore_standard_log` l, `mdl_user` u, `mdl_role_assignments` r
WHERE l.courseid=35
AND l.userid = u.id
AND (l.timecreated > UNIX_TIMESTAMP('2015-01-01 00:00:00') AND l.timecreated <= UNIX_TIMESTAMP('2015-01-31 23:59:59'))AND r.contextid= (
SELECT id
FROM mdl_context
WHERE contextlevel=50 AND instanceid=l.courseid
)
AND r.roleid=5
AND r.userid = u.id
アクティブコース、上級
含まれるもの: 教師の名前、コースへのリンク、すべてのタイプの活動ログ、特別なYEAR生成フィールド、活動とリソース数、登録済みの学生数
SELECT COUNT(l.id) hits, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,CASE
WHEN c.fullname LIKE '%תשע' THEN 'תשע'
WHEN c.fullname LIKE '%תשעא' THEN 'תשעא'
WHEN c.fullname LIKE '%תשעב' THEN 'תשעב'
END AS Year
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
FROM prefix_log l
INNER JOIN prefix_course c ON l.course = c.id
GROUP BY c.id
- The following line restricts the courses returned to those having more than 2 modules. Adjust based on your needs.
HAVING Modules > 2
ORDER BY Year DESC, hits DESC
最もアクティブでない、または、おそらく空のコース
寄稿者：Randy Thornton
コースが実際に空であるか、実際に使用されていないかを知るのは難しい場合があります。 コースが作成され、二度と触れられないという単純な場合を除いて、作成されたコースと変更されたコースは同じになります。教師や他のユーザーのシェルとして作成された多くのコースは、1回または数回使用され、数が少ないか、 テストユーザは登録していません。 このクエリは、そのようなコースの範囲を確認するのに役立ち、最初の作成後に使用された日数と、登録されているユーザの数を示します。 これは、"0" ではなく "-1" で変更されたことがないコースを示しているため、それらを一番上に並べ替えることができます。 デフォルトでは、これは作成から60日以内に使用されるコースと、登録数が3以下のコース（たとえば、教師とアシスタント、およびテスト学生のアカウントのみ）に制限されます。これらの数は簡単に調整できます。 クエリには、コースへのリンクも含まれています。
SELECT
c.fullname,
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'CourseLink',
DATE_FORMAT(FROM_UNIXTIME(c.timecreated), '%Y-%m-%d %H:%i') AS 'Timecreated',
DATE_FORMAT(FROM_UNIXTIME(c.timemodified), '%Y-%m-%d %H:%i') AS 'Timemodified',
CASE
WHEN c.timecreated = c.timemodified THEN '-1'
ELSE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated))
END AS 'DateDifference',
COUNT(ue.id) AS Enroled
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
LEFT JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
WHERE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated) ) < 60
GROUP BY c.id
HAVING COUNT(ue.id) <= 3
ORDER BY c.fullname
少なくともXモジュール（Moodle19）を使用するコースを持つユニークな教師数
外側の "SELECT COUNT(*) FROM (...) AS ActiveTeachers" SQLクエリを削除して、教師とコースのリストを取得できます。
SELECT COUNT(*)
FROM (SELECT c.id AS CourseID, c.fullname AS Course, ra.roleid AS RoleID, CONCAT(u.firstname, ' ', u.lastname) AS Teacher
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) AS Modules
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE ra.roleid = 3
GROUP BY u.id
HAVING Modules > 5) AS ActiveTeachers
各コースのリソース数
SELECT COUNT(l.id) count, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
GROUP BY course
ORDER BY count DESC
各カテゴリの共通リソースタイプ数（Moodle19）
合計数にサブカテゴリを含めます。
SELECT mcc.id AS mccid, CONCAT( LPAD( , mcc.depth, '.' ) , mcc.name ) AS Category
,(SELECT COUNT( * )
FROM prefix_resource AS r
JOIN prefix_course AS c ON c.id = r.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference LIKE 'http://%'
) AS Links
,(SELECT COUNT( * )
FROM prefix_resource AS r
JOIN prefix_course AS c ON c.id = r.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference NOT LIKE 'http://%'
) AS Files
,(SELECT COUNT( * )
FROM prefix_resource AS r
JOIN prefix_course AS c ON c.id = r.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'directory'
) AS Folders
,(SELECT COUNT( * )
FROM prefix_resource AS r
JOIN prefix_course AS c ON c.id = r.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'html'
) AS Pages
,(SELECT COUNT(*)
FROM stats_log_context_role_course
WHERE roleid = 5 AND module = 'resource' AND category = mcc.id
) AS Hits
FROM prefix_course_categories AS mcc
ORDER BY mcc.path
ここで、"stats_log_context_role_course"（上記のSQLクエリ内）は、次の方法で生成されたVIEWです。
CREATE VIEW stats_log_context_role_course AS
SELECT l.course, c.category, cc.path, l.module, l.action, ra.userid, ra.roleid
FROM prefix_log AS l
JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50
JOIN prefix_role_assignments AS ra ON ra.userid = l.userid AND ra.contextid = context.id
JOIN prefix_course AS c ON c.id = l.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
同じクエリですが、Moodle2 +用です
SELECT mcc.id AS mccid, CONCAT( LPAD( , mcc.depth, '.' ) , mcc.name ) AS Category,
mcc.path,
(SELECT COUNT(*)
FROM prefix_url AS u
JOIN prefix_course AS c ON c.id = u.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS URLs,
(SELECT COUNT(*)
FROM prefix_folder AS f
JOIN prefix_course AS c ON c.id = f.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS FOLDERs,
(SELECT COUNT(*)
FROM prefix_page AS p
JOIN prefix_course AS c ON c.id = p.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS PAGEs,
(SELECT COUNT(*)
FROM prefix_book AS b
JOIN prefix_course AS c ON c.id = b.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS BOOKs,
(SELECT COUNT(*)
FROM prefix_label AS l
JOIN prefix_course AS c ON c.id = l.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS LABELs,
(SELECT COUNT(*)
FROM prefix_tab AS t
JOIN prefix_course AS c ON c.id = t.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS TABs
FROM prefix_course_categories AS mcc
ORDER BY mcc.path
各コースの教師による詳細なリソース数
年、学期、コースIDによる（オプションの）フィルタを含みます。
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS CourseID
, c.id
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
FROM prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
, (CASE
WHEN c.fullname LIKE '%תשעב%' THEN '2012'
WHEN c.fullname LIKE '%תשעא%' THEN '2011'
END ) as Year
, (CASE
WHEN c.fullname LIKE '%סמסטר א%' THEN 'Semester A'
WHEN c.fullname LIKE '%סמסטר ב%' THEN 'Semester B'
WHEN c.fullname LIKE '%סמסטר ק%' THEN 'Semester C'
END ) as Semester
,COUNT(c.id) AS Total
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 20) AS TABs
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 33) AS BOOKs
FROM `prefix_resource` as r
JOIN `prefix_course` AS c on c.id = r.course
- WHERE type= 'file' and reference NOT LIKE 'http://%'
- WHERE 1=1
- %%FILTER_YEARS:c.fullname%%
- AND c.fullname LIKE '%2013%'
GROUP BY course
ORDER BY COUNT(c.id) DESC
グループを使用するように定義されているコース
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
,(SELECT count(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups
FROM `prefix_course` AS c
WHERE groupmode > 0
グループのあるコース
寄稿者：Randy Thornton
グループが含まれるすべてのコースのリスト（groupmode > 0）。 groupmode = 1を使用して個別のタイプグループのみを一覧表示したり、groupmode = 2を使用して表示可能なタイプグループを一覧表示したりすることもできます。
SELECT c.shortname, g.name, c.groupmode
FROM prefix_course AS c
JOIN prefix_groups AS g ON c.id = g.courseid
WHERE c.groupmode > 0
グループのあるコースに登録しているがグループが割り当てられていないユーザ
グループのあるコースで、グループが割り当てられていないすべての登録ユーザをコースごとに表示します。 注: これは最適化する必要があります。
SELECT DISTINCT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
course.fullname AS Course
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) AS ROLE
,(SELECT name FROM prefix_role WHERE id=en.roleid) AS RoleName
FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS user2 ON ue.userid = user2.id
JOIN prefix_groups AS g ON g.courseid = course.id
WHERE ue.enrolid NOT IN (select userid from prefix_groups_members WHERE g.id=groupid)
ORDER BY Course, Lastname
メンバーリストのあるコースのグループ
寄稿者: Randy Thornton
コース内のグループを各グループのメンバーと一緒にリストします（#をコースID番号に置き換えます）。
SELECT c.shortname, g.name AS Groupname, u.username
FROM prefix_course AS c
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_user AS u ON m.userid = u.id
WHERE c.id = #
注: Configurable Reportsブロックを使用していて、現在のコースでこのクエリを実行する場合は、次のようなWHERE句を使用できます。
WHERE c.id = %%COURSEID%%
グループのエクスポート
寄稿者：Randy Thornton
グループのインポート関数はありますが、エクスポートはありません。 これを使用して、適切な列の順序と見出しを含むレポートをcsvファイルにエクスポートし、別のコースにインポートしてグループを複製できます。 これは、グループ名、説明、登録キーのメインフィールドのみを含む単純なバージョンです。
SELECT g.name AS groupname, g.description, g.enrolmentkey
FROM prefix_groups AS g
JOIN prefix_course as c ON g.courseid = c.id
WHERE c.id = #
注: Configurable Reportsブロックを使用していて、現在のコースでこのクエリを実行する場合は、次のようなWHERE句を使用できます。
WHERE c.id = %%COURSEID%%
特定のカテゴリ内およびそれ以下のすべてのコースを一覧表示します
このSQLコードを使用して、設定されたカテゴリ内またはその下に存在するすべてのコースを取得します。
$sは、知りたいカテゴリのIDである必要があります...
SELECT prefix_course. * , prefix_course_categories. *
FROM prefix_course, prefix_course_categories
WHERE prefix_course.category = prefix_course_categories.id
AND (
prefix_course_categories.path LIKE '%/$s/%'
OR prefix_course_categories.path LIKE '%/$s'
)
特定のカテゴリの下の1つのレベルにすべてのカテゴリを一覧表示します
このPHPコードを使用して、特定のカテゴリの下にあるすべてのカテゴリのリストを取得します。
$sは、関心のある最上位カテゴリのIDである必要があります。
<?php
require_once('./config.php');
$parent_id = $s;
$categories= array();
$categories = get_categories($parent_id);
echo '
';
foreach ($categories as $category)
{
echo '- <a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a>
';
}
echo '
';
?>
コースごとのブログ活動（VIEWを含まない）
活動ログを特定のコースカテゴリにフィルタリングします。
+コース名を実際のコースにリンクする（クイックリファレンス用）
（%blog%を%wiki%に変更して、すべてのwiki活動またはその他の必要なモジュールをフィルタリングできます）
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
,m.name ,count(cm.id) as counter
,(SELECT Count( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5
AND ctx.instanceid = c.id
) AS Students
, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
GROUP BY cm.course,cm.module order by counter desc
すべてのコースブログ（oublog）での学生の投稿コンテンツ
SELECT
b.name
,op.title
,op.message
,( SELECT CONCAT(u.firstname, ' ',u.lastname) FROM prefix_user AS u WHERE u.id = oi.userid) AS "Username"
FROM prefix_oublog_posts AS op
JOIN prefix_oublog_instances AS oi ON oi.id = op.oubloginstancesid
JOIN prefix_oublog as b ON b.id = oi.oublogid
JOIN prefix_course AS c ON b.course = c.id
WHERE c.id = %%COURSEID%%
シラバスファイルをアップロードしたすべてのコース
+特定のカテゴリの下
+そのコースの最初の教師を表示する
+コースのフルネームを実際のコースにリンクする
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,c.shortname,r.name
,(SELECT CONCAT(u.firstname,' ', u.lastname) as Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user as u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) as Teacher
FROM prefix_resource as r
JOIN prefix_course as c ON r.course = c.id
WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' )
AND c.category IN (10,18,26,13,28)
コース名別のサイト全体で完了したSCORM活動
このレポートには、すべてのSCORM活動で完了したすべての受験が一覧表示されます。 最初にコース名、次に学生の姓、次に学生の名、次に受験番号の順に並べられます。 注意: FROM_UNIXTIMEコマンドはMySQL用です。
SELECT u.firstname First,u.lastname Last,c.fullname Course, st.attempt Attempt,st.value Status,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") Date
FROM prefix_scorm_scoes_track AS st
JOIN prefix_user AS u ON st.userid=u.id
JOIN prefix_scorm AS sc ON sc.id=st.scormid
JOIN prefix_course AS c ON c.id=sc.course
WHERE st.value='completed'
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
ロールなしでコースに登録したすべてのユーザ
コースに登録されているがロールが割り当てられていないすべてのユーザを識別します。
SELECT
user.firstname AS Firstname,
user.lastname AS Lastname,
user.idnumber Employee_ID,
course.fullname AS Course
FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user as user ON user.id = ue.userid
WHERE user.id NOT IN (
SELECT u.id
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE c.id=course.id
)
ORDER BY Course, Lastname, Firstname
コースリソースの累積ファイルサイズとカウントを一覧表示します
これはメイン（最初の）レポートであり、コース内の各ファイルを一覧表示する2番目のレポート（このページの次のレポート）へのリンク（エイリアス）があります。
SELECT c.id "CourseID", context.id "ContextID"
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=', c.id, '">', c.fullname ,'</a>') AS "Course Name"
, COUNT(*) "Course Files" , ROUND( SUM( f.filesize ) /1048576 ) AS file_size_MB
,CONCAT('<a target="_new" href="%%WWWROOT%%/blocks/configurable_reports/viewreport.php?alias=coursefiles&courseid=1&filter_courses=', c.id, '">List files</a>') AS "List Files"
FROM mdl_files AS f
JOIN mdl_context AS context ON context.id = f.contextid
JOIN mdl_course AS c ON c.id = (
SELECT instanceid
FROM mdl_context
WHERE id = SUBSTRING_INDEX( SUBSTRING_INDEX( context.path, '/' , -2 ) , '/', 1 ) )
WHERE filesize >0
GROUP BY c.id
このレポートでは、上記のレポートから呼び出すことができるように、"エイリアス" レポートプロパティを "コースファイル" に定義する必要があります。
また、FILTER_COURSESフィルターを設定（追加）します。
SELECT
id ,CONCAT('<a target="_new" href="%%WWWROOT%%/pluginfile.php/', contextid, '/', component, '/', filearea, '/', itemid, '/', filename, '">', filename,'</a>') AS "File"
,filesize, mimetype ,author, license, timecreated, component, filearea, filepath
FROM mdl_files AS f
WHERE filesize >0
AND f.contextid
IN ( SELECT id
FROM mdl_context
WHERE path
LIKE ( SELECT CONCAT('%/',id,'/%')
AS contextquery
FROM mdl_context
WHERE 1=1
%%FILTER_COURSES:instanceid%%
AND contextlevel = 50
)
)
どのコースに冗長なトピックがあるか
このレポートには、コースごとにいくつかの "アクティブなトピック" の計算がリストされています。 これにより、管理者は、どのトピック/セクション/週がリソースと活動で満たされ、どのトピック/セクション/週が空で使用されていないか（通常はコースの最後）を示す必要があります。
次の2番目のSQLクエリは、コース形式のnumsection（セクション数）設定を更新することにより、これらの冗長なコーストピック/セクション/週を "トリム" するために使用できます。 （これはコースごとのフォーマット設定です！）
SELECT id, format,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">', c.fullname,'</a>') AS Course
,(SELECT value FROM `mdl_course_format_options` WHERE `courseid` = c.id AND `format` = c.format AND `name` = 'numsections' ) AS "numsections"
,(SELECT COUNT(*) FROM `mdl_course_sections` WHERE `course` = c.id AND `sequence` != ) AS "Non empty sections count"
,(SELECT COUNT(*) FROM `mdl_course_sections` WHERE `course` = c.id ) AS "Total section count"
,(SELECT COUNT(*) FROM `mdl_course_sections` WHERE `course` = c.id AND sequence IS NOT NULL) AS "Non NULL sections count"
,(SELECT COUNT(*) FROM `mdl_course_sections` WHERE `course` = c.id AND name != ) AS "Non empty section Name count"
,(SELECT COUNT(*) FROM mdl_course_modules cm WHERE cm.course = c.id) "Modules count"
FROM mdl_course AS c
次のSQLREPLACEクエリは、特定のコースフォーマット "onetopics" の "numsections" を "修正"（更新）するために使用されます（すべてのコース形式でこのSQL REPLACEを使用するには、いつでも変更または破棄できます）。
REPLACE INTO `mdl_course_format_options` (`id`, `courseid`, `format`, `sectionid`, `name`, `value`)
SELECT NULL, c.id, 'onetopic', '0', 'numsections', (SELECT COUNT(*) FROM `mdl_course_sections` WHERE `course` = c.id AND name != )
FROM `mdl_course` c where format = 'onetopic'
学生が登録されている非表示のコース
Eric Stromによる寄稿
このクエリは、現在学生から隠されている学生登録のあるコースを識別します。 定義されたコース開始日、学生とインストラクタの数、およびインストラクタのクリック可能な電子メールリンク（複数の場合は最初に見つかったレコード）が含まれます。
SELECT c.visible AS Visible,
DATE(FROM_UNIXTIME(c.startdate)) AS StartDate,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',
c.id,'">',c.idnumber,'</a>') AS Course_ID,
(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students,
(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Instructors,
(SELECT DISTINCT concat('<a href="mailto:',u.email,'">',u.email,'</a>')
FROM prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS 'Instructor_Email',
now() AS Report_Timestamp
FROM prefix_course AS c
WHERE c.visible = 0 AND (SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) > 0
ORDER BY StartDate, Instructor_Email, Course_ID
コースデザインレポート
これらは、セクションごとの活動とリソースモジュール、使用される活動のタイプなど、コース設計の側面を要約したレポートです。
コースコンテンツ/週
Granite State CollegeのElizabeth Daltonによる寄稿
このレポートでは、コースの最初の14セクション（"0" または "ようこそ" セクションを除く）が週に対応していることを前提としています（"サブセクション" には、シーケンスの番号がはるかに大きくなっています）。 これらのセクションのうち、それぞれがチェックされて、次の数がカウントされます。
フォーラム
段階的な活動（フォーラムが含まれる場合があります）
リソース（ラベルを含まない）
セクションごとのこれらのタイプのコンテンツ要素のそれぞれの合計が提供されます。
注: 表示されているリソースと活動のみがカウントされます。
注: これは "グローバル" レポートです。 コース内で実行すると、そのコースの内容の概要が表示されます。
SELECT
cs.section AS 'Week'
, cs.name AS 'Section Name'
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT LIKE 'label'),cm.id,NULL)) AS 'Ungraded Resources'
, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Graded Activities'
FROM prefix_course AS c
JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.section <= 14 AND cs.section > 0
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
JOIN prefix_modules AS m ON m.id = cm.module
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance
WHERE
cs.visible = 1
AND cm.visible = 1
AND c.id = %%COURSEID%%
GROUP BY cs.section
ORDER BY cs.section
課題と重み
Granite State CollegeのElizabeth Daltonによる寄稿
現在のコースの評定表カテゴリのリスト、評定表の重み付け、カテゴリに含まれる最初のタイプの課題、各カテゴリのさまざまな課題タイプの数、および各カテゴリの課題の数を返します。
重みが0のカテゴリは、このレポートには含まれません。
このレポートには、目に見える活動のみが含まれています。
注: これは、構成可能レポートの "グローバル" レポートになるように設計されています。
SELECT
IF(gc.parent IS NOT NULL, gc.fullname, 'None') AS 'Grade Book Category'
, IF(gc.parent IS NOT NULL, ROUND(gic.aggregationcoef, 2), ROUND(SUM(DISTINCT gi.aggregationcoef), 2)+ROUND(SUM(DISTINCT mgi.aggregationcoef), 2)) AS 'Category weight'
, CONCAT_WS(', ',GROUP_CONCAT(DISTINCT gi.itemmodule SEPARATOR ', '), IF(mgi.id, 'manual',NULL)) AS 'Activity Types'
, COUNT(DISTINCT gi.itemmodule) + IF(mgi.id,1,0) AS 'Different Activity Types'
, CONCAT_WS('
', GROUP_CONCAT(DISTINCT gi.itemname ORDER BY gi.itemname SEPARATOR '
'), GROUP_CONCAT(DISTINCT mgi.itemname ORDER BY mgi.itemname SEPARATOR '
')) AS 'Activity Names'
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) + COUNT(DISTINCT mgi.id) AS 'Activity Count'
FROM prefix_course AS c
- get grade categories
LEFT JOIN prefix_grade_categories AS gc ON gc.courseid = c.id
- back from categories to grade items to get aggregations and weights
JOIN prefix_grade_items AS gic ON gic.courseid = c.id AND gic.itemtype = 'category' AND gic.aggregationcoef != 0 AND (LOCATE(gic.iteminstance, gc.path) OR (gc.parent IS NULL))
- attach activities to course
JOIN prefix_course_modules AS cm ON cm.course = c.id
- attach grade items to activities
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.iteminstance = cm.instance AND gi.itemtype = 'mod' AND gi.categoryid = gc.id AND gi.hidden != 1
- attach manual grade items to course-- they don't have modules
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = c.id and mgi.itemtype = 'manual' AND mgi.categoryid = gc.id
WHERE
cm.visible = 1
AND c.id = %%COURSEID%%
GROUP BY gc.id
ORDER BY gc.id
学期前のコースレビュー
Granite State CollegeのElizabeth Daltonによる寄稿
ステージングカテゴリとすべてのサブカテゴリのオンライン、ハイブリッド、およびブレンドコースの準備状況の概要を説明します。 各コースへのリンクが提供されています。 その他の情報:
- "必須ブロック" には、インストラクタブロック（mooprofile）、活動、およびリサーチブロックが含まれます。
- "インストラクタの詳細" ブロックは、システムによって自動的に提供される "インストラクタ" ブロック（mooprofile）ではありません。 インストラクタが編集できるオプションのブロックです。 ボイラープレートテキストを削除するように編集していない場合は、非表示にする必要があります。
- すべてのコースは、"週" 構造の "折りたたまれたトピック" フォーマットである必要があります。
- "コース設定で定義された週" は、コースシェルの作成時にSISから取得されますが、教員が編集できます。"名前が付けられて表示される週数" は通常、この値と一致するか、それを超える必要があります。
- 毎週、少なくとも1つのフォーラム、少なくとも1つの評定済み活動、および少なくとも1つの評定されていないリソースを含めることをお勧めします。
- "シラバス更新" の日付は、名前に "シラバス" というテキストが含まれている最初の添付ファイルです。 便宜上、"数日前" の計算が含まれています。
注: 当校では、各学期にカテゴリを作成し、コース開発の準備段階または "ステージング" フェーズで、学期前コースのカテゴリIDにテキスト文字列 "ステージング" を挿入します。 新しい学期の開始時にコースが公開されると、このテキスト文字列が削除されます（そして "本番" に変更されます）。
SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS Course
- ,RIGHT(c.idnumber,2) AS Type # Specific to GSC "Instructional Method" storage
- , substring_index(substr(c.shortname FROM locate('.',c.shortname)+1),'-',1) AS Section # Specific to GSC
,(SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.lastname,', ', u.firstname,'</a>')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Instructor'
,(SELECT IF((u2.description IS NULL) OR (u2.description LIKE ),'NO', 'YES')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u2 ON u2.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Bio'
,(SELECT IF(u3.picture > 0,'YES','NO')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u3 ON u3.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Picture'
, IF(((bpi.visible IS NULL) OR (bpi.visible !=0)) AND ((bpm.visible IS NULL) OR (bpm.visible !=0)) AND ((bpa.visible IS NULL) OR (bpa.visible !=0)) AND ((bpr.visible IS NULL) OR (bpr.visible !=0)),'YES','NO') AS 'Required blocks visible'
- , IF((bpm.visible IS NULL) OR (bpm.visible !=0),'YES','NO') AS 'Messages block visible'
- , IF((bpa.visible IS NULL) OR (bpa.visible !=0),'YES','NO') AS 'activities block visible'
- , IF((bpr.visible IS NULL) OR (bpr.visible !=0),'YES','NO') AS 'research block visible'
- , IF(SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)) AND (bip.visible !=0),'YES',) AS 'Instructor Details Block visible' # This is a hack based on UUencoded string data from the title of HTML "Instructor Details" block
- , IF(bi.configdata LIKE '%ZGl0IHRoaXMgYmxvY2s%','NO',) AS 'Instructor Details Block Updated' # HTML block has string 'dit this block'
- , IF(COUNT(bi.id) - SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)),'YES',) AS 'possible extra instructor blocks' #looking for any HTML block with "instructor" in the title
, IF(c.format='topcoll','YES', c.format) AS 'Collapsed Topics course format' # change this if you want to test for a different format
, IF(cfo.value = 2, 'YES','NO') AS 'weeks structure'
, cfw.value AS 'weeks defined in course settings'
, COUNT(DISTINCT IF(((cs.name IS NOT NULL) AND (cs.visible = 1) AND (cs.section != '0') AND (cs.sequence IS NOT NULL)),cs.id,NULL)) AS '# of weeks named & visible (includes orphans)'
, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'
, COUNT(DISTINCT IF(m.name LIKE 'forum' ,cs.id , NULL)) AS 'Weeks with Forum'
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Activities'
, COUNT(DISTINCT IF(gi.id, cs.id, NULL)) AS 'Weeks with Activities'
, COUNT(DISTINCT mgi.id) AS 'Manual Grade Items'
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT IN ('forum','label')),cm.id,NULL)) AS 'Resources'
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT IN ('forum','label')), cs.id, NULL)) AS 'Weeks with Resources'
- Here are some other things you could check for per course
- ,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
- ,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%quiz%') AS Quizzes
- ,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%assign%') AS Assignments
- ,(SELECT COUNT(prefix_resource.id) FROM prefix_resource JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files
- ,(SELECT COUNT(prefix_url.id) FROM prefix_url JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links
,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS SyllabusDate
,(SELECT TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', f.id,NULL)),'YES','NO' ) AS 'Announcement Forum Visible'
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', fd.id,NULL)),'YES','NO' ) AS 'Announcement posted'
FROM prefix_course AS c
LEFT JOIN prefix_course_categories as cc ON c.category = cc.id
LEFT JOIN prefix_context AS ctxx ON c.id = ctxx.instanceid
LEFT JOIN prefix_block_positions AS bpi ON bpi.contextid = ctxx.id AND bpi.blockinstanceid = '43692' # mooprofile
LEFT JOIN prefix_block_positions AS bpm ON bpm.contextid = ctxx.id AND bpm.blockinstanceid = '43962' # messages
LEFT JOIN prefix_block_positions AS bpa ON bpa.contextid = ctxx.id AND bpa.blockinstanceid = '43963' # activities
LEFT JOIN prefix_block_positions AS bpr ON bpr.contextid = ctxx.id AND bpr.blockinstanceid = '38368' # html research help
LEFT JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.visible = 1 AND cs.sequence IS NOT NULL
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
LEFT JOIN prefix_modules AS m ON m.id = cm.module
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance
LEFT JOIN prefix_forum AS f ON f.course = c.id AND cm.instance = f.id AND cm.visible = 1
LEFT JOIN prefix_forum_discussions AS fd ON fd.forum = f.id
- attach manual grade items to course-- they don't have modules
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = c.id and mgi.itemtype = 'manual'
LEFT JOIN prefix_course_format_options AS cfo ON cfo.courseid = c.id AND cfo.name = 'layoutstructure'
LEFT JOIN prefix_course_format_options AS cfw ON cfw.courseid = c.id AND cfw.name = 'numsections'
LEFT JOIN prefix_block_instances AS bi ON bi.parentcontextid = ctxx.id AND bi.blockname = 'html' AND (bi.configdata LIKE '%SW5zdHJ1Y3Rvc%' or bi.configdata LIKE '%bnN0cnVjdG9y%')
LEFT JOIN prefix_block_positions AS bip ON bip.blockinstanceid = bi.id
WHERE RIGHT(c.idnumber,2) IN ('OL', 'BL', 'HY')
- AND substring(cc.path,2,2) IN ('26') # Staging
- AND substring(cc.path,2,3) IN ('158') # UG
AND cc.idnumber LIKE '%staging%'
AND ctxx.contextlevel = 50
GROUP BY c.shortname
モジュールインスタンス+コース内の教師と学生ロールによるモジュールHIT
SELECT
m.name AS "Module name"
, COUNT(*) AS "Module count"
,(SELECT COUNT(*)
FROM prefix_log AS l
WHERE l.course = cm.course AND l.module = m.name ) AS "Hits"
,(SELECT COUNT(*)
FROM prefix_log AS l
JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 5
WHERE l.course = cm.course AND l.module = m.name) AS "Students HITs"
,(SELECT COUNT(*)
FROM prefix_log AS l
JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 3
WHERE l.course = cm.course AND l.module = m.name) AS "Teachers HITs"
FROM mdl_course_modules AS cm
JOIN mdl_modules AS m on m.id = cm.module
WHERE cm.course = '%%COURSEID%%'
GROUP BY cm.module
コースシラバス
Granite State College / Moodle HQのElizabeth Daltonによる寄稿
このレポートにはELISが必要です。 コース内から実行され、コースのコンテンツとコースに関連するELISエントリ（クラスインスタンス、コースの説明、およびプログラム）に基づいてコースのシラバスを作成します。 これは、自動化されたシラバス作成ツールの概念実証です。"コースポリシー" や "教育哲学" などのフィールドがクラスインスタンスレコードに追加され、インストラクタがそこに入力します。 インストラクタの経歴は、コースで教師のロールを持つすべてのユーザのユーザプロファイルから取得されます。
SELECT
c.fullname AS 'fullname'
, ec.idnumber AS 'elis-id'
, DATE_FORMAT(FROM_UNIXTIME(ec.startdate), '%b %e, %Y') AS 'start'
, DATE_FORMAT(FROM_UNIXTIME(ec.enddate), '%b %e, %Y') AS 'end'
, ecd.name AS 'longname'
, ecd.code AS 'coursecode'
, ecd.credits AS 'coursecredits'
, ecd.syllabus AS 'description'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'learning-outcomes'
WHERE ctxecd.id = eft.contextid) AS 'outcomes'
,(SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.firstname,' ', u.lastname,'</a> ', u.email)
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Instructor'
, (SELECT efc.data
FROM prefix_local_eliscore_fld_data_char AS efc
JOIN prefix_local_eliscore_field AS ef ON ef.id = efc.fieldid AND ef.shortname = 'term-code'
WHERE ctxci.id = efc.contextid) AS 'termcode'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'prerequisites'
WHERE ctxecd.id = eft.contextid) AS 'prerequisites'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'textbooks'
WHERE ctxci.id = eft.contextid) AS 'textbooks'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'other-class-materials'
WHERE ctxci.id = eft.contextid) AS 'other-class-materials'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'course-policies'
WHERE ctxci.id = eft.contextid) AS 'course-policies'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'teaching-philosophy'
WHERE ctxci.id = eft.contextid) AS 'teaching-philosophy'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'course-methods'
WHERE ctxci.id = eft.contextid) AS 'course-methods'
,(SELECT u2.description
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u2 ON u2.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Bio'
,(SELECT
GROUP_CONCAT(DISTINCT CONCAT(
'',IF(gc.parent IS NOT NULL, gc.fullname, 'None')
, ' '
,IF(gc.parent IS NOT NULL, ROUND(gic.aggregationcoef, 2), ROUND( gi.aggregationcoef, 2)+ROUND(mgi.aggregationcoef, 2))
) SEPARATOR '')
- get grade categories
FROM prefix_grade_categories AS gc
- back from categories to grade items to get aggregations and weights
LEFT JOIN prefix_grade_items AS gic ON gic.courseid = gc.courseid AND gic.itemtype = 'category' AND gic.aggregationcoef != 0 AND (LOCATE(gic.iteminstance, gc.path) OR (gc.parent IS NULL))
- attach grade items to activities
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = gc.courseid AND gi.itemtype = 'mod' AND gi.categoryid = gc.id AND gi.hidden != 1
- attach manual grade items to course-- they don't have modules
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = gc.courseid and mgi.itemtype = 'manual' AND mgi.categoryid = gc.id
WHERE gc.courseid = c.id ) AS 'grade categories'
, '
' AS 'table start'
, '
' AS 'table start 2'
, '
' AS 'table end'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'activities-schedule'
WHERE ctxci.id = eft.contextid) AS 'activities'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'schedule'
WHERE ctxci.id = eft.contextid) AS 'schedule'
, (SELECT eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'grading-scale'
WHERE ctxepm.id = eft.contextid) AS 'gradescale'
FROM
prefix_course AS c
- connect moodle course to ELIS class instance
LEFT JOIN prefix_local_elisprogram_cls_mdl AS ecm ON ecm.moodlecourseid = c.id
LEFT JOIN prefix_local_elisprogram_cls AS ec ON ec.id = ecm.classid
- class instance context
LEFT JOIN prefix_context AS ctxci ON ctxci.instanceid = ec.id AND ctxci.contextlevel = '14'
- connect ELIS class instance to ELIS course description
LEFT JOIN prefix_local_elisprogram_crs AS ecd ON ecd.id = ec.courseid
- course description context
LEFT JOIN prefix_context AS ctxecd ON ctxecd.instanceid = ecd.id AND ctxecd.contextlevel = '13'
- connect ELIS program to ELIS Course Description
LEFT JOIN prefix_local_elisprogram_pgm_crs AS epc ON epc.courseid = ecd.id
LEFT JOIN prefix_local_elisprogram_pgm AS epm ON epm.id = epc.curriculumid
- course program context
LEFT JOIN prefix_context AS ctxepm ON ctxepm.instanceid = epm.id AND ctxepm.contextlevel = '11'
WHERE
c.id = %%COURSEID%%
コース活動ヘルパー
Granite State CollegeのElizabeth Daltonによる寄稿
このレポートは、コースの評定された活動のリストを提供します。
- 注: 評定された活動のみが表示されます。
- 注: これは "グローバル" レポートです。 コース内で実行すると、そのコースの内容の概要が表示されます。
- 注: このレポートは、コースセクションがそれぞれ最後の1週間であることを前提としています。
- 303 Course Activities Helper
SELECT
gi.itemmodule AS 'activity type'
- cs.section AS 'section number'
- Calculation assumes each section lasts one week
, CONCAT(DATE_FORMAT(FROM_UNIXTIME(c.startdate + (7*24*60*60* (cs.section-1))), '%b %e, %Y'),' -
',DATE_FORMAT(FROM_UNIXTIME(c.startdate + (7*24*60*60* (cs.section))), '%b %e, %Y')) AS 'Date'
, gi.itemname AS 'activity name'
- , (SELECT asg.intro FROM prefix_assign AS asg WHERE asg.id = cm.instance) AS 'intro'
- , (SELECT f.intro FROM prefix_forum AS f WHERE f.id = cm.instance) AS 'f intro'
, CASE gi.itemmodule
WHEN 'assign' THEN (SELECT asg.intro FROM prefix_assign AS asg WHERE asg.id = gi.iteminstance)
WHEN 'forum' THEN (SELECT f.intro FROM prefix_forum AS f WHERE f.id = gi.iteminstance)
WHEN 'quiz' THEN (SELECT q.intro FROM prefix_quiz AS q WHERE q.id = gi.iteminstance)
END AS 'test case'
- , (SELECT GROUP_CONCAT(CONCAT(' - ',gi.itemname) SEPARATOR '
') FROM prefix_grade_items AS gi JOIN prefix_course_modules AS cm ON gi.iteminstance = cm.instance WHERE gi.gradetype = 1 AND gi.hidden != 1 AND gi.courseid = c.id AND cm.course = c.id AND cm.section = cs.id ) AS 'activities'
FROM
prefix_course AS c
- get grade sections
LEFT JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.section > 0 AND cs.section <=14
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
- LEFT JOIN prefix_assign AS asg ON asg.id = cm.instance
JOIN prefix_grade_items AS gi ON gi.iteminstance = cm.instance AND gi.gradetype = 1 AND gi.hidden != 1 AND gi.courseid = c.id AND cm.course = c.id AND cm.section = cs.id
WHERE
c.id = %%COURSEID%%
AND cs.visible = 1
ORDER BY gi.itemmodule, cs.section
評定とコースの完了レポート
すべてのアイテムを含むサイト全体の評定レポート
各学生のコース合計とともに、すべてのコース項目の評定を表示します。 アドホックレポートまたは構成可能なレポートで機能します
SELECT u.firstname AS 'First' , u.lastname AS 'Last',
u.firstname + ' ' + u.lastname AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',
CASE
WHEN gi.itemtype = 'course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category
WHERE gi.courseid = c.id
ORDER BY lastname
For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD. Replace the line:
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
with:
FROM_UNIXTIME(gg.timemodified) AS Time
そして:
u.firstname + ' ' + u.lastname AS 'Display Name',
と:
CONCAT(u.firstname,' ',u.lastname) AS 'Display Name',
コース合計だけのサイト全体の評定レポート
コースの合計のみを表示する、すべての学生向けの2番目のサイト全体の評定レポート。 アドホックレポートまたは構成可能なレポートで機能します
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
cc.name AS 'Category',
CASE
WHEN gi.itemtype = 'course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemtype = 'course'
ORDER BY lastname
MySQLユーザの場合:
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) AS TIME
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id AND gi.itemtype = 'course'
ORDER BY lastname
評定のある学習者による学習者レポート
どのコースのどの学習者とどの評定ですか
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS Max, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) as Percentage,
if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as Pass
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE gi.courseid = c.id and gi.itemname != 'Attendance'
ORDER BY `Name` ASC
コースの完了ユーザ
寄稿者：Randy Thornton
ユーザ名ごとのコース完了ステータスのリストを含む非常に単純なレポート。 完了は日付で示され、それ以外の場合は空白になります。
SELECT
u.username,
c.shortname,
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%Y-%m-%d') AS completed
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
WHERE c.enablecompletion = 1
ORDER BY u.username
クライテリアを使用したコースの完了ユーザ
寄稿者: Randy Thornton
ユーザ名別のコース完了レポート、集計方法、クライテリアタイプ、および利用可能な場合はクライテリアの詳細。
SELECT u.username AS user,
c.shortname AS course,
DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
CASE
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a WHERE (a.course = c.id AND a.criteriatype IS NULL) = 1) THEN "Any"
ELSE "All"
END AS aggregation,
CASE
WHEN p.criteriatype = 1 THEN "Self"
WHEN p.criteriatype = 2 THEN "By Date"
WHEN p.criteriatype = 3 THEN "Unenrol Status"
WHEN p.criteriatype = 4 THEN "Activity"
WHEN p.criteriatype = 5 THEN "Duration"
WHEN p.criteriatype = 6 THEN "Course Grade"
WHEN p.criteriatype = 7 THEN "Approve by Role"
WHEN p.criteriatype = 8 THEN "Previous Course"
END AS criteriatype,
CASE
WHEN p.criteriatype = 1 THEN "*"
WHEN p.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(p.timeend),'%Y-%m-%d')
WHEN p.criteriatype = 3 THEN t.unenroled
WHEN p.criteriatype = 4 THEN
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',p.module,'/view.php?id=',p.moduleinstance,'">',p.module,'</a>')
WHEN p.criteriatype = 5 THEN p.enrolperiod
WHEN p.criteriatype = 6 THEN CONCAT('Needed: ',ROUND(p.gradepass,2),' Achieved: ',ROUND(t.gradefinal,2))
WHEN p.criteriatype = 7 THEN p.role
WHEN p.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = p.courseinstance)
END AS criteriadetail
FROM prefix_course_completion_crit_compl AS t
JOIN prefix_user AS u ON t.userid = u.id
JOIN prefix_course AS c ON t.course = c.id
JOIN prefix_course_completion_criteria AS p ON t.criteriaid = p.id
完了が有効になっているコースとその設定
寄稿者: Randy Thornton
完了が有効になっているすべてのコースと、それらの集計設定、クライテリアのタイプ、およびクライテリアの詳細のリスト。
SELECT c.shortname AS Course,
CASE
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a WHERE (a.course = t.course AND a.criteriatype IS NULL)) = 2 THEN "All"
ELSE "Any"
END AS Course_Aggregation,
CASE
WHEN t.criteriatype = 1 THEN "Self completion"
WHEN t.criteriatype = 2 THEN "Date done by"
WHEN t.criteriatype = 3 THEN "Unenrolement"
WHEN t.criteriatype = 4 THEN "Activity completion"
WHEN t.criteriatype = 5 THEN "Duration in days"
WHEN t.criteriatype = 6 THEN "Final grade"
WHEN t.criteriatype = 7 THEN "Approve by role"
WHEN t.criteriatype = 8 THEN "Previous course"
END AS Criteria_type,
CASE
WHEN t.criteriatype = 1 THEN "On"
WHEN t.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(t.timeend),'%Y-%m-%d')
WHEN t.criteriatype = 3 THEN "On"
WHEN t.criteriatype = 4 THEN
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',t.module,'/view.php?id=',t.moduleinstance,'">',t.module,'</a>')
WHEN t.criteriatype = 5 THEN ROUND(t.enrolperiod/86400)
WHEN t.criteriatype = 6 THEN ROUND(t.gradepass,2)
WHEN t.criteriatype = 7 THEN (SELECT r.shortname FROM prefix_role AS r WHERE r.id = t.role)
WHEN t.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = t.courseinstance)
END AS Criteria_detail
FROM prefix_course_completion_criteria as t
JOIN prefix_course AS c ON t.course = c.id
WHERE c.enablecompletion = 1
ORDER BY course
カスタム日付のコース完了レポート
ユーザが選択した開始日から終了日までに複数または単一のコースを完了したユーザのリスト。 出力には、ユーザ名、名前、コース名、完了日、評点が表示されます
SELECT u.username AS 'User Name',
CONCAT(u.firstname , ' ' , u.lastname) AS 'Name',
c.shortname AS 'Course Name',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%W %e %M, %Y') AS 'Completed Date',
ROUND(c4.gradefinal,2) AS 'Score'
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_course_completion_crit_compl AS c4 ON u.id = c4.userid
WHERE c.enablecompletion = 1 AND (p.timecompleted IS NOT NULL OR p.timecompleted !=)
AND (p.timecompleted>= :start_date AND p.timecompleted<=:end_date)
GROUP BY u.username
ORDER BY c.shortname
活動で使用されるスケール
SELECT scale.name
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',gi.itemmodule,'/view.php?id=',cm.id,'">',gi.itemname,'</a>') AS "Module View"
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/modedit.php?up','date=',cm.id,'">',gi.itemname,'</a>') AS "Module Settings"
FROM prefix_grade_items AS gi
JOIN prefix_course AS c ON c.id = gi.courseid
JOIN prefix_course_modules AS cm ON cm.course = gi.courseid AND cm.instance = gi.iteminstance
JOIN prefix_scale AS scale ON scale.id = gi.scaleid
WHERE gi.scaleid IS NOT NULL
名前のみによる追加点項目
Eric Stromによる寄稿
このクエリは、アイテムの名前に "追加点" が含まれているが、評定設定で追加点として設定されている、学生が登録している表示可能なコースの評定項目を識別します。 定義されたコース開始日、学生とインストラクタの数、およびインストラクタのクリック可能な電子メールリンク（複数の場合は最初に見つかったレコード）が含まれます。
SELECT DATE(FROM_UNIXTIME(c.startdate)) AS StartDate,
concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php',CHAR(63),'id=',
c.id,'">',c.idnumber,'</a>') AS Course_ID, gi.itemname AS Item_Name
,(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
,(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Instructors
,(SELECT DISTINCT concat('<a href="mailto:',u.email,'">',u.email,'</a>')
FROM prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS 'Instructor_Email'
,now() AS Report_Timestamp
FROM prefix_grade_items AS gi
JOIN prefix_course AS c ON gi.courseid = c.id
WHERE gi.itemname LIKE '%extra credit%'
AND gi.gradetype = '1'
AND gi.hidden = '0'
AND gi.aggregationcoef = '0'
AND c.visible = 1
AND (SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) > 0
GROUP BY Course_ID, gi.id
ORDER BY StartDate, Course_ID
%%FILTER_SEARCHTEXT:Course_ID:~%%
ユーザが完了したサイト全体のコース数
Ken St. Johnによる寄稿
サイト全体のすべてのユーザの完了したコースの数を示す簡単なレポート
SELECT u.lastname, u.firstname,
COUNT(p.timecompleted) AS TotalCompletions
FROM prefix_course_completions AS p
JOIN prefix_user AS u ON p.userid = u.id
GROUP BY p.userid
ORDER BY u.lastname
活動モジュールレポート
日付付きの活動完了ユーザ
寄稿者: Randy Thornton
このレポートには、すべてのコースにわたる活動のユーザの完了ステータスが表示されます。 これは、ユーザ、開始時間、および終了時間の構成可能レポートフィルタで使用し、モジュール名を検索できるようにすることを目的としています。
注: モジュール番号を含むCASEステートメントは、サイトが作成されたとき、またはモジュールがサイトに追加されたときにモジュールに付けられた番号に応じて、システムによって異なる場合があります。 これらは一般的なデフォルト番号ですが、course_modulesテーブルでそれらのID番号を確認し、必要に応じて調整する必要があります。 必要に応じて、他のサードパーティプラグインを追加することもできます。
SELECT
u.username As 'User',
c.shortname AS 'Course',
m.name AS Activitytype,
CASE
WHEN cm.module = 1 THEN (SELECT a1.name FROM prefix_assign a1 WHERE a1.id = cm.instance)
WHEN cm.module = 2 THEN (SELECT a2.name FROM prefix_assignment a2 WHERE a2.id = cm.instance)
WHEN cm.module = 3 THEN (SELECT a3.name FROM prefix_book a3 WHERE a3.id = cm.instance)
WHEN cm.module = 4 THEN (SELECT a4.name FROM prefix_chat a4 WHERE a4.id = cm.instance)
WHEN cm.module = 5 THEN (SELECT a5.name FROM prefix_choice a5 WHERE a5.id = cm.instance)
WHEN cm.module = 6 THEN (SELECT a6.name FROM prefix_data a6 WHERE a6.id = cm.instance)
WHEN cm.module = 7 THEN (SELECT a7.name FROM prefix_feedback a7 WHERE a7.id = cm.instance)
WHEN cm.module = 8 THEN (SELECT a8.name FROM prefix_folder a8 WHERE a8.id = cm.instance)
WHEN cm.module = 9 THEN (SELECT a9.name FROM prefix_forum a9 WHERE a9.id = cm.instance)
WHEN cm.module = 10 THEN (SELECT a10.name FROM prefix_glossary a10 WHERE a10.id = cm.instance)
WHEN cm.module = 11 THEN (SELECT a11.name FROM prefix_imscp a11 WHERE a11.id = cm.instance)
WHEN cm.module = 12 THEN (SELECT a12.name FROM prefix_label a12 WHERE a12.id = cm.instance)
WHEN cm.module = 13 THEN (SELECT a13.name FROM prefix_lesson a13 WHERE a13.id = cm.instance)
WHEN cm.module = 14 THEN (SELECT a14.name FROM prefix_lti a14 WHERE a14.id = cm.instance)
WHEN cm.module = 15 THEN (SELECT a15.name FROM prefix_page a15 WHERE a15.id = cm.instance)
WHEN cm.module = 16 THEN (SELECT a16.name FROM prefix_quiz a16 WHERE a16.id = cm.instance)
WHEN cm.module = 17 THEN (SELECT a17.name FROM prefix_resource a17 WHERE a17.id = cm.instance)
WHEN cm.module = 18 THEN (SELECT a18.name FROM prefix_scorm a18 WHERE a18.id = cm.instance)
WHEN cm.module = 19 THEN (SELECT a19.name FROM prefix_survey a19 WHERE a19.id = cm.instance)
WHEN cm.module = 20 THEN (SELECT a20.name FROM prefix_url a20 WHERE a20.id = cm.instance)
WHEN cm.module = 21 THEN (SELECT a21.name FROM prefix_wiki a21 WHERE a21.id = cm.instance)
WHEN cm.module = 22 THEN (SELECT a22.name FROM prefix_workshop a22 WHERE a22.id = cm.instance)
END AS Actvityname,
- cm.section AS Coursesection,
CASE
WHEN cm.completion = 0 THEN '0 None'
WHEN cm.completion = 1 THEN '1 Self'
WHEN cm.completion = 2 THEN '2 Auto'
END AS Activtycompletiontype,
CASE
WHEN cmc.completionstate = 0 THEN 'In Progress'
WHEN cmc.completionstate = 1 THEN 'Completed'
WHEN cmc.completionstate = 2 THEN 'Completed with Pass'
WHEN cmc.completionstate = 3 THEN 'Completed with Fail'
ELSE 'Unknown'
END AS 'Progress',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When'
FROM prefix_course_modules_completion cmc
JOIN prefix_user u ON cmc.userid = u.id
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN prefix_course c ON cm.course = c.id
JOIN prefix_modules m ON cm.module = m.id
- skip the predefined admin and guest user
WHERE u.id > 2
- config reports filters
%%FILTER_USERS:u.username%%
%%FILTER_SEARCHTEXT:m.name:~%%
%%FILTER_STARTTIME:cmc.timemodified:>%% %%FILTER_ENDTIME:cmc.timemodified:<%%
ORDER BY u.username
各コースで使用されるSCORM活動の数
SELECT cm.course,c.fullname ,m.name
,concat('<a target="_new" href="%%WWWROOT%%/mod/scorm/index.php?id=',c.id,'">',count(cm.id),'</a>') AS Counter
FROM `prefix_course_modules` as cm
JOIN prefix_modules as m ON cm.module=m.id
JOIN prefix_course as c ON cm.course = c.id
WHERE m.name LIKE '%scorm%'
GROUP BY cm.course,cm.module
ORDER BY count(cm.id) desc
コース開始日ごとのSCORM使用量
Granite State CollegeのElizabeth Daltonによる寄稿
コース開始日でフィルタリングされた、コースに含まれるSCORM活動のレポート数。
SELECT
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'course'
, cc.name AS 'Category'
, scm.name AS 'Sample Activity Name'
, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
, COUNT(DISTINCT cm.id) AS 'Resources Used'
- , FROM_UNIXTIME(cm.added) AS 'resource added'
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id AND m.name LIKE 'SCO%'
JOIN prefix_course AS c ON c.id = cm.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
JOIN prefix_scorm AS scm ON scm.id = cm.instance
WHERE
1
%%FILTER_STARTTIME:c.startdate:>%%
%%FILTER_ENDTIME:c.startdate:<%%
GROUP BY c.shortname, m.name
ORDER BY c.startdate, c.shortname
LTI（外部ツール）コース開始日別の使用量
Granite State CollegeのElizabeth Daltonによる寄稿
コースの開始日でフィルタリングされた、コース内のLTI（外部ツール）使用活動の包含数のレポート。
SELECT
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'course'
, cc.name AS 'Category'
, lti.name AS 'Sample Activity Name'
, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
, COUNT(DISTINCT cm.id) AS 'Resources Used'
- , FROM_UNIXTIME(cm.added) AS 'resource added'
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id AND m.name LIKE 'lti'
JOIN prefix_course AS c ON c.id = cm.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
JOIN prefix_lti AS lti ON lti.id = cm.instance
WHERE
1
%%FILTER_STARTTIME:c.startdate:>%%
%%FILTER_ENDTIME:c.startdate:<%%
GROUP BY c.shortname, m.name
ORDER BY c.startdate, c.shortname
各モジュールの詳細なアクション
SELECT module,action,count(id) as counter
FROM prefix_log
GROUP BY module,action
ORDER BY module,counter desc
最も人気のある活動
SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC
システム全体での活動とリソースの使用
SELECT count( cm.id ) AS counter, m.name
FROM `prefix_course_modules` AS cm
JOIN prefix_modules AS m ON cm.module = m.id
GROUP BY cm.module
ORDER BY counter DESC
コースごとのモジュールごとのログファイルアクション（ID）
select course,module,action,count(action) as summa from prefix_log
where action <> 'new'
group by course,action,module
order by course,module,action
さまざまなコース活動のシステム全体の使用数
（Moodle 2.xでテストされ、正常に動作します） いいね：フォーラム、Wiki、ブログ、課題、データベース、
- 特定のカテゴリ内
- コースの教師の名前
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
FROM prefix_course AS c
WHERE c.category IN ( 18)
ORDER BY Wikis DESC,Blogs DESC, Forums DESC
過去6学期のコースWikiの使用法/活動
SELECT "Courses with Wikis"
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester A%') AS '2010
Semester A'
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester B%') AS '2010
Semester B'
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעא
סמסטר א'
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעא
סמסטר ב'
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','תשעב','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעב
סמסטר א'
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','תשעב','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעב
סמסטר ב'
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','תשעג','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעג
סמסטר א'
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course AS c ON c.id = cm.course
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
and c.fullname LIKE CONCAT('%','תשעג','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעג
סמסטר ב'
詳細なWIKI活動（コースごとのWikiごと）
コースの学生数を含む（参考）
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
,(SELECT Count( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students
,m.name
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%updat%' ) as 'UPDAT E'
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%annotate%' ) as ANNOTATE
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%comment%' ) as COMMENT
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%add%' ) as 'A DD'
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%edit%' ) as EDIT
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action NOT LIKE '%view%' ) as 'All (NO View)'
FROM `prefix_course_modules` as cm
JOIN prefix_modules as m ON cm.module=m.id
JOIN prefix_course as c ON cm.course = c.id
WHERE m.name LIKE '%wiki%'
GROUP BY cm.course,cm.module
ORDER BY 'All (NO View)' DESC
システム全体のWikiの使用法
（特定のコースカテゴリを選択することで、出力をフィルタリングできます: "WHERE c.category IN ( 8,13,15)"）
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%') AS 'WikiActivity
ALL'
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%add%' ) AS 'WikiActivity
ADD'
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%edit%' ) AS 'WikiActivity
EDIT'
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%annotate%' ) AS 'WikiActivity
ANNOTATE'
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%comments%' ) AS 'WikiActivity
Comments'
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
,(SELECT count(*) FROM prefix_ouwiki_pages as ouwp
JOIN prefix_ouwiki as ouw ON ouw.id = ouwp.subwikiid
WHERE ouw.course = c.id GROUP BY ouw.course ) as OUWikiPages
,(SELECT count( DISTINCT nwp.pagename ) FROM prefix_wiki_pages AS nwp
JOIN prefix_wiki AS nw ON nw.id = nwp.dfwiki WHERE nw.course = c.id ) As NWikiPages
FROM prefix_course AS c
WHERE c.category IN ( 8,13,15)
HAVING Wikis > 0
ORDER BY 'WikiActivity
ALL' DESC
"WEB2" モジュールによる教師の活動の集約
（Moodle 2.xでテストされ、正常に動作します）
NV列には、VIEW活動ログのない活動が表示されます
SELECT ra.userid, u.firstname,u.lastname
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%') AS Wiki
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%' AND l.action NOT LIKE '%view%') AS Wiki_NV
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%') AS Forum
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%' AND l.action NOT LIKE '%view%') AS Forum_NV
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%') AS Blog
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%' AND l.action NOT LIKE '%view%') AS Blog_NV
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%') AS Assignment
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%' AND l.action NOT LIKE '%view%') AS Assignment_NV
FROM prefix_role_assignments AS ra
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3
GROUP BY ra.userid
発行されたすべての証明書を一覧表示し、カスタムプロファイルフィールドの変数で並べ替えます
注: SQLクエリは最初は威圧的に見えますが、習得するのはそれほど難しくありません。フォーラムで、ユーザーが 'サイト全体' のグループを1.9倍で実行したいと思っているのを見てきました。これは一種のアイデアです。カスタムプロファイルフィールド（私の場合はユニットまたは部門（つまり、サイト全体のグループ））でソートされたすべてのユーザに発行されたすべての証明書をプルします。なぜ証明書なのか？評定と小テストの両方で調べましたが、コース管理者は実際の評定にはあまり関心がありませんが、学習者が証明書を受け取ったかどうか（つまり、x、y、z活動でコースに合格したかどうか）に関心があります。また、グループを作成して適切なグループに割り当てる手間も省けます。コースごとに25以上のグループがあり、常に新しい学習者がコースに登録しているため、一括で割り当てることも効率的ではありません。制限はサーバと関係がありますか？ 5000行のデータしかプルしないためです。誰かがこれを変更する方法を見つけたら、私に知らせてください。それまでの間、回避策は、行数を制限するために一度に数ユニット/部門のみをプルすることです。各コース管理者は特定のユニット/部門にのみ責任があるため、現時点ではこれで問題ありません。
SELECT
DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y-%m-%d' ) AS Date,
prefix_certificate_issues.classname AS Topic,
prefix_certificate.name AS Certificate,
prefix_certificate_issues.studentname as Name,
prefix_user_info_data.data AS Units
FROM
prefix_certificate_issues
INNER JOIN prefix_user_info_data
on prefix_certificate_issues.userid = prefix_user_info_data.userid
INNER JOIN prefix_certificate
on prefix_certificate_issues.certificateid = prefix_certificate.id
WHERE prefix_user_info_data.data='Unit 1'
OR prefix_user_info_data.data='Unit 2'
OR prefix_user_info_data.data='Unit 3'
ORDER BY Units, Name, Topic ASC
サイトで獲得したすべての単純な証明書
寄稿者: Randy Thornton
サイト全体でSimple Certificateプラグインモジュールを使用して取得したすべての証明書の基本レポート。最新のものから順に並べ替えられます。 （注: これはMySQL DATE_FORMAT関数を使用します。）
SELECT
CONCAT (u.firstname, ' ',u.lastname) As 'User',
c.fullname AS 'Course',
sc.name AS 'Certificate',
DATE_FORMAT( FROM_UNIXTIME(sci.timecreated), '%Y-%m-%d' ) As 'Date Awarded'
- sci.code 'CertificateId'
FROM prefix_simplecertificate_issues sci
JOIN prefix_user u ON sci.userid = u.id
JOIN prefix_simplecertificate sc ON sci.certificateid = sc.id
JOIN prefix_course AS c ON sc.course = c.id
ORDER BY sci.timecreated DESC
これを最新のものに制限したい場合は、条件を追加して、過去の特定の日数に制限することができます。 たとえば、このWHERE句（ORDER BYの上）を追加すると、過去30日間に獲得したものだけが表示されます。
WHERE DATEDIFF(NOW(),FROM_UNIXTIME(sci.timecreated) ) < 30
コースでのカウンターブログの使用、システム全体
どのコースのどの教師がブログを使用し、そのコースで何人の学生がカウントされるか。
SELECT ( @counter := @counter+1) as counter,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
FROM prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
FROM prefix_course AS c, (SELECT @counter := 0) as s_init
WHERE c.category IN ( 8,13,15)
HAVING Blogs > 0
ORDER BY Blogs DESC
Elluminate（Blackboard Collaborate）- システム全体での使用
SELECT e.name As Session ,er.recordingsize
,c.fullname As Course
,u.firstname,u.lastname
,DATE_FORMAT(FROM_UNIXTIME(e.timestart),'%d-%m-%Y') AS dTimeStart
,concat('<a target="_new" href="%%WWWROOT%%/moodle/mod/elluminate/loadrecording.php?id=',er.id,'">Show</a>') AS RecordedSession
FROM prefix_elluminate_recordings AS er
JOIN prefix_elluminate AS e ON e.meetingid = er.meetingid
JOIN prefix_course as c ON c.id = e.course
JOIN prefix_user AS u ON u.id = e.creator
ORDER BY er.recordingsize DESC
投票
寄稿者: Randy Thornton
投票活動の結果。 すべてのコースについて、コースの短縮名、ユーザ名、選択テキスト、およびユーザーが投票した回答を表示します。
SELECT c.shortname AS course, u.username, h.name as question, o.text AS answer
FROM prefix_choice AS h
JOIN prefix_course AS c ON h.course = c.id
JOIN prefix_choice_answers AS a ON h.id = a.choiceid
JOIN prefix_user AS u ON a.userid = u.id
JOIN prefix_choice_options AS o ON a.optionid = o.id
コースでの課題タイプの使用法
SELECT
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/index.php?id=',c.id,'">',c.fullname,'</a>') AS "List assignments"
,(SELECT COUNT(*) FROM prefix_assign WHERE c.id = course) AS Assignments
,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
- GROUP BY apc.plugin
) AS "File Assignments"
,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'onlinetext' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "Online Assignments"
,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'pdf' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "PDF Assignments"
,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'offline' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "Offline Assignments"
,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'comments' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "Assignments Comments"
FROM prefix_assign AS assign
JOIN prefix_course AS c ON c.id = assign.course
GROUP BY c.id
課題モジュールレポート
すべての評定されていない課題
注: このクエリは、Moodle 2.2の非推奨の古い課題モジュールに対するものであり、新しい課題モジュールに対するものではありません。 作成者である場合は、このクエリを更新してください。更新しない場合、リリース2.7以降、2.2課題モジュールはサポートされなくなったため削除されます。 参照: [1]
まだ評定が必要な提出済みの課題をすべて返します
select
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment"
from prefix_assignment_submissions as asb
join prefix_assignment as a ON a.id = asb.assignment
join prefix_user as u ON u.id = asb.userid
join prefix_course as c ON c.id = a.course
join prefix_course_modules as cm ON c.id = cm.course
where asb.grade < 0 and cm.instance = a.id
and cm.module = 1
order by c.fullname, a.name, u.lastname
リンク付きのすべての未評定の課題
注：このクエリは、Moodle 2.2の非推奨の古い課題モジュールに対するものであり、新しい課題モジュールに対するものではありません。 作成者である場合は、このクエリを更新してください。更新しない場合、リリース2.7以降、2.2課題モジュールはサポートされなくなったため削除されます。 参照: [2]
まだ評定が必要な提出済みのすべての課題と、提出物に直接アクセスして評定するためのリンクを返します。 Moodle内でレポートを表示すると、リンクが機能します。
select
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment",
'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) +
+ 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar)
+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
AS "Assignmentlink"
from prefix_assignment_submissions as asb
join prefix_assignment as a ON a.id = asb.assignment
join prefix_user as u ON u.id = asb.userid
join prefix_course as c ON c.id = a.course
join prefix_course_modules as cm ON c.id = cm.course
where asb.grade < 0 and cm.instance = a.id and cm.module = 1
order by c.fullname, a.name, u.lastname
評定待ちの課題（および小テスト）
注: このクエリは、Moodle 2.2の非推奨の古い課題モジュールに対するものであり、新しい課題モジュールに対するものではありません。 作成者である場合は、このクエリを更新してください。更新しない場合、リリース2.7以降、2.2課題モジュールはサポートされなくなったため削除されます。 参照: [3]
このレポートでは、YEARフィルタを追加する必要があります（最新のblock / configureurable_reportsを使用する場合に使用可能）
このクエリを以前のバージョンで機能させるために、いつでも削除できます。
レポートには次のものが含まれます。
- 小テストの数
- 未完了の小テストの受験
- 終了した小テストの受験
- 学生達の人数
- 課題の数
- 学生が提出した回答の数
- コース内のチェックされていない課題（教師を待っている）の数。
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes'
,(SELECT COUNT(*)
FROM prefix_course_modules cm
JOIN prefix_modules as m ON m.id = cm.module
WHERE m.name LIKE 'quiz' AND cm.course = c.id
GROUP BY cm.course
) AS 'nQuizzes'
,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish = 0
GROUP BY q.course) AS 'unFinished Quiz attempts'
,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish > 0
GROUP BY q.course) AS 'finished quiz attempts'
,(SELECT Count( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5
AND ctx.instanceid = c.id
) AS nStudents
,(
SELECT count(a.id)
FROM prefix_assignment AS a
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) nAssignments
,(
SELECT count(*)
FROM prefix_assignment AS a
WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) 'Open
Assignments'
, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished
Assignments
(percent)'
,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'unChecked
Submissions'
,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'Submitted
Assignments'
FROM prefix_course AS c
LEFT JOIN (
SELECT course, count(*) AS iAssignments
FROM prefix_assignment AS a
GROUP BY a.course
) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id
LEFT JOIN (
SELECT course, count(*) AS iOpenAssignments
FROM prefix_assignment AS a
WHERE FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id
WHERE 1=1
- AND c.fullname LIKE '%תשעג%'
%%FILTER_YEARS:c.fullname%%
- You can enable the SEMESTER filter as well,
- by uncommenting the following line:
- %%FILTER_SEMESTERS:c.fullname%%
ORDER BY 'Open
Assignments' DESC
クライテリアにゼロ値がないルーブリック
Eric Stromによる寄稿
Moodleのルーブリック計算は、評価で使用される各クライテリアの値がゼロでない場合、インストラクタの期待と一致しない可能性があります。 https://docs.moodle.org/32/en/Rubrics#Grade_calculation のドキュメントから:
"たとえば、前の例の教師が両方のレベルを1ポイントで選択した場合、単純な合計は2ポイントになります。しかし、それは実際には可能な限り低いスコアであるため、Moodleの評定0にマッピングされます。 ヒント：この種の混乱を避けるために、すべてのルーブリッククライテリアに0ポイントのレベルを含めることをお勧めします。"
このレポートは、ゼロ値レベルのないクライテリアを持つルーブリックと、それらが住んでいるコースを識別します。これは、コースの学生に表示されるアクティブなルーブリックを持つ課題のみに絞り込まれます。各ルーブリックIDへのリンクは、ルーブリックを編集するための直接リンクです。欠落しているクライテリアごとにゼロレベルを追加して修正します。一般的に、結果として生じる評定の変更は、学生に有利になります。
もちろんidnumberの検索フィルタが含まれています。
SELECT cat.name AS Department, concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',
c.id,'">',c.idnumber,'</a>') AS Course_ID,
c.fullname AS Course_Name,
concat('<a target="_new" href="%%WWWROOT%%/grade/grading/form/rubric/edit.php',CHAR(63),'areaid=',gd.areaid,'">',gd.areaid,'</a>') AS Rubric
FROM prefix_course AS c
JOIN prefix_course_categories AS cat
ON cat.id = c.category
JOIN prefix_course_modules AS cm
ON c.id=cm.course
JOIN prefix_context AS ctx
ON cm.id = ctx.instanceid
JOIN prefix_grading_areas AS garea
ON ctx.id = garea.contextid
JOIN prefix_grading_definitions AS gd
ON garea.id = gd.areaid
JOIN prefix_gradingform_rubric_criteria AS crit
ON gd.id = crit.definitionid
JOIN prefix_gradingform_rubric_levels AS levels
ON levels.criterionid = crit.id
WHERE cm.visible='1' AND garea.activemethod = 'rubric' AND (crit.id NOT IN
(SELECT crit.id
FROM prefix_gradingform_rubric_criteria AS crit
JOIN prefix_gradingform_rubric_levels AS levels
ON levels.criterionid = crit.id WHERE levels.score = '0'))
GROUP BY Rubric
ORDER BY Course_ID, Rubric
%%FILTER_SEARCHTEXT:c.idnumber:~%%
"単一ファイルアップロード" 課題を使用しているのは誰か
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,ass.name as "Assignment Name"
FROM
prefix_assignment as ass
JOIN
prefix_course as c ON c.id = ass.course
WHERE `assignmenttype` LIKE 'uploadsingle'
フィードバックモジュールレポート
現在のユーザによって送信された現在のコース内のすべてのフィードバック活動への回答を一覧表示します
SELECT /* crs.fullname as "Course name", f.name AS "Journal name", CONCAT(u.firstname,' ',UPPER(u.lastname)) as "Participant", */ /* include these fields if you want to check the composition of the recordset */
DATE_FORMAT(FROM_UNIXTIME(c.timemodified),'%W %e %M, %Y') as "Answer Date",
CASE i.typ WHEN 'label' THEN i.presentation ELSE i.name END as "Topic", /* usually labels are used as section titles, so you'd want them present in the recordset */
v.value as "My Answer"
FROM prefix_feedback AS f
INNER JOIN prefix_course as crs on crs.id=f.course %%FILTER_COURSES:f.course%%
INNER JOIN prefix_feedback_item AS i ON f.id=i.feedback
INNER JOIN prefix_feedback_completed AS c on f.id=c.feedback %%FILTER_COURSEUSER:c.userid%%
LEFT JOIN prefix_feedback_value AS v on v.completed=c.id AND v.item=i.id
INNER JOIN prefix_user AS u on c.userid=u.id
WHERE c.id = %%COURSEID%% AND u.id = %%USERID%% AND c.anonymous_response = 1 /* This clause limits the recordset to the current course and the current user and includes/ excludes the anonymous responses as needed */
ORDER BY f.id, c.timemodified, i.id
匿名ユーザの名前の表示を含む、すべてのユーザのすべてのコースからのすべてのフィードバックを表示する
寄稿者: Randy Thornton
匿名ユーザのユーザ名の表示を含む、すべてのユーザのすべての多肢選択問題の回答とともに、すべてのコースのすべてのフィードバックを表示します。 また、フロントページに 'ログインしていない' ユーザとして匿名ユーザを表示します。 これは大まかなレポートであり、きれいなレポートではなく、多肢選択問題に限定されていますが、回答番号と可能な回答のリストが生の形式で表示されます。 さらなるレポートの基礎として、また必要に応じて匿名ユーザのIDを取得するために、ここに投稿します。
SELECT
c.shortname AS Course,
f.name AS Feedback,
- i.id AS Itemid,
i.name AS Itemname,
i.label AS Itemlabel,
CASE
WHEN f.anonymous = 1 AND u.id != 0 THEN CONCAT(u.username, ' :ANON')
WHEN fc.userid = 0 THEN 'Not-logged-in'
ELSE u.username
END AS 'User',
DATE_FORMAT(FROM_UNIXTIME(fc.timemodified),'%Y-%m-%d %H:%i') AS "Completed",
v.value AS "Choice",
CASE
WHEN i.typ = 'multichoice' THEN
IF ( SUBSTRING(i.presentation,1,6)='d>>>>>',
SUBSTRING(i.presentation,7),
i.presentation)
ELSE i.presentation
END AS "Answers",
i.typ,
i.dependitem,
i.dependvalue
FROM prefix_feedback f
JOIN prefix_course c ON c.id=f.course
JOIN prefix_feedback_item AS i ON f.id=i.feedback
JOIN prefix_feedback_completed fc ON f.id=fc.feedback
LEFT JOIN prefix_feedback_value v ON v.completed=fc.id AND v.item=i.id
LEFT JOIN prefix_user AS u ON fc.userid=u.id
WHERE i.typ != 'pagebreak'
リソースモジュールレポート
"最近アップロードされたファイル" のリスト
ユーザがアップロードしているものを確認する
SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info
FROM `prefix_log`
WHERE `action` LIKE 'upload'
ORDER BY `prefix_log`.`time` DESC
特定のファイルをロードしたコースを一覧表示する: "X"
教師は（おそらく）コースのシラバスをアップロードしましたか？
SELECT c.id, c.fullname FROM `prefix_log` as l
JOIN prefix_course as c ON c.id = l.course
WHERE `action` LIKE '%upload%' AND ( info LIKE '%Syllabus%' OR info LIKE '%Sylabus%' ) GROUP BY c.id
特定の外部ウェブサイトにリンクするすべてのリソース
+コースへのリンク
+教師は誰か
+外部リソースへのリンク
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,c.shortname,r.name
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
FROM prefix_resource AS r
JOIN prefix_course AS c ON r.course = c.id
WHERE r.reference LIKE 'http://info.oranim.ac.il/home%'
"Webページの作成" リソース数
SELECT course,prefix_course.fullname, COUNT(*) AS Total
FROM `prefix_resource`
JOIN `prefix_course` ON prefix_course.id = prefix_resource.course
WHERE type='html'
GROUP BY course
コースのリソース数
+（最初の）教師名
+コースが特定のカテゴリ内にある場合
SELECT
COUNT(*) AS count
,r.course
,c.shortname shortname
,c.fullname coursename
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
FROM prefix_role_assignments AS ra
JOIN prefix_user as u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
FROM prefix_resource r
JOIN prefix_course c ON r.course = c.id
WHERE c.category IN (10,13,28,18,26)
GROUP BY r.course
ORDER BY COUNT(*) DESC
すべての自動バックアップファイルを削除します
ファイルシステム上のすべての自動バックアップファイルを削除するbashcliスクリプトを準備します。 （ディスクスペースをクリーンアップします）
SELECT CONCAT( 'rm -f /var/moodledatanew/filedir/', SUBSTRING( contenthash, 1, 2 ) , '/', SUBSTRING( contenthash, 3, 2 ) , '/', contenthash )
FROM `mdl_files`
WHERE `filename` LIKE '%mbz%'
AND filearea = 'automated'
すべての自動バックアップファイルで使用されているディスク容量を確認します。
SELECT SUM(filesize)/(1024*1024*1024) FROM `mdl_files` WHERE `filename` LIKE '%mbz%' AND filearea = 'automated'
フォーラムモジュールレポート
コースフォーラムのすべてのユーザの投稿を印刷
%%COURSEID%% は変数であり、SQLレポートを実行している現在のCourseIDに置き換えられます。 最新のblock/configureurable_reportsを使用している場合！ （いつでも固定コースに変更したり、削除してすべてのコースを表示したりできます。）
SELECT
concat('<a target="_new" href="%%WWWROOT%%/mod/forum/user.php?course=',c.id,'&id=',u.id,'&mode=posts">',CONCAT(u.firstname,' ', u.lastname),'</a>') As Fullname
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum
,count(*) as Posts
,(SELECT count(*) FROM prefix_forum_discussions AS ifd JOIN prefix_forum as iforum ON iforum.id = ifd.forum WHERE ifd.userid = fp.userid AND iforum.id = f.id) AS cAllDiscussion
FROM prefix_forum_posts AS fp
JOIN prefix_user as u ON u.id = fp.userid
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
JOIN prefix_forum AS f ON f.id = fd.forum
JOIN prefix_course as c ON c.id = fd.course
WHERE fd.course = %%COURSEID%%
GROUP BY f.id,u.id
ORDER BY u.id
フォーラムはコースごとのカウントを使用します - ニュースフォーラムは含まれません！
SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum
INNER JOIN prefix_course
ON prefix_course.id = prefix_forum.course
WHERE NOT(prefix_forum.type = 'news')
GROUP BY prefix_forum.course
ORDER BY total desc
フォーラムはタイプ別のコースごとのカウントを使用します - ニュースフォーラムは含まれません！
SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum
INNER JOIN prefix_course
ON prefix_course.id = prefix_forum.course
WHERE NOT(prefix_forum.type = 'news')
GROUP BY prefix_forum.course,prefix_forum.type
ORDER BY total desc
フォーラム活動 - システム全体
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS CourseID
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
FROM prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
,c.fullname as Course
,f.type
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
, fd.forum, f.name,count(*) AS cPostAndDisc
,(SELECT count(*) FROM prefix_forum_discussions AS ifd WHERE ifd.forum = f.id) AS cDiscussion
FROM prefix_forum_posts AS fp
JOIN prefix_forum_discussions AS fd ON fd.id = fp.discussion
JOIN prefix_forum AS f ON f.id = fd.forum
JOIN prefix_course AS c ON c.id = f.course
WHERE f.type != 'news' AND c.fullname LIKE '%2013%'
- WHERE 1=1
- %%FILTER_YEARS:c.fullname%%
- You can enable the SEMESTER filter as well,
- by uncommenting the following line:
- %%FILTER_SEMESTERS:c.fullname%%
GROUP BY fd.forum
ORDER BY count( * ) DESC
フォーラムでの活動
集計することにより、フォーラムで実際にどの程度の活動が行われているかを把握しようとしています。
コース内のユーザ、投稿数、ディスカッション数、ユニークな学生投稿、ユニークな学生ディスカッション、教師数、学生数、ユニークな学生投稿とコース内の学生数の比率...
SELECT c.fullname,f.name,f.type
,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
,(SELECT count(fp.id) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as Posts
,(SELECT count(distinct fp.userid) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as UniqueUsersPosts
,(SELECT Count( ra.userid ) AS Students
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid =5
AND ctx.instanceid = c.id
) AS StudentsCount
,(SELECT Count( ra.userid ) AS Teachers
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid =3
AND ctx.instanceid = c.id
) AS 'Teacher
Count'
,(SELECT Count( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid IN (3,5)
AND ctx.instanceid = c.id
) AS UserCount
, (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage
, (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage
FROM prefix_forum as f
JOIN prefix_course as c ON f.course = c.id
WHERE `type` != 'news'
ORDER BY StudentPostUsage DESC
すべてのフォーラムタイプ: NEWS
SELECT f.id, f.name
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_forum AS f ON cm.instance = f.id
WHERE m.name = 'forum'
AND f.type = 'news'
すべての私のコースからのすべての新しいフォーラムニュースアイテム（ディスカッション）
"userid = 26" と "id = 26" を新しいユーザIDに変更します
SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
FROM prefix_forum_discussions as fd
JOIN prefix_forum as f ON f.id = fd.forum
JOIN prefix_course as c ON c.id = f.course
JOIN prefix_user_lastaccess as ul ON (c.id = ul.courseid AND ul.userid = 26)
WHERE fd.timemodified > ul.timeaccess
AND fd.forum IN (SELECT f.id
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_forum AS f ON cm.instance = f.id
WHERE m.name = 'forum'
AND f.type = 'news')
AND c.id IN (SELECT c.id
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC
ニュースフォーラム-ディスカッションCOUNT
これは実際には...学生が教師からどのくらいの指示を受けるか
SELECT c.shortname ,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
FROM prefix_role_assignments AS ra
JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',count(fd.id),'</a>') AS DiscussionsSum
FROM prefix_forum_discussions AS fd
INNER JOIN prefix_forum AS f ON f.id = fd.forum
INNER JOIN prefix_course AS c ON c.id = f.course
WHERE f.type = 'news' AND c.category IN (10,13,28,18,26)
GROUP BY fd.forum
ORDER BY count(fd.id) DESC
Cantidad de foros que han sido posteados por profesor
（教師/ Google翻訳者によって投稿されたフォーラムの数）
Queriamos sabre cuales son las acciones del profesor dentro de los foros de cada curso、por ello se hizoesteinforme。
（各コースのフォーラムでの教師の行動を知りたかったので、このレポートを作成しました。/Google翻訳者）
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS curso,
CONCAT(u.firstname ,' ',u.lastname) AS Facilitador,
(SELECT COUNT( m.name ) AS COUNT FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS foros,
COUNT(*) AS Posts
FROM prefix_forum_posts AS fp
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
JOIN prefix_forum AS f ON f.id = fd.forum
JOIN prefix_course AS c ON c.id = fd.course
JOIN prefix_user AS u ON u.id = fp.userid
WHERE fp.userid =
(
select distinct prefix_user.id
from prefix_user
join prefix_role_assignments as ra on ra.userid = prefix_user.id
where ra.roleid = 3
and userid = fp.userid
limit 1
)
and c.shortname like '%2014-2-1%'
GROUP BY c.id, u.id
高い評価を得たすべてのフォーラムのすべての投稿を一覧表示します
教師と学生がフォーラムの投稿を "重要、興味深い、価値のある、評価されていない" 尺度で評価できるように尺度を設定します
そして、コース "すべての興味深い投稿へのリンク" の冒頭で、次のレポートへのリンクを追加します
SELECT
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',f.id,'">',f.name,'</a>') AS 'Forum name,
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/discuss.php?d=',fd.id,'#p',fp.id,'">',fp.subject,'</a>') AS 'Post link',
SUM(r.rating) AS 'Rating'
FROM mdl_rating AS r
JOIN mdl_forum_posts AS fp ON fp.id = r.itemid
JOIN mdl_forum_discussions AS fd ON fd.id = fp.discussion
JOIN mdl_forum AS f ON f.id = fd.forum
WHERE r.component = 'mod_forum' AND r.ratingarea = 'post' AND f.course = %%COURSEID%%
GROUP BY r.itemid
ORDER BY SUM(r.rating) DESC
1つのフォーラムのすべてのディスカッションのすべての投稿を一覧表示します
このレポートは、"&filter_var = cmid" を使用してコンテキストモジュールIDをパラメータとしてレポートに渡すことにより、単一のフォーラムのすべての学生の投稿とディスカッションをエクスポートするために使用されます。
SELECT
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=', f.id, '">', f.name, '</a>') AS 'Forum name',
fd.name AS 'Discussion',
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/discuss.php?d=', fd.id, '#p', fp.id, '">', fp.subject, '</a>') AS 'Post (link)',
fp.message
FROM mdl_forum_posts AS fp
JOIN mdl_forum_discussions AS fd ON fd.id = fp.discussion
JOIN mdl_forum AS f ON f.id = fd.forum
JOIN mdl_course_modules AS cm ON cm.module = 9 AND cm.instance = f.id
WHERE cm.id = %%FILTER_VAR%%
ORDER BY f.id, fd.id
小テストモジュールレポート
小テストに "作文問題" があるコースのインストラクタとそのメールアドレスのリストを生成します
SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,
co.fullname AS course_fullname, co.shortname AS course_shortname,
qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
u.firstname, u.lastname, u.email,
FROM prefix_quiz qu, prefix_course co, prefix_role re, prefix_context ct, prefix_role_assignments ra, prefix_user u
WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND
qu.course = co.id AND
co.id = ct.instanceid AND
ra.roleid = re.id AND
re.name = 'Teacher' AND
ra.contextid = ct.id AND
ra.userid = u.id
SELECT Count('x') As NumOfStudents
FROM prefix_role_assignments a
JOIN prefix_user u ON userid = u.id
WHERE roleid = 5 AND contextid = (SELECT id FROM prefix_context WHERE instanceid = 668 AND contextlevel = 50)
コースごとの小テストの数
SELECT count(*)
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">Link</a>') AS Quizes
FROM prefix_course_modules cm
JOIN prefix_course c ON c.id = cm.course
JOIN prefix_modules as m ON m.id = cm.module
WHERE m.name LIKE 'quiz'
GROUP BY c.id
すべての複数回答 (Cloze) 問題を一覧表示
SELECT concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/attempt.php?q=', quiz.id, '">', quiz.name, '</a>') AS Quiz
,question.id question_id, question.questiontext
FROM prefix_question question
JOIN prefix_quiz_question_instances qqi ON question.id = qqi.question
JOIN prefix_quiz quiz ON qqi.quiz = quiz.id
WHERE `qtype` LIKE 'multianswer'
手動評定のコースを一覧表示
これは基本的に、Moodleを使用してMoodleの評定表内でオフラインの評定を保持する教師への指示です。
したがって、評定は管理SISにアップロードできます。 構成可能なレポートで使用します。
SELECT COUNT( * )
,concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php?showadvanced=1&id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_grade_items AS gi
JOIN prefix_course as c ON c.id = gi.courseid
WHERE `itemtype` = 'manual'
GROUP BY courseid
小テストを受けなかったユーザを一覧表示します
"c.id = 14" とq.nameLIKEを変更することを忘れないでください '%小テスト名はここにあります%'
SELECT
user2.id AS ID,
ul.timeaccess,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.username AS IDNumber,
user2.institution AS Institution,
IF (user2.lastaccess = 0,'never',
DATE_FORMAT(FROM_UNIXTIME(user2.lastaccess),'%Y-%m-%d')) AS dLastAccess
,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id AND courseid=c.id) AS CourseLastAccess
,(SELECT r.name
FROM prefix_user_enrolments AS uenrol
JOIN prefix_enrol AS e ON e.id = uenrol.enrolid
JOIN prefix_role AS r ON e.id = r.id
WHERE uenrol.userid=user2.id AND e.courseid = c.id) AS RoleName
FROM prefix_user_enrolments AS ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS user2 ON user2 .id = ue.userid
LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id
WHERE c.id=14 and ue.userid NOT IN (SELECT qa.userid FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON qa.quiz = q.id
JOIN prefix_course AS c ON q.course = c.id
WHERE c.id = 14 AND q.name LIKE '%quiz name goes here%')
各小テストの問題をリストする
SELECT quiz.id,quiz.name, q.id, q.name
FROM mdl_quiz AS quiz
JOIN mdl_question AS q ON FIND_IN_SET(q.id, quiz.questions)
WHERE quiz.course = %%COURSEID%%
ORDER BY quiz.id ASC
注: このクエリはMoodle2.8以降では機能しません。 mdl_quiz.questionsフィールドはありません。 使用法/コンテキストID編成を使用するには、書き直す必要があります。
これがMoodle3.xのバージョンです
SELECT cm.id 'cmid', quiz.id 'quiz id'
,CONCAT('<a target="_new" href="%%WWWROOT%%/mod/quiz/edit.php?cmid=',
cm.id, '">', quiz.name, '</a>') AS 'edit quiz'
,q.id 'qid', q.name 'question name'
FROM mdl_quiz AS quiz
JOIN mdl_course_modules cm ON cm.instance = quiz.id AND cm.module = 33 # 33=quiz mdl_modules
JOIN mdl_quiz_slots qs ON qs.quizid = quiz.id
JOIN mdl_question AS q ON q.id = qs.questionid
WHERE quiz.course = %%COURSEID%%
ORDER BY quiz.id ASC
小テスト活動調査
このレポートは、オンライン学習におけるインストラクショナルデザインの教授法の適応に関する学術研究のための小テストで学生の完全な活動を抽出するために作成されました。 学生は、小テストモジュールを標準の小テストとして使用するのではなく、学生が進捗状況を評価するのに役立つ問題とヒントが埋め込まれた学習小冊子またはミニコースとして使用します（SCORM活動で期待するものと同様）
SELECT
cm.course "course_id", cm.id "moduel_id", q.id "quiz_id", q.name "quiz_name",
CASE q.grademethod
WHEN 1 THEN "GRADEHIGHEST"
WHEN 2 THEN "GRADEAVERAGE"
WHEN 3 THEN "ATTEMPTFIRST"
WHEN 4 THEN "ATTEMPTLAST"
END "grade method"
, q.attempts "quiz_attempts_allowed", cm.groupmode "group_mode"
, qa.id "attempt_id", qa.state "attempt_state", qa.sumgrades "attempt_grade", qg.grade "user_final_grade", q.grade "quiz_max_grade"
,(SELECT GROUP_CONCAT(g.name) FROM mdl_groups AS g
JOIN mdl_groups_members AS m ON g.id = m.groupid WHERE g.courseid = q.course AND m.userid = u.id) "user_groups",
DATE_FORMAT(FROM_UNIXTIME(qa.timestart), '%d-%m-%Y %h:%k') "attempt_start",
DATE_FORMAT(FROM_UNIXTIME(qa.timefinish), '%d-%m-%Y %h:%k') "attempt_finish",
u.id "user_id", u.firstname, u.lastname,
question.id "question_id", question.name "question_name",
qas.state "question_step_state",qas.fraction "question_grade", qh.hint, question.qtype "question_type"
FROM mdl_quiz as q
JOIN mdl_course_modules as cm ON cm.instance = q.id and cm.module = 14
JOIN mdl_quiz_attempts qa ON q.id = qa.quiz
LEFT JOIN mdl_quiz_grades as qg ON qg.quiz = q.id and qg.userid = qa.userid
JOIN mdl_user as u ON u.id = qa.userid
JOIN mdl_question_usages as qu ON qu.id = qa.uniqueid
JOIN mdl_question_attempts as qatt ON qatt.questionusageid = qu.id
JOIN mdl_question as question ON question.id = qatt.questionid
JOIN mdl_question_attempt_steps as qas ON qas.questionattemptid = qatt.id
LEFT JOIN mdl_question_hints as qh ON qh.questionid = q.id
- WHERE q.id = "SOME QUIZ ID"
WHERE cm.course = "SOME COURSE ID"
日付別のコースでの小テストの使用
Granite State CollegeのElizabeth Daltonによる寄稿
このレポートは、2つの値の間にコース開始日がある小テストを含むコースを一覧表示し、各コースの小テストの問題のタイプと、問題のランダム化および回答のランダム化機能が使用されたかどうかの概要を提供します。
"Multiple Choice" には、○/×および組み合わせ問題タイプが含まれます。
"Short Answer" は、1つのフレーズを受け入れる問題です。
"Other" の問題には、固定の数値、計算、作文、およびさまざまなドラッグ＆ドロップタイプが含まれます。
"Min Quiz Age" と"Max Quiz Age" は、コースの開始日と比較した、コース内の小テストの最終変更日に関するデータを提供します。値は日単位で表されます。負の値は、コースの開始後に小テストが編集されたことを示します。 90日を超える値は、小テストが変更なしで以前の用語（コーホート）で使用された可能性があることを示します。
注: 構成可能レポートでは、"適用" ボタンがクリックされるまで日付フィルタは適用されません。
SELECT
c.shortname AS 'Course'
- , u.lastname AS 'Instructor'
, COUNT(DISTINCT q.id) AS 'Quizzes'
, COUNT(DISTINCT qu.id) AS 'Questions'
, SUM(IF (qu.qtype = 'multichoice', 1, 0 )) + SUM(IF (qu.qtype = 'truefalse', 1, 0 )) + SUM(IF (qu.qtype = 'match', 1, 0 )) AS 'multichoice'
, SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'shortanswer'
, COUNT( qu.id) - SUM(IF (qu.qtype = 'multichoice', 1, 0 )) - SUM(IF (qu.qtype = 'truefalse', 1, 0 )) - SUM(IF (qu.qtype = 'match', 1, 0 )) - SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'Other'
, (SUM(IF (qu.qtype = 'multichoice', 1, 0 )) + SUM(IF (qu.qtype = 'truefalse', 1, 0 )) + SUM(IF (qu.qtype = 'match', 1, 0 )))/COUNT( qu.id) AS 'Percent MC'
- , SUM(IF (qu.qtype = 'numerical', 1, 0 )) AS 'numerical'
- , SUM(IF (qu.qtype LIKE 'calc%', 1, 0 )) AS 'calculated'
- , SUM(IF (qu.qtype = 'random', 1, 0 )) AS 'random'
- , SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'shortanswer'
- , SUM(IF (qu.qtype = 'essay', 1, 0 )) AS 'essay'
, IF(q.shufflequestions > 0,'Yes','No') AS 'Randomized Questions'
, IF(q.shuffleanswers > 0,'Yes','No') AS 'Randomized Answers'
- , FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
- , FROM_UNIXTIME(MIN(q.timemodified)) AS 'Last Modified'
- , DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(MIN(q.timemodified))) AS 'Quiz age'
, MIN(DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified))) AS 'Min Quiz Age'
, MAX(DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified))) AS 'Max Quiz Age'
- , SUM(IF (DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified)) < 90, 1,0)) AS 'new quizzes'
FROM prefix_quiz AS q
JOIN prefix_course AS c on c.id = q.course
JOIN prefix_quiz_question_instances AS qqi ON qqi.quiz = q.id
LEFT JOIN prefix_question AS qu ON qu.id = qqi.question
WHERE
1
%%FILTER_STARTTIME:c.startdate:>%% %%FILTER_ENDTIME:c.startdate:<%%
GROUP BY c.id
ORDER BY c.shortname
