Custom SQL queries report - Revision history

Tim@horizoneducationnetwork.org: /* Share your interesting queries here */

2025-05-22T15:02:25Z

Share your interesting queries here: <syntaxhighlight lang="SQL">

← Older revision		Revision as of 15:02, 22 May 2025
Line 96:		Line 96:
	This query was set to run daily to provide a report of students in a course that uses groups but have not yet been assigned to a group and provide a link to the view to assign them to a group. Teacher/Non-Editing teacher roles are filtered out.		This query was set to run daily to provide a report of students in a course that uses groups but have not yet been assigned to a group and provide a link to the view to assign them to a group. Teacher/Non-Editing teacher roles are filtered out.

	<~~code sql~~>		<syntaxhighlight lang="SQL">
	SELECT		SELECT
	DISTINCT u.username, CONCAT( u.firstname, " ", u.lastname ) AS NAME,		DISTINCT u.username, CONCAT( u.firstname, " ", u.lastname ) AS NAME,
Line 117:		Line 117:
	AND r.id NOT IN (3,4)		AND r.id NOT IN (3,4)
	ORDER BY ic.shortname		ORDER BY ic.shortname
	</~~code~~>		</syntaxhighlight>

	===Quiz attempts in the last week/month===		===Quiz attempts in the last week/month===
Line 123:		Line 123:
	Set this up as a scheduled report:		Set this up as a scheduled report:

	<~~code sql~~>		<syntaxhighlight lang="SQL">
	SELECT COUNT(*)		SELECT COUNT(*)
	FROM prefix_quiz_attempts		FROM prefix_quiz_attempts
Line 129:		Line 129:
	AND timefinish <= %%ENDTIME%%		AND timefinish <= %%ENDTIME%%
	AND preview = 0		AND preview = 0
	</~~code~~>		</syntaxhighlight>

	This '''must''' be set up as a '''Scheduled, on the first day of each week''' or '''Scheduled, on the first day of each month''' report, or it will not work.		This '''must''' be set up as a '''Scheduled, on the first day of each week''' or '''Scheduled, on the first day of each month''' report, or it will not work.
Line 137:		Line 137:
	This report shows roughly the same usage statistics that are sent to moodle.org when you register your site. The ones that are aggregated at http://moodle.org/stats/. (The only difference is that the registration form does not do AND confirmed = 1 for some reason.		This report shows roughly the same usage statistics that are sent to moodle.org when you register your site. The ones that are aggregated at http://moodle.org/stats/. (The only difference is that the registration form does not do AND confirmed = 1 for some reason.

	<~~code sql~~>		<syntaxhighlight lang="SQL">
	SELECT		SELECT
	(SELECT COUNT(id) FROM prefix_course) - 1 AS courses,		(SELECT COUNT(id) FROM prefix_course) - 1 AS courses,
Line 149:		Line 149:
	(SELECT COUNT(id) FROM prefix_resource) AS resources,		(SELECT COUNT(id) FROM prefix_resource) AS resources,
	(SELECT COUNT(id) FROM prefix_question) AS questions		(SELECT COUNT(id) FROM prefix_question) AS questions
	</~~code~~>		</syntaxhighlight>

	There are two interesting queries in this blog post [http://tjhunt.blogspot.com/2010/03/when-do-students-submit-their-online.html When do students submit their online tests?].		There are two interesting queries in this blog post [http://tjhunt.blogspot.com/2010/03/when-do-students-submit-their-online.html When do students submit their online tests?].
Line 157:		Line 157:
	This report shows a distinct count of users by their role, accessing your site. Each instance of user and role is counted once per month, no matter how many courses they access. We use this to show the total number of students and teachers accessing our site.		This report shows a distinct count of users by their role, accessing your site. Each instance of user and role is counted once per month, no matter how many courses they access. We use this to show the total number of students and teachers accessing our site.

	<~~code sql~~>		<syntaxhighlight lang="SQL">
	SELECT		SELECT
	month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month,		month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month,
Line 178:		Line 178:
	year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),		year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
	prefix_role.name		prefix_role.name
	</~~code~~>		</syntaxhighlight>

	You must have Statistics turned on to be able to generate any data with this report.		You must have Statistics turned on to be able to generate any data with this report.
Line 184:		Line 184:
	PostgreSQL version of the Monthly Usage by Role query:		PostgreSQL version of the Monthly Usage by Role query:

	<~~code sql~~>		<syntaxhighlight lang="SQL">
	SELECT		SELECT
	extract(month from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_month,		extract(month from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_month,
Line 205:		Line 205:
	extract(year from to_timestamp(prefix_stats_user_monthly.timeend)), extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),		extract(year from to_timestamp(prefix_stats_user_monthly.timeend)), extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),
	prefix_role.name		prefix_role.name
	</~~code~~>		</syntaxhighlight>

	===Show all Quiz results across a site===		===Show all Quiz results across a site===
	I'm sure someone will be able to improve upon this, but this is a query that will show Quiz results across a site. --[[User:Stuart Mealor\|Stuart Mealor]] 22:07, 9 April 2010 (UTC)		I'm sure someone will be able to improve upon this, but this is a query that will show Quiz results across a site. --[[User:Stuart Mealor\|Stuart Mealor]] 22:07, 9 April 2010 (UTC)

	<~~code sql~~>		<syntaxhighlight lang="SQL">
	SELECT		SELECT
	prefix_grade_items.itemname,		prefix_grade_items.itemname,
Line 228:		Line 228:
	AND (prefix_grade_grades.finalgrade > 0)		AND (prefix_grade_grades.finalgrade > 0)
	AND (prefix_user.deleted = 0)		AND (prefix_user.deleted = 0)
	</~~code~~>		</syntaxhighlight>

	==See also==		==See also==

Stuartrmealor: /* See also */

2020-10-11T04:05:34Z

Marycooch: copying from 3.2 docs

2017-05-22T10:15:41Z

copying from 3.2 docs

← Older revision		Revision as of 10:15, 22 May 2017
Line 92:		Line 92:

	If you come up with any interesting SQL to custom reports, you can share it here.		If you come up with any interesting SQL to custom reports, you can share it here.

			=== Students that are in a course where groups are turned on, but have not been assigned to a group yet ===
			This query was set to run daily to provide a report of students in a course that uses groups but have not yet been assigned to a group and provide a link to the view to assign them to a group. Teacher/Non-Editing teacher roles are filtered out.

			<code sql>
			SELECT
			DISTINCT u.username, CONCAT( u.firstname, " ", u.lastname ) AS NAME,
			ic.shortname AS "Course Code",
			CONCAT("%%WWWROOT%%/enrol/users.php%%Q%%id=",ic.id, "&action=addmember&user=", u.id) AS "Link to Add to Group"
			FROM
			prefix_course ic
			JOIN prefix_context con ON con.instanceid = ic.id
			JOIN prefix_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50
			JOIN prefix_role r ON ra.roleid = r.id
			JOIN prefix_user u ON u.id = ra.userid
			LEFT JOIN prefix_groups_members gm ON u.id = gm.userid
			WHERE
			ic.visible = 1
			AND ic.shortname NOT LIKE '%META%'
			AND ic.shortname NOT LIKE '%INOL%'
			AND u.username NOT LIKE 'email@domain.com'
			AND ic.groupmode <> '0'
			AND gm.userid IS NULL
			AND r.id NOT IN (3,4)
			ORDER BY ic.shortname
			</code>

	===Quiz attempts in the last week/month===		===Quiz attempts in the last week/month===

Libertymoodle: /* typo fix */

2017-03-09T09:31:32Z

typo fix

← Older revision		Revision as of 09:31, 9 March 2017
Line 210:		Line 210:
	* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.		* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.
	* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]		* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]
	* [[Configurable_reports\|Configurable reports]] ~~bloc~~		* [[Configurable_reports\|Configurable reports]] block

	[[Category:Contributed_code]]		[[Category:Contributed_code]]

Fox: /* See also */

2017-03-08T11:29:06Z

Germanvaleroelizondo: fix typo in link to spanish translation

2014-12-09T15:41:32Z

fix typo in link to spanish translation

← Older revision		Revision as of 15:41, 9 December 2014
Line 213:		Line 213:
	[[Category:Contributed_code]]		[[Category:Contributed_code]]

	[[es:Reportes de consultas ~~comunes~~ SQL]]		[[es:Reportes de consultas personalizadas SQL]]
	[[fr:admin/report/customsql/index]]		[[fr:admin/report/customsql/index]]

Germanvaleroelizondo: added link to spanish translation of page

2014-12-09T15:38:43Z

added link to spanish translation of page

← Older revision		Revision as of 15:38, 9 December 2014
Line 213:		Line 213:
	[[Category:Contributed_code]]		[[Category:Contributed_code]]

			[[es:Reportes de consultas comunes SQL]]
	[[fr:admin/report/customsql/index]]		[[fr:admin/report/customsql/index]]

P3t3r: Added a PostgreSQL version of the Monthly Usage by Role query

2012-09-07T14:21:26Z

Added a PostgreSQL version of the Monthly Usage by Role query

← Older revision		Revision as of 14:21, 7 September 2012
Line 155:		Line 155:

	You must have Statistics turned on to be able to generate any data with this report.		You must have Statistics turned on to be able to generate any data with this report.

			PostgreSQL version of the Monthly Usage by Role query:

			<code sql>
			SELECT
			extract(month from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_month,
			extract(year from to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_year,
			prefix_role.name AS user_role,
			COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users
			FROM
			prefix_stats_user_monthly
			INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid
			INNER JOIN prefix_context ON prefix_role_assignments.contextid = prefix_context.id
			INNER JOIN prefix_role ON prefix_role_assignments.roleid = prefix_role.id
			WHERE prefix_context.contextlevel = 50
			AND prefix_stats_user_monthly.stattype = 'activity'
			AND prefix_stats_user_monthly.courseid <>1
			GROUP BY extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),
			extract(year from to_timestamp(prefix_stats_user_monthly.timeend)),
			prefix_stats_user_monthly.stattype,
			prefix_role.name
			ORDER BY
			extract(year from to_timestamp(prefix_stats_user_monthly.timeend)), extract(month from to_timestamp(prefix_stats_user_monthly.timeend)),
			prefix_role.name
			</code>

	===Show all Quiz results across a site===		===Show all Quiz results across a site===

Tsala: sitewide reports template

2011-10-19T09:42:38Z

sitewide reports template

← Older revision		Revision as of 09:42, 19 October 2011
Line 1:		Line 1:
			{{Sitewide reports}}
	Created by The Open University		Created by The Open University

Line 5:		Line 6:
	==Installing this report==		==Installing this report==

	Follow the generic [[Installing ~~contributed modules or~~ plugins]] documentation.		Follow the generic [[Installing plugins]] documentation.


	==Screen shots==		==Screen shots==
Line 29:		Line 29:

	The list shows on-demand and scheduled queries separately.		The list shows on-demand and scheduled queries separately.


	===Running an on-demand query===		===Running an on-demand query===
Line 40:		Line 39:

	The summary of when the query was run and how long it took is shown at the bottom, along with a link to download the CSV file (for example to get the data into Excel) and a link back to the list of all available queries.		The summary of when the query was run and how long it took is shown at the bottom, along with a link to download the CSV file (for example to get the data into Excel) and a link back to the list of all available queries.


	===Viewing the results of scheduled queries===		===Viewing the results of scheduled queries===
Line 49:		Line 47:

	However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time.		However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time.


	==Interface for administrators==		==Interface for administrators==

	Administrators (that is, users with report/customsql:definequeries) see everything that other users see, but with some additions.		Administrators (that is, users with report/customsql:definequeries) see everything that other users see, but with some additions.


	===Additional controls in the staff interface===		===Additional controls in the staff interface===
Line 65:		Line 61:

	When viewing a particular query, Administrators get an edit and a delete link underneath the table of results.		When viewing a particular query, Administrators get an edit and a delete link underneath the table of results.


	===Adding or editing a query===		===Adding or editing a query===
Line 186:		Line 181:
	==See also==		==See also==

	* [[Reports_(administrator)]] for other admin reports
	* [[Installing contributed modules or plugins]]
	* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement		* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement
	* list of [[ad-hoc contributed reports]]		* list of [[ad-hoc contributed reports]]
Line 193:		Line 186:
	* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]		* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]

	~~[[Category: Administrator]]~~
	~~[[Category:Report]]~~
	[[Category:Contributed_code]]		[[Category:Contributed_code]]

	[[fr:admin/report/customsql/index]]		[[fr:admin/report/customsql/index]]

Tsala: removing 1.9 template

2011-07-11T10:09:31Z

removing 1.9 template

← Older revision		Revision as of 10:09, 11 July 2011
Line 1:		Line 1:
	~~{{Moodle 1.9}}~~
	Created by The Open University		Created by The Open University

← Older revision		Revision as of 04:05, 11 October 2020
Line 237:		Line 237:
	* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]		* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]
	* [[Configurable_reports\|Configurable reports]] block		* [[Configurable_reports\|Configurable reports]] block
			* [https://www.moodlebites.com/mod/page/view.php?id=9112 Moodle SQL Reporting] online introduction course.

	[[Category:Contributed_code]]		[[Category:Contributed_code]]

← Older revision		Revision as of 11:29, 8 March 2017
Line 210:		Line 210:
	* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.		* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.
	* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]		* [http://moodle.org/mod/data/view.php?d=13&rid=2884 Modules and plugins database entry]
			* [[Configurable_reports\|Configurable reports]] bloc

	[[Category:Contributed_code]]		[[Category:Contributed_code]]