Custom SQL queries report - Revision history

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

TimHunt: /* See also */

2011-06-09T15:47:28Z

Fox at 08:36, 6 April 2011

2011-04-06T08:36:52Z

← Older revision		Revision as of 08:36, 6 April 2011
Line 1:		Line 1:
			{{Moodle 1.9}}
	Created by The Open University		Created by The Open University

← 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]]

← Older revision		Revision as of 15:47, 9 June 2011
Line 192:		Line 192:
	* list of [[ad-hoc contributed reports]]		* list of [[ad-hoc contributed reports]]
	* [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]

	[[Category: Administrator]]		[[Category: Administrator]]