アドホック寄稿レポート
このページは現在作成中です！
ユーザとロールのレポート
カテゴリごとに登録された個別の学習者と教師の数を数えます（そのすべてのサブカテゴリを含む）
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
