General plugins

This feature is great. I've been wanting it for ages and we finally were able to get the right people to request it so that we were allocated a few days to develop it. Thanks Tim.

--sam

Thanks Tim, this is really useful and will drop my use of PHPMyAdmin down considerably.

Paul.

I suspect this will produce a raft of sql queries being shared around to produce reports, really nicely done.

Tim,

this is excellent. This could form the basis for a little report generator that is long overdue in Moodle.

Alex - Synergy Learning

I just uploaded a new version. The new feature is that you can now set up scheduled queries that are automatically run on the first day of the week or month. First day of the week is defined as midnight between Friday/Saturday for internal OU reasons, but there is a fairly obvious constant at the top of locallib.php to hack if you want to change this.

There is also now documentation: Custom_SQL_queries_report, and a component in the tracker for bug reports: http://tracker.moodle.org/browse/CONTRIB/component/10540

Finally, standard disclaimer, the download package won't be ready until tomorrow, but it is in CVS now.

BRILLIANT! Just what I was looking for!

Thanks Tim.

Great plugin. A great addition to it would be the ability to use cfg variables in the query so that you can create sql querries that are dynamic.

Is there a central location that discusses and links to all the possible reporting options for Moodle - current, in development, and under discussion? I expect to be pointed to the forums, of course - my question stems from the desire for a more comprehensive view of the state of affairs. I am aware of the dev project for a custom reporting tool. Thank you!

Yes, as everyone is saying, it's great to see this development - well done Tim!

I just tried a very simple report:
SELECT firstname,lastname,email FROM prefix_user

What surprised me is that some records returned email addresses, and others did not!?
Although all users do have an email, and I can see it's active in their profile.

Is there something special about the email field I'm not aware of maybe?
Any ideas...

Thanks, Stu.

I agree with the earlier comment, it will be awesome if we can have a location to share these Custom SQL queries - administrators sharing cool queries similar to the way teachers share Glossaries and Database Presets

Stu

Cheers Tim, this is really useful

It doesn't seem to let you specify a LIMIT clause on any of the queries though - would be useful, rather than it listing the first 5000 records (or whatever the default is).

I'm trying to use this module to have supervisors see what certificates their employees has received. Is there any way to have the query "know" who is accessing the query and use that user id in the query? Similar to replacing %%WWWROOT%%with the domain? Maybe some way to define %%USERID%%or something along those lines. It would make it much easier than creating a different query for EVERY supervisor.

Thanks.

This looks really useful - thanks!  Started playing around with it but noticed that it was visible to Student.  Not sure why.  So I edited permissions/define roles to set this Report to "prevent" and then "prohibit" for Student role but it had no effect.  I then cured it by adjusting the block Site Administration with an override permission to prevent a Student from seeing that block  -which they would not have seen normally.  So maybe I missed somethng really obvious.  this on 1.9.5+ (Build: 20090930)

I have installed admin report correctly i.e admin/report/customsql
but i am not able to view cutomsql from administration block..

Version - 1.9

Thanks very much for this Tim. This is a particularly useful tool for moodle application admins who are not also skillled db admins. We are using it on our development instance until we're really, really sure that an aberrant query won't break the live production instance for 12,000 users. We're almost convinced

We've just run a report, like that on your blog, to see how soon before the deadline our students are submitting their quizzes. Answer: mostly at the last minute, interestingly, unlike OU.

We'd also like to collect the same type of information for assignment submissions. Any chance you could share with us the query for that? One more addition to the, hopefully, growing repository of queries? ... Alan

Hello Tim,

Thanks for create so useful plugin, I want to try it but I  haven't been able to install it yet, I unzipped and copied it to moodle/admin/reports, have gone to 'Notifications' but nothing happens, my moodle version is Moodle 1.9.5 (Build: 20090520), is this correct for the plugin? How can install it successfully?

Thanks is advance,

Esteban

This sounds great, REALLY great!

however, after a seemingly smooth installation, I am faced with a blank page at
http://domain/admin/report/customsql/index.php

just nothing appearing at all...am i missing some settings here?

(am running version 1.9.7)

Paul C

Hi all

I have version 1.8 but i need to use this module...can't upgrade to 1.9 cause my courses are to difficult to upgrade (i made too many changes).Plz need help

Tim, thank you for all your work on this feature. I have installed it ok and have added and run the Usage summary report. However I'm having a problem with the Monthly Usage by Role report - I get an error:

ERROR: syntax error at or near "." LINE 2: month(from_unixtime(`m_stats_user_monthly`.`timeend`)) AS ca... ^

Would this be down to the fact we have a PostgreSQL database?

I am having a problem with security for this module. I would like to prevent students from viewing custom reports completely, but the menu item Site Administration :: Reports :: Ad-hoc database queries" is still visible for all students, even though no items are accessible underneath.

Is it possible to completely hide "Ad-hoc database queries" if there are no custom queries underneath that the current user has access to? I tried to edit Student role and set custom sql prevent flags, but it didn't work.

Any ideas?

Andrew

I would love some debug help if anyone has a moment.

User activity report.

SELECT FROM_UNIXTIME(prefix_log.time),
prefix_user.username,
prefix_user.firstname,
prefix_user.lastname,
prefix_user.email,
prefix_course.shortname,
prefix_resource.name
FROM prefix_log
INNER JOIN prefix_user ON prefix_log.userid=prefix_user.id
INNER JOIN prefix_course ON prefix_log.course=prefix_course.id
INNER JOIN prefix_resource ON prefix_log.info=prefix_resource.id
ORDER by FROM_UNIXTIME(prefix_log.time)DESC
#LIMIT 1000 #LIMIT no worky

I get this error when i copy customsql module in report and run notification from admin

This script generated 1 warning - Scroll to the first warning

Tim,

This is GREAT! Installed the query admin report without any issues and have been working on my SQL!

I have a question about a query I've put together (pasted below). I'd like to create a query that pulls final grades from courses but in the midst of writing the query I discovered that the "finalgrade" field in the mdl_grade_grades table isn't really a course final grade. It is apparently more of a category final grade, I guess.

I managed to work something out here that works for my current setup but I don't think it's going to hold water in the face of some careful scrutiny. It was designed on the assumption (in the ORDER BY and GROUP BY statements) that the ID for the final grade record in mdl_grade_grades is going to be a lower integer than the other items but I'm not sure if that will always be an absolute truth....or will it?

Anyway, I wanted to get some feedback on it here before posting it in the documentation area.

Here it is

SELECT
prefix_course.fullname AS "Course",
ROUND(prefix_grade_grades.finalgrade, 2) AS "Final Grade",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
prefix_user.username AS "Username"
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
WHERE prefix_grade_items.id = prefix_grade_grades.itemid
AND (prefix_grade_items.courseid = prefix_course.id)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
AND (prefix_grade_grades.timecreated IS NULL)
GROUP BY prefix_user.username
ORDER BY prefix_grade_grades.id

Also, I'm pretty green when it comes to SQL so if I've made some glaring mistake here in terms of efficiency etc. feel free to educate me

Jason

P.S. EDIT - I just removed the following line as I realized it was problematic.
AND (prefix_grade_grades.usermodified IS NULL)

Thanks Tim for this great admin report, it's very useful

I've made a french translation that you can integrate.

And i also have made some queries that i'll certainly share, if it can help other people. Would also be glad to know is some of them can be optimized

It would also be very cool to be able to sort results ascending/descending by clicking on columns, your tool regenerating the query but just changing sort order

Thank you for the very useful plugin!

May anyone help me to rewrite query for speedup?

select
  a.fullname,
   (select
       count(1)
    from
        prefix_question b
    where
        b.parent=0 and b.hidden=0
        and b.category in
        (select
            c.id
         from
            prefix_question_categories c
         where
            c.contextid=(select d.id from prefix_context d where d.contextlevel=50 and d.instanceid=a.id)
        )
   ) as qcount
from
  prefix_course a

Aim - get the list of courses with corresponding number of questions in it.

Thank you in advance.

I’m using Moodle 2.0 (Build: 20101130) and have extracted the customsql.zip file associated with this plugin to moodle/admin/report. However, I'm unable to complete the installation as I’m getting a message that reads “Plugin is not compatible with Moodle 2.x or later”. Are you intending to release a Moodle 2.0 version of this plugin? Thanks.

Tim,

Firstly I love your report!  We have been using it for about a year now and it's very useful!  Thanks for this!

However, I have just enountered something that has never happened before and am perplexed!  Hoping you or someone else might be able to shed some light?

It seems as though, attempting to run a query keeps bringing our DB down!  And has caused the following error message to appear:

Error: Database connection failed.

It is possible that the database is overloaded or otherwise not running properly.

Here is the query: (syntax is fine as we have run it on our development server ... however our development server obviously does not have any connections!)

SELECT DISTINCT prefix_course_categories.name, Count(prefix_log.action) AS CountOfaction, Min(prefix_log.time) AS MinOftime
FROM (prefix_log INNER JOIN prefix_course ON prefix_log.course = prefix_course.id) INNER JOIN prefix_course_categories ON prefix_course.category = prefix_course_categories.id
GROUP BY prefix_course_categories.name
HAVING (((prefix_course_categories.name)="CUP"))

One very recent change that has occurred is a migration of web servers, so my thought was that there might be a setting on the new web server somewhere that is different than it's predecessor.

Any ideas?

Thanks!!!!

Moodle v. 1.9.8

MYSQL

 

Dear colleagues, please help!

I'm new to sql and moodle DB. and i need to make a report like that:

prefix_user.lastname	???	prefix_grade_items.itemname	prefix_grade_items.grademax	prefix_grade_grades.finalgrade	prefix_grade_grades_timecreated
user lastname	courses user enrolled in	quizes inside courses	max grade	user final grade	date grade recived (in datetime format)
u1	c1	q1	100	95	22-01-2011
u1	c1	q2	100	100	23-01-2011
u1	c2	q3	100	50	22-10-2011
u2	c1	q1	100	NULL	25-01-2011

So as a result I want to see a list of all users with courses they enrolled. Then quizes inside courses (so i wanna see grades fox quiz only - not for the whole course) with max possible grade and with the grade user received (NULL if he do not have mark for this quiz). Also I wonder how to make date look likу normal date - not epoch.

 

Is it possible?

Thanx

 

Tim, is there a way to create a report that first prompts the user for a value and then uses that value as a filter condition in the SQL statement?

Example: the report prompts the user for a "department" and then displays all the records in the database containing the specified value in the department field on mdl_user.

SELECT firstname, lastname, idnumber
FROM prefix_user u
WHERE u.department = %%DEPARTMENT%%

"%%DEPARTMENT%%" being whatever value the user is prompted for.

Tim:

Needind a little help regarding "Custom SQL queries" (Ad-ho database queries).

I have 7 courses, each with severall groups and hundreds of students.

The groups represent severall school/class names. In a specific course, there may exist groups with names equal to groups in other specific course (meaning that a school/class has students in both these specific courses).

I need several reports:

1 - Total number of (unique) users that are enrolled as students at least in a course. I tried using:

SELECT r.name AS 'Papel',l.action AS 'Ação', count(DISTINCT l.userid ) AS 'Nº logins'
FROM prefix_user AS u
JOIN prefix_log AS l ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'

2 - Number of (unique) "students" (users that are enrolled as students at least in a course), that made at least ONE login during a specific period of time (in the following example, the month of October):

SELECT r.name AS 'Papel',l.action AS 'Ação', count(DISTINCT l.userid ) AS 'Nº logins'
FROM prefix_user AS u
JOIN prefix_log AS l ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'

3 - Total number of student logins (not unique) during a specific period of time (in the following example, the month of October):

SELECT r.name AS 'Papel',l.action AS 'Ação', count(l.userid ) AS 'Nº logins'
FROM prefix_user AS u
JOIN prefix_log AS l ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'

4 -Total number of quiz attempts, by students, completed in a specific period of time (in the following example, the month of October):

SELECT r.name AS 'Papel',COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_user AS u ON qa.userid = u.id
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid = 5 AND timefinish > unix_timestamp('2011-10-01 00:00:00')
    AND timefinish <= unix_timestamp('2011-10-31 23:59:59')
    AND preview = 0

5 - Average time used by students to complete a quiz that was finished in a specific period of time (in the following example, the month of October). Note that I do not consider a period of over a week (7 days) between starting and ending the quiz:

SELECT FROM_UNIXTIME(AVG(TIMEDIFF(FROM_UNIXTIME(q.timefinish),FROM_UNIXTIME(q.timestart))))
FROM prefix_quiz_attempts AS q
WHERE q.timefinish > unix_timestamp('2011-10-01 00:00:00')
    AND q.timefinish <= unix_timestamp('2011-10-31 23:59:59')
    AND TIMEDIFF(FROM_UNIXTIME(q.timefinish),FROM_UNIXTIME(q.timestart)) > 0 AND TIMEDIFF(FROM_UNIXTIME(q.timefinish),FROM_UNIXTIME(q.timestart)) < 604800

 

Then, I need to answer this same queries, but separating the output by group.

What I did was (for example, for the "1 - Total number of (unique) users that are enrolled as students at least in a course"):

SELECT r.name,l.action,g.name,count(DISTINCT l.userid ) AS counter
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
JOIN prefix_user AS u ON l.userid = u.id
JOIN prefix_groups_members AS gm ON u.id = gm.userid
JOIN prefix_groups AS g ON gm.groupid = g.id
JOIN prefix_course AS c ON c.id = g.courseid
WHERE ra.roleid = 5 AND time >= unix_timestamp('2011-10-01 00:00:00') AND time <= unix_timestamp('2011-10-31 23:59:59') AND l.action='login'
GROUP BY g.name
ORDER BY g.name

Could you tell me if this is the right way of doing it?

Tim,

Just a heads up that as far as I can see, this will need to be updated for version 2.2, as the reports are no longer in the /admin/report folder and are now stored in /report instead.

Thanks

Thanks to all for the great SQL contributions, they are really helpful to someone who is not that strong in SQL (like me)!

I am running Moodle 2.2.1 and have installed the ad-hoc database query plugin. I was looking over the contributed SQL queries and found one that I was particularly interested in:

How many LOGINs per user and user's Activity

+ link username to a user activity graph report

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

When I run the query, I receive the error:

ERROR: Incorrect number of query parameters. Expected 1, got 0.

Does someone know the source of the error?

Thanks in advance!

I want to thank the contributors of this plug-in for great work.  It really fills a gap from a report/grade book stand point.  

I am not sure if this is the correct location for an enquiry of this nature, if not please point me in the right direction.  I am trying to produce a report that will allow a merge to produce summarized quarterly report cards with all courses on an individual basis for print out.

In looking through the custom sql I found two reports that get me close. Unfortunately my sql query skills are lacking.

The  Learner Report  and the Site Wide report with Course Totals . The values I am looking for are the "category totals" .  The learner report returns these but with no category labels, so cannot delineate between first and second quarter categories.  Also on Site wide reports it will produce the grades I am needing if you substitute "category" for "course" in the line below, but unfortunately it also does not produce labels for the grade categories:

WHERE  gi.courseid = c.id AND gi.itemtype = 'course'

The table that contains the category labels is:

Table
mdl_grade_categories

Field
fullname

Thanks in advance, any help would be greatly appreciated.

Jay Hickman

Agape Christian Academy

Hello,

Does anyone have or know how I can get a report to show courses that have been created within a specific school year? I think I modified a query to start something, but I don't think the "where" clause will work. I'm running Moodle on a windows server with apache.

SELECT COUNT(id) AS Courses FROM `mdl_course`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120

Thanks,
Tyler 

Tim,

Great report plugin, thank you. Just a small request for help, since i am SQL challenged.

I have run the Sitewide grade report as below, and it works fine, but is there some way, that i can get the total scores per student, as a row, with subsquent columns being the total marks across the various courses that the student has taken? (sort of an overview report combined with the site wide user report)

Since we need to evaluate the students across all the courses this would give a birds eye view for the student. i think that this code can be modified to group per student, across courses, for a predefined category.

Wish list would also include:

1. Failed marks would underlined, 

2. Each total would be a link which would open up to that courses detailed view.

The student could have this view also, restricted to only his marks.

thank you for your help in this,

regards and warm wishes for the new year!

 

 

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,

DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) 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

 

 

Hi guys,

I've been looking for something like this for a while as I am needing to make a "Report Card" section for my site.

Using Jason Hollowel's code I've been able to generate a list that shows EVERY students final grades, but this leads to a glaring privacy concern.

This is Jason's code that I've been using, I'm wondering if we can change it to suit my needs.

SELECT
prefix_course.fullname AS "Course",
ROUND(prefix_grade_grades.finalgrade, 2) AS "Final Grade",
prefix_user.firstname AS "First Name",
prefix_user.lastname AS "Last Name",
prefix_user.username AS "Username"
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
INNER JOIN prefix_course ON prefix_grade_items.courseid = prefix_course.id
WHERE prefix_grade_items.id = prefix_grade_grades.itemid
AND (prefix_grade_items.courseid = prefix_course.id)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
AND (prefix_grade_grades.timecreated IS NULL)
AND prefix_grade_items.itemtype = 'course'
ORDER BY prefix_course.id, prefix_user.id

 

I need to have something that a student can click on "Report Card" and they will be taken to a list that shows them all of their final grades in all of the courses they are enrolled in (but only their own grades).  This also needs to be organized by Course Category (Each year/cohort has three terms so it looks like "Year 1"(category) -> "Term 1"(sub-category) -> "Science"(course)

I'd love it to look like this (or at least have this information) when a student clicks on Report Card.  I dont know or understand SQL at all so any help would be wonderful!

Joe Student	Term 1	English	90%
Joe Student	Term 1	Math	90%
Joe Student	Term 1	Science	90%
Joe Student	Term 2	English	80%
Joe Student	Term 2	Math	80%
Joe Student	Term 2	Science	80%

Thanks so much for helping out!!!

Firstly, thanks for this very useful plugin which I've been trying out along with an ad-hoc contributed report to show a learner report, by Learner with grades.

The learner report works OK but I would like to know if it's possible to show the grades scales that are used in my courses instead of the percentage based grades? I am using the code below and any help would be much appreciated.

Thanks

Richard

 

SQL code:

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

 

Hi, Moodlers !

I just installed this plugin, and started to query.

my first attempt was :

SELECT u.username AS 'matricule', u.lastname AS 'Nom', u.firstname as 'Prenom', DATE_FORMAT(FROM_UNIXTIME(uid.data), '%d-%m-%Y') AS 'dt_entree'
FROM prefix_user AS u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
WHERE uid.data = 1383519600
ORDER BY u.lastname ASC

uid.data is a custom profile field with a date (without time)

I noticed 2 'odd' things :

when running this query as it is upthere, no problem.

If I change the 'dt_entree' with something that has space (like 'dt entree'), the query does not run anymore.

If I want to display the uid.data as '%d/%m/%Y', the query doesn't run either, but changing it to '%d-%m-%Y' make it run correctly again...

Is it mySQL related, or has to do with the plugin ? (I use to query MS Sql databases, and never noticed that kind of thing....)

My Moodle is 2.5.1+ and everything (server, platform, browser, etc.) is full-french-featured system... Regional settings also use "/" as date delimiter.

Any idea ? Thanks for every contribution !

Hi, was wondering if anyone could maybe get me started in listing a users activities and also wether those activities are completed or not.

Also have another question - I see that all the ad-hoc reports are for site-wide users, how can i target only the signed in, current user?

Thanks!

Hi Tim, i'm a developer who is new to Moodle, i recently installed your plugin (which works great)
and tried to do an UPDATE query. I got the error saying i can't perform such queries (UPDATE, DELETE etc...) and was wondering if this is blocked by the plugin itself or is it something the Moodle system just does not allow?