アドホック寄稿レポート
ユーザとロールのレポート
カテゴリごとに登録された個別の学習者と教師の数を数えます（そのすべてのサブカテゴリを含む）
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
小テストの問題に対する学生の回答（回答）
（フォーラムでTim huntと仲間のMoodlersの助けを借りて、Juan Fによって寄稿されました）
すべての生物学コースの特定の小テストを対象としたレポート、すべての問題の要約、および何人の学生がそれらを正しく/間違っているか。
SELECT
concat( u.firstname, " ", u.lastname ) AS "Student Name",
u.id,
quiza.userid,
q.course,
q.name,
quiza.attempt,
qa.slot,
que.questiontext AS 'Question',
qa.rightanswer AS 'Correct Answer',
qa.responsesummary AS 'Student Answer'
FROM mdl_quiz_attempts quiza
JOIN mdl_quiz q ON q.id=quiza.quiz
JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid
JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id
JOIN mdl_question que ON que.id = qa.questionid
JOIN mdl_user u ON u.id = quiza.userid
WHERE q.name = "BIO 208 Post Test Assessment"
AND q.course = "17926"
ORDER BY quiza.userid, quiza.attempt, qa.slot
コース/小テスト内でタグ付けされた問題
コースの各小テストのタグのサブ評定を計算します。 https://moodle.org/mod/forum/discuss.php?d=324314#p1346542 でDaniel Thiesによって寄稿されました
SELECT
quiz.name AS quiz,
t.rawname AS tag,
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/quiz/review.php?attempt=',
MAX(quiza.id),'">',u.firstname,' ',u.lastname,'</a>') AS student,
CAST(SUM(qas.fraction) as decimal(12,1)) AS correct,
CAST(SUM(qa.maxmark) as decimal(12,1)) AS maximum,
CAST(SUM(qas.fraction)/SUM(qa.maxmark)*100 as decimal(4,2)) AS score
FROM prefix_quiz_attempts quiza
JOIN prefix_user u ON quiza.userid = u.id
JOIN prefix_question_usages qu ON qu.id = quiza.uniqueid
JOIN prefix_question_attempts qa ON qa.questionusageid = qu.id
JOIN prefix_quiz quiz ON quiz.id = quiza.quiz
JOIN prefix_tag_instance ti ON qa.questionid = ti.itemid
JOIN prefix_tag t ON t.id = ti.tagid
JOIN (SELECT MAX(fraction) AS fraction, questionattemptid
FROM prefix_question_attempt_steps
GROUP BY questionattemptid) qas ON qas.questionattemptid = qa.id
WHERE quiz.course = %%COURSEID%%
GROUP BY quiza.userid,
quiza.quiz,
quiz.name,
u.firstname,
u.lastname,
ti.tagid,
t.rawname
ORDER BY quiza.quiz, t.rawname, u.lastname, u.firstname, score
SCORM活動レポート
完了したすべての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
登録されているすべてのユーザのSCORMステータスをコース名別に一覧表示します
このレポートには、コースに登録されているすべてのユーザのSCORMステータスが一覧表示されます。 最初にコース名、次に学生の姓、次に学生の名、次に受験番号の順に並べられます。 これは、レポートするコースIDをwhere句に追加することにより、個々のコースに制限できます。
SELECT
u.firstname AS First,
u.lastname AS Last,
u.idnumber AS Employee_ID,
u.city AS City,
uid.data AS State,
u.country AS Country,
g.name AS Group_name,
c.fullname AS Course,
st.attempt AS Attempt,
st.value AS Status,
FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") AS Date
FROM prefix_scorm_scoes_track AS st
JOIN prefix_user AS u ON st.userid=u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_scorm AS sc ON sc.id=st.scormid
JOIN prefix_course AS c ON c.id=sc.course
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
WHERE st.element='cmi.core.lesson_status' AND m.userid=u.id
UNION
SELECT
user2.firstname AS First,
user2.lastname AS Last,
user2. idnumber AS Employee_ID,
user2.city AS City,
uid.data AS State,
user2.country AS Country,
g.name AS Group_name,
c.fullname AS Course,
"-" AS Attempt,
"not_started" AS Status,
"-" AS Date
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
JOIN prefix_user_info_data AS uid ON uid.userid = user2.id
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_scorm AS sc ON sc.course=c.id
Left Join prefix_scorm_scoes_track AS st on st.scormid=sc.id AND st.userid=user2.id
WHERE st.timemodified IS NULL AND m.userid=user2.id
ORDER BY Course, Last, First, Attempt
Badges
ユーザーが発行したすべてのバッジ
寄稿者: Randy Thornton
このレポートには、バッジを発行した各ユーザのユーザ名ごとに、サイトとすべてのコースの両方で発行されたサイト上のすべてのバッジが表示されます。 合格した基準の種類（活動、コース修了、マニュアル）、発行日、有効期限、および発行されたバッジページへの直接リンクが含まれているため、そのバッジの他のすべての詳細を確認できます。
SELECT u.username, b.name AS badgename,
CASE
WHEN b.courseid IS NOT NULL THEN
(SELECT c.shortname
FROM prefix_course AS c
WHERE c.id = b.courseid)
WHEN b.courseid IS NULL THEN "*"
END AS Context,
CASE
WHEN t.criteriatype = 1 AND t.method = 1 THEN "Activity Completion (All)"
WHEN t.criteriatype = 1 AND t.method = 2 THEN "Activity Completion (Any)"
WHEN t.criteriatype = 2 AND t.method = 2 THEN "Manual Award"
WHEN t.criteriatype = 4 AND t.method = 1 THEN "Course Completion (All)"
WHEN t.criteriatype = 4 AND t.method = 2 THEN "Course Completion (Any)"
ELSE CONCAT ('Other: ', t.criteriatype)
END AS Criteriatype,
DATE_FORMAT( FROM_UNIXTIME( d.dateissued ) , '%Y-%m-%d' ) AS dateissued,
DATE_FORMAT( FROM_UNIXTIME( d.dateexpire ), '%Y-%m-%d' ) AS dateexpires,
CONCAT ('<a target="_new" href="%%WWWROOT%%/badges/badge.php?hash=',d.uniquehash,'">link</a>') AS Details
FROM prefix_badge_issued AS d
JOIN prefix_badge AS b ON d.badgeid = b.id
JOIN prefix_user AS u ON d.userid = u.id
JOIN prefix_badge_criteria AS t on b.id = t.badgeid
WHERE t.criteriatype <> 0
ORDER BY u.username
注意: FROM_UNIXTIMEコマンドはMySQL用です。
システムで利用可能なすべてのバッジ、獲得数=
寄稿者: https://moodle.org/user/profile.php?id=88992 Randy Thornton]
システム内のすべてのバッジのレポート。バッジの名前と説明、コンテキスト、コースバッジの場合はコースの短縮名、アクティブで利用可能かどうか、そのバッジが発行されたユーザの数が含まれます。
SELECT b.id, b.name, b.description,
CASE
WHEN b.type = 1 THEN "System"
WHEN b.type = 2 THEN "Course"
END AS Context,
CASE
WHEN b.courseid IS NOT NULL THEN
(SELECT c.shortname
FROM prefix_course AS c
WHERE c.id = b.courseid)
WHEN b.courseid IS NULL THEN "*"
END AS Course,
CASE
WHEN b.status = 0 OR b.status = 2 THEN "No"
WHEN b.status = 1 OR b.status = 3 THEN "Yes"
WHEN b.status = 4 THEN "x"
END AS Available,
CASE
WHEN b.status = 0 OR b.status = 1 THEN "0"
WHEN b.status = 2 OR b.status = 3 OR b.status = 4 THEN
(SELECT COUNT(*)
FROM prefix_badge_issued AS d
WHERE d.badgeid = b.id
)
END AS Earned
FROM prefix_badge AS b
リーダーボードバッジ
寄稿者: Randy Thornton
ユーザ名の簡単なリストと、ユーザが全体的に獲得したバッジの数。
SELECT u.username, (SELECT COUNT(*) FROM prefix_badge_issued AS d WHERE d.userid = u.id) AS earned
FROM prefix_user AS u
ORDER BY earned DESC, u.username ASC
バッジの管理（システムとコース）
システム全体のバッジ、コースおよびシステムレベルのバッジと、関連する "バッジの管理" ページへのリンクを一覧表示します。
SELECT b.id, b.name, b.description
,CASE
WHEN b.type = 1 THEN 'System'
WHEN b.type = 2 THEN 'Course'
END AS Level
,CONCAT('<a target="_new" href="%%WWWROOT%%/badges/index.php?type=', b.type, '&id=',
c.id, '">Manage badges in: ', c.fullname, '</a>') AS Manage
FROM prefix_badge AS b
JOIN prefix_course AS c ON c.id = b.courseid
管理者レポート
エクスポートに適したフォーマットでの構成の変更
寄稿者: Randy Thornton
管理レポートの構成の変更は非常に便利ですが、1つのリストに簡単にエクスポートできるフォーマットであると便利です。 これを行うためのコードは次のとおりです。
SELECT
DATE_FORMAT( FROM_UNIXTIME( g.timemodified ) , '%Y-%m-%d' ) AS date,
u.username AS user,
g.name AS setting,
CASE
WHEN g.plugin IS NULL THEN "core"
ELSE g.plugin
END AS plugin,
g.value AS new_value,
g.oldvalue AS original_value
FROM prefix_config_log AS g
JOIN prefix_user AS u ON g.userid = u.id
ORDER BY date DESC
ユーザ別のコーホート
寄稿者: Randy Thornton
すべてのユーザのリストを取得する方法と、それらが属するコーホート。
SELECT u.firstname, u.lastname, h.idnumber, h.name
FROM prefix_cohort AS h
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
JOIN prefix_user AS u ON hm.userid = u.id
ORDER BY u.firstname
コースのあるコホート
寄稿者: Randy Thornton
名前、ID、可視性、および登録されているコースを含むすべてのコーホートのリスト。
SELECT
- h.id,
- e.customint1,
h.name AS Cohort,
h.idnumber AS Cohortid,
CASE
WHEN h.visible = 1 THEN 'Yes'
ELSE '-'
END AS Cohortvisible,
CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php', CHAR(63),'id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_cohort h
JOIN prefix_enrol e ON h.id = e.customint1
JOIN prefix_course c ON c.id = e.courseid %%FILTER_COURSES:e.courseid%%
WHERE e.enrol = 'cohort' AND e.roleid = 5
作成されたコースと年ごとのアクティブなコース
アクティブなコースは、少なくとも1つのヒットがあるコースをカウントし、"Active_MoreThan100Hits" は、少なくとも100のヒットがあるコースをカウントします。
SELECT
YEAR( FROM_UNIXTIME( `timecreated` ) ) AS YEAR, COUNT( * ) AS Counter
, (SELECT COUNT( DISTINCT course )
FROM prefix_log AS l
WHERE YEAR( FROM_UNIXTIME( l.`time` ) ) = YEAR( FROM_UNIXTIME( `timecreated` ) )
) AS "Active"
,(SELECT COUNT(*) FROM (
SELECT COUNT( * ),time
FROM prefix_log AS l
GROUP BY course
HAVING COUNT(*) > 100) AS courses_log
WHERE YEAR( FROM_UNIXTIME( courses_log.`time` ) ) = YEAR( FROM_UNIXTIME( `timecreated` ) )
) AS "Active_MoreThan100Hits"
FROM `prefix_course`
GROUP BY YEAR( FROM_UNIXTIME( `timecreated` ) )
作成されたユーザと年ごとのアクティブユーザ
アクティブユーザは、ヒット数が1つ以上のユーザをカウントし、"Active_MoreThan500Hits" は、ヒット数が500以上のユーザをカウントします。
SELECT
YEAR( FROM_UNIXTIME( `firstaccess` ) ) AS YEAR, COUNT( * ) AS Counter
, (SELECT COUNT( DISTINCT userid )
FROM prefix_log AS l
WHERE YEAR( FROM_UNIXTIME( l.`time` ) ) = YEAR( FROM_UNIXTIME( `firstaccess` ) )
) AS "Active"
,(SELECT COUNT(*) FROM (
SELECT COUNT( * ),time
FROM prefix_log AS l
GROUP BY userid
HAVING COUNT(*) > 500) AS users_log
WHERE YEAR( FROM_UNIXTIME( users_log.`time` ) ) = YEAR( FROM_UNIXTIME( `firstaccess` ) )
) AS "Active_MoreThan500Hits"
FROM `prefix_user`
GROUP BY YEAR( FROM_UNIXTIME( `timecreated` ) )
コース集計レポート
Granite State CollegeのElizabeth Daltonによる寄稿
Moodle 2.6から2.8以降へのアップグレードを検討している場合、評定が変更される可能性があります。 このレポートは、変更のリスクがあるコースを定量化して特定するのに役立ちます。
特に、計算に変化をもたらすことが知られている次のパラメータの組み合わせを持つコースに注意してください。
- サブカテゴリの集計で設定された評定の平均。
- サブカテゴリを含む集計と最低のドロップを含む評定の単純加重平均
- 評定の合計が最低に落ちる
また、レビュー:
https://tracker.moodle.org/browse/MDL-48618
https://tracker.moodle.org/browse/MDL-48634
https://tracker.moodle.org/browse/MDL-49257
https://tracker.moodle.org/browse/MDL-50089
https://tracker.moodle.org/browse/MDL-50062
SELECT
COUNT(c.shortname) AS 'Count of Courses'
- If you want to display all the courses for each aggregation type, uncomment the next line and change GROUP BY settings
- , c.shortname AS 'course name'
- If you need to display grade categories for each aggregation type, uncomment the next line and change GROUP BY settings
- , gc.fullname AS 'grade category name'
, gc.aggregation AS 'aggregation method'
- These aggregation text strings appear to be hard-coded. I couldn't find a table for them. If you have aggregation types I haven't included here, they'll be blank in your report results.
, CASE gc.aggregation
WHEN 0 THEN 'Mean of Grades'
WHEN 2 THEN 'Median of Grades'
WHEN 6 THEN 'Highest Grade'
WHEN 8 THEN 'Mode of Grades'
WHEN 10 THEN 'Weighted Mean of Grades'
WHEN 11 THEN 'Simple Weighted Mean of Grades'
WHEN 12 THEN 'Mean of Grades (with extra credits)'
WHEN 13 THEN 'Sum of Grades'
END AS 'aggregation name'
- Note that gc.aggregatesubcats column is eliminated in 2.8 and later per MDL-47503, so comment that line on updated systems or you'll get an error
, gc.keephigh AS 'keep high'
, gc.droplow AS 'dr0p low'
, gc.aggregateonlygraded AS 'Aggregate only graded'
, gc.aggregateoutcomes AS 'aggregate outcomes'
, gc.aggregatesubcats AS 'aggregate subcategories'
- If you are displaying data about individual courses, you may want to know how old they are
- , FROM_UNIXTIME(c.startdate) AS 'course start date'
- If you are trying to use this report to check to see if final grades have changed after an upgrade, you might want these data items, but calculations can still change later when the courses are actually viewed. Also, you'll need to uncomment the necessary JOINs below
- , gi.itemname AS 'grade item'
- , gg.finalgrade AS 'final grade'
FROM
prefix_course AS c
JOIN prefix_grade_categories AS gc ON gc.courseid = c.id
JOIN prefix_course_categories AS cc ON cc.id = c.category
- LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id #AND gi.categoryid=gc.id
- LEFT JOIN prefix_grade_grades AS gg ON gg.itemid = gi.id AND gg.userid = u.id
WHERE
1
- AND gc.aggregation = 13 #only the dreaded Sum of Grades aggregations
- AND gc.depth = 1 # if for some reason you only want course aggregations, not subcategories
GROUP BY gc.aggregation, gc.keephigh, gc.droplow, gc.aggregateonlygraded, gc.aggregateoutcomes, gc.aggregatesubcats
Cronジョブの実行（task_scheduled）
SELECT classname
,DATE_FORMAT(FROM_UNIXTIME(lastruntime), '%H:%i [%d]') AS 'last'
,DATE_FORMAT(now(), '%H:%i') AS 'now'
,DATE_FORMAT(FROM_UNIXTIME(nextruntime), '%H:%i [%d]') AS 'next'
,DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP()-nextruntime), '%i') AS 'next in min'
FROM mdl_task_scheduled
WHERE now() > FROM_UNIXTIME(nextruntime)
親と子のコース関係を持つすべてのメタコース
寄稿者: Randy Thornton
これは、メタコースリンクの登録が含まれているコース ('親コース') と、登録を提供するためにそれらに接続されているコース ('子コース') のリストを示しています。
SELECT
c.fullname AS 'Parent course name',
c.shortname AS 'Parent course shortname',
en.courseid AS 'Parent course id',
(SELECT fullname FROM prefix_course WHERE prefix_course.id = en.customint1) As 'Child course name',
(SELECT shortname FROM prefix_course WHERE prefix_course.id = en.customint1) As 'Child course shortname',
en.customint1 AS 'Child course id'
FROM prefix_enrol en
JOIN prefix_course c ON c.id = en.courseid
WHERE en.enrol = 'meta'
ORDER BY c.fullname
分析レポートの学習
（Moodle v. 3.4以降）
学習分析モデルの概要
このレポートは、有効かどうかに関係なく、サイトの学習分析モデルのリストと、それらに関するいくつかの詳細を提供します。
（注: このレポートはPostgreSQLを使用するシステムで作成されました。他の形式のSQLではいくつかの変更が必要になる場合があります。）
Moodle HQのElizabeth Daltonによる寄稿
SELECT
am.id AS "model id",
split_part(am.target,'\',5) AS "target",
CASE WHEN am.enabled=1 THEN 'YES' ELSE 'NO' END AS "enabled",
CASE WHEN am.trained=1 THEN 'YES' ELSE 'NO' END AS "trained",
am.name,
/* indicators,*/
char_length(am.indicators) - char_length(REPLACE(am.indicators,',',))+1 AS "indicator count",
split_part(am.timesplitting,'\',5) AS "interval",
/*
to_timestamp(am.version) AS "version",
to_timestamp(am.timecreated) AS "time created",
to_timestamp(am.timemodified) AS "time modified",
- /
COUNT(DISTINCT ap.contextid) AS "contexts",
COUNT(ap.sampleid) AS "samples",
/* AVG(ap.prediction) AS "avg prediction", */
ROUND(ap.prediction,1) AS "prediction",
ROUND(AVG(aml.score),3) AS "model accuracy (avg)",
apa.actionname AS "action",
COUNT(apa.id) AS "number actions taken"
FROM prefix_analytics_models AS am
JOIN prefix_analytics_predictions AS ap ON am.id = ap.modelid
LEFT JOIN prefix_analytics_models_log AS aml ON aml.modelid = am.id
LEFT JOIN prefix_analytics_prediction_actions AS apa ON apa.predictionid = ap.id
GROUP BY am.id, ap.prediction, apa.actionname
アナリティクス指標の計算
すべてのモデルの各コンテキスト内の各サンプルの各指標に対して行われたすべての計算で構成される "analytics_indicator_calc" テーブルから計算を引き出します。 ほとんどの場合、これをコンテキストまたはサンプルごとに制限するか、少なくともコンテキストおよびサンプルごとにグループ化する必要があります。
SELECT
id,
to_timestamp(starttime) AS "start time",
to_timestamp(endtime) AS "end time",
contextid,
sampleorigin,
sampleid,
/*indicator, */
split_part(indicator,'\',2) AS "module",
split_part(indicator,'\',5) AS "indicator type",
value,
to_timestamp(timecreated) AS "time created"
FROM prefix_analytics_indicator_calc
WHERE id = 1
分析モデル
モデルごとに1つの行で構成される "analytics_models" テーブルからデータを取得します。 より包括的なビューを提供するためにさまざまなテーブルのデータをJOINでモデル化する拡張レポートについては、上記の "Learning Analytics Model Summary" レポートを参照してください。 Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
enabled,
trained,
name,
split_part(target,'\',5) AS "target",
/* indicators,*/
char_length(indicators) - char_length(REPLACE(indicators,',',))+1 AS "indicator count",
split_part(timesplitting,'\',5) AS "interval",
predictionsprocessor,
to_timestamp(version) AS "version",
to_timestamp(timecreated) AS "time created",
to_timestamp(timemodified) AS "time modified",
usermodified
FROM prefix_analytics_models
分析モデルログ
モデルごとの評価計算で構成される "analytics_models_log" テーブルからデータを取得します。 モデル評価がコマンドラインからシステム上で手動で実行されていない場合、このテーブルには内容がありません。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
modelid,
to_timestamp(version) AS "version",
evaluationmode,
split_part(target,'\',5) AS "target",
/* indicators,*/
char_length(indicators) - char_length(REPLACE(indicators,',',))+1 AS "indicator count",
split_part(timesplitting,'\',5) AS "interval",
score,
info,
dir,
to_timestamp(timecreated) AS "time created",
usermodified
FROM prefix_analytics_models_log
分析の予測
モデルごとの予測ごとに1つの行で構成される "analytics_predictions" テーブルからデータを取得します。 予測ごとに計算された指標の数をカウントしますが、リストはしません。 モデルがまだトレーニングされていない場合、システムは予測を行うことができず、このテーブルにはそのモデルIDの行が含まれません。 より包括的なビューを提供するためにさまざまなテーブルのデータをJOINでモデル化する拡張レポートについては、上記の "Learning Analytics Model Summary" レポートを参照してください。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
modelid,
contextid,
sampleid,
rangeindex,
prediction,
predictionscore,
char_length(calculations) - char_length(REPLACE(calculations,',',))+1 AS "indicators calculated",
to_timestamp(timecreated) AS "time created",
to_timestamp(timestart) AS "time start",
to_timestamp(timeend) AS "time end"
from prefix_analytics_predictions
分析予測アクション
予測ごとに実行されたアクションごとに1行で構成される "analytics_prediction_actions" テーブルからデータを取得します（たとえば、教師がリスクのある学生の概要レポートを表示します）。 モデルがまだ予測を行っていない場合、予測アクションはありません。 より包括的なビューを提供するためにさまざまなテーブルのデータをJOINでモデル化する拡張レポートについては、上記の "Learning Analytics Model Summary" レポートを参照してください。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
predictionid,
userid,
actionname,
to_timestamp(timecreated) AS "time created"
FROM prefix_analytics_prediction_actions
すべての指標を使用した分析予測
モデルごとの予測ごとに1つの行で構成される "analytics_predictions" テーブルからデータを取得します。 各予測に対して計算された指標を一覧表示します。 モデルがまだトレーニングされていない場合、システムは予測を行うことができず、このテーブルにはそのモデルIDの行が含まれません。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id AS "Prediction ID",
modelid AS "Model ID",
contextid AS "Context ID",
sampleid AS "Sample ID",
rangeindex AS "Analysis Interval",
prediction AS "Prediction value",
predictionscore,
calculations,
char_length(calculations) - char_length(REPLACE(calculations,',',))+1 AS "indicators calculated",
to_timestamp(timecreated) AS "time created",
to_timestamp(timestart) AS "time start",
to_timestamp(timeend) AS "time end"
from prefix_analytics_predictions
アナリティクスはサンプルを予測します
モデルごとの分析間隔ごとに1つの行で構成される "analytics_predict_samples" テーブルからデータを取得し、各予測に使用されたサンプルの数を示します。 サンプルの詳細はここには含まれていませんが、必要に応じて、sampleidsフィールドの内容を解析することにより、IDごとにサンプルを一覧表示するようにレポートを変更できます。 たとえば、これは、システムが特定のモデルと分析間隔の予測を生成した学生の登録数をカウントします。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
modelid,
analysableid,
split_part(timesplitting,'\',5) AS "interval",
rangeindex,
/* sampleids, */
char_length(sampleids) - char_length(REPLACE(sampleids,',',))+1 AS "samples used",
to_timestamp(timecreated) AS "time created",
to_timestamp(timemodified) AS "time modified"
FROM prefix_analytics_predict_samples
アナリティクストレーニングサンプル
モデルごとの分析間隔ごとに1行で構成される "analytics_train_samples" テーブルからデータを取得し、各トレーニング計算に使用されたサンプルの数を示します。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
modelid,
analysableid,
split_part(timesplitting,'\',5) AS "interval",
/* sampleids, */
char_length(sampleids) - char_length(REPLACE(sampleids,',',))+1 AS "samples used",
to_timestamp(timecreated) AS "time created"
FROM prefix_analytics_train_samples
分析は分析可能物を使用しました
モデルごとのコンテキストごとに1つの行で構成される "analytics_used_analysables" テーブルからデータを取得し、分析可能がモデルのトレーニングに使用されたか、予測に使用されたかを示します。 このデータは、トレーニングと予測のプロセスを制御するために使用されます。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
modelid,
action,
analysableid,
to_timestamp(firstanalysis) AS "first analysis",
to_timestamp(timeanalysed) AS "time analysed"
FROM prefix_analytics_used_analysables
アナリティクスの使用ファイル
モデルごとのファイルごとに1つの行で構成される "analytics_used_files" テーブルからデータを取得し、ファイルがモデルのトレーニングに使用されたか、予測に使用されたかを示します。 このデータは、トレーニングと予測のプロセスを制御するために使用されます。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
id,
modelid,
fileid,
action,
TO_TIMESTAMP(time) AS Time
FROM prefix_analytics_used_files
平均的な認知の深さと社会的な幅
これは、システム内のすべての学生の平均認知深度と社会的幅の指標を計算するための簡単なSQLスニペットです。 これは、このモデルで定義されているようにnullであるため、指標値0を無視します。
Moodle HQのElizabeth Daltonによる寄稿
SELECT
i.contextid,
i.sampleid,
TRUNC(AVG(CASE
WHEN i.indicator LIKE '%cognitive%' THEN i.value
ELSE '0'
END),2) AS "Average Cognitive Depth",
TRUNC(AVG(CASE
WHEN i.indicator LIKE '%social%' THEN i.value
ELSE '0'
END),2) AS "Average Social Breadth"
FROM prefix_analytics_indicator_calc as i
WHERE
i.value != 0
GROUP BY i.contextid, i.sampleid
コンピテンシー
フレームワークとそれらを含むコースからのコンピテンシーのリスト
François Parlantによる寄稿
SELECT
f.shortname AS 'Framework',
comp.shortname AS 'Competency',
cccomp.courseid AS 'Course id',
c.fullname AS 'Course name',
c.shortname AS 'Course code'
FROM
prefix_competency_coursecomp AS cccomp
INNER JOIN prefix_competency AS comp ON cccomp.competencyid = comp.id
INNER JOIN prefix_course AS c ON cccomp.courseid = c.id
INNER JOIN prefix_competency_framework AS f ON comp.competencyframeworkid = f.id
フレームワークの各コンピテンシーを使用してコースを数える
François Parlantによる寄稿
残念ながら、コンピテンシーフレームワークによるフィルタはありません。
select
f.shortname AS framework,
comp.shortname AS 'Competency',
COUNT(cccomp.competencyid) AS 'nb course'
FROM prefix_competency AS comp
INNER JOIN prefix_competency_framework AS f ON comp.competencyframeworkid = f.id
LEFT JOIN prefix_competency_coursecomp AS cccomp ON cccomp.competencyid = comp.id
GROUP BY comp.id, comp.shortname
IDを使用して詳細をスケーリング
寄稿者：Randy Thornton
コンピテンシーのインポートおよびエクスポートファイルには、ID番号付きの評価尺度が含まれています。 ただし、評定 > 評価尺度の管理ページには、評価尺度IDや、評価尺度が自分自身について保存するその他の有用な詳細（誰がいつ作成したか、どのコンテキストに関連するかなど）がありません。 この単純なクエリは、その情報を示します。
SELECT
s.id AS Scaleid,
s.name AS Scale_Name,
s.scale AS Scale,
CASE
WHEN s.courseid = 0 THEN 'System'
ELSE (SELECT shortname FROM prefix_course WHERE id = s.courseid)
END AS Context,
CASE
WHEN s.userid = 0 THEN 'System'
ELSE (SELECT username FROM prefix_user WHERE id = s.userid)
END AS User,
s.description,
DATE_FORMAT( FROM_UNIXTIME(s.timemodified), '%Y-%m-%d %H:%i' ) AS 'Modified'
FROM prefix_scale s
シラバス
私たちの学校は、教師にコースページにファイル（リソース）をドロップするように依頼するだけです。 このリソース（ファイルではなく）の名前を "シラバス"（大文字と小文字を区別しない）で始めます。
名前が "シラバス" で始まるリソースの数を数える
François Parlantによる寄稿
Select
r.name As 'Resource name',
cc.name AS 'Category',
CONCAT('<a href="%%WWWROOT%%/pluginfile.php/', ct.id, '/mod_resource/content/1/', f.filename, '">',f.filename,'</a>') AS 'Clickable filename',
c.fullname AS 'Course name',
c.shortname AS 'Course shortname',
- the date filters are connected to this "last modif" field
- userful to check if the syllabus has been updated this year
DATE_FORMAT(FROM_UNIXTIME(f.timemodified), '%e %b %Y') AS 'last modif',
- tell if the file is visible by the students or hidden
IF(cm.visible=0,"masqué","visible") AS 'Visibility',
- next line tries to give the real path (local path) if you want to create a zip file using an external script)
- notice that the path is in the column "contenthash" and NOT in the column pathhash
- if the contenthash starts with 9af3... then the file is stored in moodledata/filedir/9a/f3/contenthash
- I try to get the path to moodledata from the value of the geoip variable in the mdl_config table... maybe a bad idea
CONCAT('"',(Select left(value, length(value)-25) from prefix_config where name ="geoip2file"),'/filedir/', left(f.contenthash,2), "/",substring(f.contenthash,3,2),'/', f.contenthash, '"') AS 'link'
FROM prefix_resource AS r
INNER JOIN prefix_course_modules AS cm ON cm.instance = r.id
INNER JOIN prefix_course AS c ON c.id = r.course
INNER JOIN prefix_context AS ct ON ct.instanceid = cm.id
JOIN prefix_course_categories cc ON c.category = cc.id
INNER JOIN prefix_files AS f ON f.contextid = ct.id AND f.mimetype IS NOT NULL AND f.component = 'mod_resource'
WHERE LOWER( r.name) LIKE 'syllabus%'
%%FILTER_STARTTIME:f.timemodified:>%% %%FILTER_ENDTIME:f.timemodified:<%%
%%FILTER_SUBCATEGORIES:cc.path%%
"シラバス" のタグが付けられたファイルを一覧表示します
François Parlantによる寄稿
Select
t.rawname AS 'rawtag',
c.shortname AS 'Cours shortname',
c.fullname AS 'Course name',
r.name As 'Resource name',
CONCAT('<a href="%%WWWROOT%%/pluginfile.php/', ti.contextid, '/mod_resource/content/1/', f.filename, '">cliquez ici</a>') AS 'link',
ti.contextid AS 'Instance for link',
f.id AS 'file id'
FROM prefix_tag_instance AS ti
INNER JOIN prefix_tag AS t ON ti.tagid = t.id
INNER JOIN prefix_course_modules AS cm ON ti.itemid = cm.id
INNER JOIN prefix_course AS c ON cm.course = c.id
INNER JOIN prefix_resource AS r ON r.id = cm.instance
INNER JOIN prefix_files AS f ON f.contextid = ti.contextid AND f.mimetype IS NOT NULL
WHERE t.rawname = 'Syllabus'
"シラバス" で始まる名前のリソースがないコースのリスト
François Parlantによる寄稿
select c.id, c.shortname,
CONCAT('<a href="%%WWWROOT%%/course/view.php?id=', c.id, '">',c.fullname,'</a>') AS 'Course link'
FROM prefix_course AS c
LEFT JOIN (
Select r.course
from prefix_resource AS r
WHERE LOWER( r.name) LIKE 'syllabus%'
GROUP BY r.course) AS r ON r.course = c.id
INNER JOIN prefix_course_categories cc ON c.category = cc.id
WHERE r.course IS NULL
%%FILTER_SUBCATEGORIES:cc.path%%
%%FILTER_STARTTIME:c.startdate:>%% %%FILTER_ENDTIME:c.enddate:<%%
コースのリストには、"シラバス%" のような名前の複数のリソースがあります
François Parlantによる寄稿
select
r.course,
c.shortname,
CONCAT('<a href="%%WWWROOT%%/course/view.php?id=', r.id, '">',c.fullname,'</a>') AS 'Course link'
FROM prefix_resource AS r
INNER JOIN prefix_course AS c ON c.id = r.course
JOIN prefix_course_categories cc ON c.category = cc.id
WHERE LOWER( r.name) LIKE 'syllabus%'
GROUP BY r.course HAVING count(r.course)>1
%%FILTER_SUBCATEGORIES:cc.path%%
チャット
チャットを一覧表示する
François Parlantによる寄稿 このレポートには、コースの名前と以降のクエリに必要なさまざまなIDを含むすべてのチャットのリストが表示されます。
"参加者" 列は、（オプションの）二次レポートを処理することを目的としています。 不要な場合は消去できます。
このチャットへの現在の参加者リストを提供する別の（オプションの）レポートへの直接リンクを生成します。
select
c.shortname,
c.fullname,
ch.course,
ch.id,
- if you intend to use a secondary report to see the participants of a specific chat
- create the secondary report, check the id of the report in the url, and change the 21 in next line to your participant report's id
CONCAT('<a href="%%WWWROOT%%/blocks/configurable_reports/viewreport.php?id=21&filter_courses=', ch.id,'">Chat participants</a>') AS 'Course link',
ch.chattime
FROM
prefix_chat ch
INNER JOIN prefix_course c ON c.id = ch.course
ORDER BY ch.chattime, c.fullname
チャットの参加者（オプションの二次レポート）
このバージョンの参加者リストは、前のレポートで提供されたリンクで機能することを目的としています。
- ユーザがプラットフォーム上のすべてのチャットを一覧表示するレポートを開きます
- ユーザが "チャット参加者" 列のリンクをクリックする
- チャットIDのフィルタでこのレポートを開く
（注意してください。代わりにchatidを実行するようにcoursefilterを調整しています。表示された "course filter" は機能しません！しかし必要です）
SELECT
c.id AS courseid,
chu.chatid,
chu.userid AS 'chat user userid',
c.fullname,
u.username,
u.firstname,
u.lastname,
u.email
FROM
prefix_user u
LEFT JOIN prefix_chat_users chu ON chu.userid = u.id
INNER JOIN prefix_course c ON c.id = chu.course
WHERE 1=1
%%FILTER_COURSES:chu.chatid%%
- you can also filter by course
- but don't put comment line between where and filter
- %%FILTER_COURSES:chu.course%%
ORDER BY c.fullname
チャットする現在の参加者を一覧表示します
François Parlantによる寄稿
SELECT
c.id AS courseid,
chu.chatid,
chu.userid AS 'chat user userid',
c.fullname,
u.username,
u.firstname,
u.lastname,
u.email
FROM
prefix_user u
LEFT JOIN prefix_chat_users chu ON chu.userid = u.id
INNER JOIN prefix_course c ON c.id = chu.course
WHERE 1=1
%%FILTER_COURSES:chu.course%%
ORDER BY c.fullname
便利なサブクエリ
このセクションには、一般的な手順がより大きなクエリの一部としてどのように役立つかを示す、短い1つの目的のサブクエリを入力してください。
コース内のすべての教師
このスニペットは、コースから教師を取得する方法を示しています。 コースオブジェクトのcontextevelは50です。デフォルトのTeacherロールはロールID3です。
,(SELECT GROUP_CONCAT( CONCAT( u.firstname, " ", u.lastname ) )
FROM prefix_course ic
JOIN prefix_context con ON con.instanceid = ic.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 ic.id = c.id
GROUP BY ic.id
) AS TeacherNames
ユーザのカスタムユーザプロファイルフィールドを取得する
寄稿者; Randy Thornton
このコードスニペットは、ユーザをカスタムプロファイルフィールドデータに接続する方法を示しています。 これにより、すべてのカスタムプロファイルフィールドとデータを持つすべてのユーザが一覧表示されます。 カスタムプロファイルフィールドには2つのテーブルがあります。1つはプロファイルフィールドの定義（user_info_field）とその設定用で、もう1つはユーザが入力したデータを保持するためのテーブル（user_info_data）です。
SELECT u.username, uif.name, uid.data
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
If you want to limit it to one of those fields, you can restrict it by shortname of the custom profile field, so:
SELECT u.username, uif.name, uid.data
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 = 'shortname1')
ショートネーム 'shortname1' のカスタムプロファイルフィールドからのデータのみが表示されます。
2つ以上のカスタムプロファイルフィールドでこれを行う場合は、それぞれにJOINとテーブルエイリアスを設定し、プロファイルフィールドの短縮名ごとに制限を設ける必要があります。 例:
SELECT u.username, d1.data AS 'Profile One', d2.data As 'Profile Two'
FROM prefix_user u
JOIN prefix_user_info_data d1 ON d1.userid = u.id
JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'shortname1'
JOIN prefix_user_info_data d2 ON d2.userid = u.id
JOIN prefix_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'shortname2'
注: 代替方法
使用する必要のあるフィールドが2つ以上ある場合、結合が多すぎるために、このクエリがタイムアウトするか、データが返されない可能性があります。 制限は約10個のカスタムプロファイルフィールドのようです。
代わりに、プロファイルフィールドごとに副選択を使用する別の方法を使用する必要があります。 詳細とサンプルコードは、このフォーラムディスカッションにあります: https://moodle.org/mod/forum/discuss.php?d=355502#p1434854 スタイルのサンプルは次のとおりです。
SELECT u.username
,(SELECT d1.data FROM prefix_user_info_data d1
JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'shortname1'
WHERE d1.userid = u.id
) AS thefirstfield
,(SELECT d1.data FROM prefix_user_info_data d1
JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'shortname2'
WHERE d1.userid = u.id
) AS thesecondfield
FROM prefix_user u
構成可能なレポートの日付と時刻のフィルタの使用方法
寄稿者: Randy Thornton
構成可能なレポートブロックで、時間と日付フィルタを設定して、レポートの開始日/時刻と終了日/時刻をインタラクティブに選択できるようにすることができます。 これは、タイムスタンプであるテーブルの任意の列で機能します。
簡単な例を次に示します。
SELECT u.username,
DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d %H:%i') AS 'FirstAccess',
DATE_FORMAT(FROM_UNIXTIME(u.lastaccess),'%Y-%m-%d %H:%i') AS 'LastAccess'
FROM prefix_user u
WHERE 1=1
%%FILTER_STARTTIME:u.firstaccess:>%%
%%FILTER_ENDTIME:u.lastaccess:<%%
1) クエリに必要な日時列を使用するには、フィルタのテーブルと列の名前を置き換える必要があります。 上記の例では、ユーザテーブルのfirstaccess列とlastaccess列でフィルタリングします。 コースの完了に関するレポートを作成している場合は、timecompleted列などを配置できます。
2) 次に、レポートのフィルタタブに開始日/終了日フィルタを追加する必要があります。 そうしないと、おそらくレポートは実行されますが、フィルタは無視されます。
注：WHERE 1 = 1ステートメントは、構成レポートのフィルタの特性です。クエリにWHEREステートメントがまだない場合は、このダミーのWHEREを追加して、ステートメントを有効に保つ必要があります。 コードにすでにWHEREステートメントがある場合は、その後（およびGROUPまたはORDER BYステートメントの前）に %%FILTER%% プレースホルダーを追加するだけです。
関連項目
- GitHubの構成可能なレポートリポジトリ
- Moodle DBスキーマエクスプローラ - テーブル、フィールド、およびテーブル間の外部キー接続の検索とフィルタリング。
