I occasionally use the following SQL statements to clean-up our Moodle system and identify old courses. I recommend backing up your database before using any and knowing enough SQL to understand what they are doing before running them. I usually use phpMyAdmin for execution and export, but use what ever works best for you.
Find Empty Moodle Courses
If you use an auto-enrolment module you will often find you have lots of courses created that are never used. We have used the LDAP and Database enrolment before and both have created large numbers of courses. Some are not used by teachers,others are just used as child courses for year wide meta courses. Either way at the end of the year you may wish to identify any class that hasn’t been used. (Perhaps to delete using the Bulk Deletion options).
No Content
Following SQL code I’ve written will generate a list containing a sum of most of the resources and activities a course has, allowing you to quickly identify those with little to no content. It bases this count on having no labels, resources, assignments, etc.
[code='sql']
SELECT m.id, m.`shortname` , m.fullname, cCount.totalcount
FROM mdl_course m
LEFT JOIN (
SELECT courseCount.course, sum( courseCount.subcount ) AS totalcount
FROM (
SELECT course, count( * ) AS subcount
FROM mdl_resource
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_quiz
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_assignment
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_survey
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_label
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_glossary
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_homework
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_wiki
GROUP BY course
) AS courseCount
GROUP BY courseCount.course
) AS cCount ON cCount.course = m.id
[/code]
You may need to edit above code if you do not have an activity installed (eg homework).
No Users
The following code generates a list of all your courses together with how many students are enrolled in each. Useful to find out if you have any courses with no one enrolled.
[code='sql']
SELECT cr.shortname, cr.fullname, count( ra.id ) AS enrolled
FROM `mdl_course` cr
JOIN `mdl_context` ct ON ( ct.instanceid = cr.id )
LEFT JOIN `mdl_role_assignments` ra ON ( ra.contextid = ct.id )
WHERE ct.contextlevel =50
GROUP BY cr.shortname, cr.fullname
ORDER BY `enrolled` ASC
[/code]
You can export either of the above queries into Excel and manipulate it from there. Order by the counts then copy the short names’ of courses to delete, paste into a text file and upload to the Bulk Course Deletion addon. Goodbye excess courses.
Data Cleanup: Roles without Users
Sometimes when a user gets deleted Moodle doesn’t clean up after it self as well as it should. The following code will list all the rows in your role assignments table that no longer match to a user:
[code='sql']SELECT *
FROM `mdl_role_assignments`
WHERE `userid` NOT
IN (
SELECT id
FROM mdl_user
)
[/code]
If you wish to the delete all of these just run following. As always make sure you have a good backup before deleting anything from DB.
[code='sql']DELETE
FROM `mdl_role_assignments`
WHERE `userid` NOT
IN (
SELECT id
FROM mdl_user
)
At the recent CC day some people asked about bulk deletion of users and courses. Moodle 1.9 beta now supports bulk user operations, such as deleting, sending messages or confirming the account. You can use a filter to easily select the users you want. The Filter allows selection based on everything from name, last access, role, authentication system (manual, LDAP, etc.) to email as well as many other options. Hopefully it will be upgraded in the future to allow filtering by other user attributes such as ID number, Departments, Institution or the new attributes that can be added to users. For Bulk Deletion of courses there is a php file posted by Jeff Church which you can place somewhere in your Moodle installation and run manually to delete your courses. It takes a text file of the courses’ short names to delete, then confirms what to remove before deleting them all. I ran it recently to remove 323 courses from 2005-2006 and it removed them with no problems.
This was very similar to a previous presentation given as part of the Tech KNOW Tour. However the Using Moodle presentation has some slight additions from Paul for a more technical audience and the spoken part of my presentation concentrated on a more technical side.
Some additional notes:
To use the DET mail server you can either add mail.det.nsw.edu.au to the mail section of your php.ini file or add it to the
Admin -> Server -> Email: SMTP Hosts section.
If you wish to restrict to sending to DET emails you can add following to the Allowed email domains : “education.nsw.gov.au det.nsw.edu.au“.
Make sure to test the mail server settings before adding any restrictions to domains.
If using Novell or Mac OS you will need to use a different LDAP browser as AD Explorer only works with Active Directory.
For Novell I usually use the free Windows program Softerra LDAP Browser (MSI).
There are a few Java based LDAP browsers that should work with Mac. A good Java based LDAP browser is JXplorer this should work on Mac, Windows and Linux. For some more info on Mac and LDAP look at this article on LDAP in Mac OS X Server from the Mac Dev Center, it is not fully applicable as it also has home drive mapping but it does contain some useful info.
Paul Ganderton has made his Geography HSC site avaliable for guests. You can also view some of the other course on our Moodle site. Look for this logo which means guest access is allowed.
Bulk Operations: Some people asked about bulk deletion of users and courses.
This section has been moved to Moodle: Bulk Deletion Operations.
Some of the items shown during talk are produced by plugins to Moodle. Below is a list of some of our favourites:
Book: Allows structuring resource pages with chapters and pages.
Gallery: Shows slide shows and generates thumbnails of images. You can upload a zip file containing an entire folder structure of pictures, and auto-generate albums and sub-albums from it.
Course Menu: Creates a Tree Structure as a block on side of page to assist navigation.
On the main SBHS Moodle page there is also a Library block that sends search queries to our Sentral library page. This will need to be customised by schools to point to their Sentral system or the new DET My Library system (only accessible inside DET WAN). Just modify the block_library.php file to point to your Library search page. To install place the library directory in your Moodle blocks folder.
In ConsoleOne it is relatively easy to change the path to an MSI but much more difficult to change the file name of the MSI itself.
A trick is to install a second copy of ConsoleOne without any plugins. (I just have link to Clean ConsoleOne.) Using this version you can then Open the App Object and go to the Other tab, and expand zenappPackageName, double click the filename to change.
I use this quite regularly when updating FrontMotion’s Firefox MSI’s. I just keep two object and alternate between them. One production and one testing.
To change the location of an MSI you need to change 2 locations in ConsoleOne (with Zen plugins):
When using Group Policies with Zenworks and Windows XP you may find users are able to create folders and files in root of C:.
This is due to the change in default security settings for drives on Windows XP from 2000.
You need to use the Security Template editor to create a template restricting rights to the C drive and deploy it with your group policies. The same procedure can be used to create a Security Template for use with Active Directory.
VideoLAN VLC is a great cross platform media player ideal for networks. CODECs for most formats are included with the player so it can be easily deployed without needing extra installers.
As it is self contained it is very easy to repackage as an MSI for deployment or it can run straight off the network without installation (although it is a bit slow to load this way).
VLC runs on Windows, Mac and Linux so it can provide a uniform interface across platforms.
The latest version, 0.8.6d fixes some security problems and improves playback on Mac.
EZ GPO is small utility that allows central power policy management through Group Policies. Saving energy, money and the environment.
Ideal for school computer labs and staff rooms, as well as business.
EZ GPO can be deployed by MSI across the network through either AD or Zenworks. You then add the supplied ADM to a group policy and set the idle times for switching monitor off, and putting computer into sleep, hibernate or standby modes.
One other option of EX GPO that makes it very attractive for laptops, is it can allow limited user accounts to change power settings on Windows 2K / XP. As most users will have experienced, Windows stores power settings in HKLM, so a standard user can not specify times for power saving. This can be very annoying if used during presentations, etc.
EZ GPO has an option that fixes this allowing the changing of the power policy.
Finally one other use for EZ GPO, against what was originally intended. If you have a PC that is locked down, but needs to be on continuously (such as a kiosk or display PC) you can use EZ GPO to prevent visible power saving options from starting. i.e No standby mode, or monitor savings. I would however recommend spinning down HDDs .
which means guest access is allowed.