Useful Queries for Moodle

I've come to compile a list of queries for Moodle, aggregrated from various sources on the internet. Credits to the original authors, whomever they be. (sorry for not keeping track)

# Top 20 courses in terms of student enrolment for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.fullname, c.shortname, count( usr.id )
FROM mdl_course c
INNER JOIN mdl_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
INNER JOIN mdl_role r ON ra.roleid = r.id
INNER JOIN mdl_user usr ON ra.userid = usr.id
WHERE r.name = 'Student' and c.idnumber like '%\_2010%'
GROUP BY c.fullname
ORDER BY count( usr.id ) DESC
LIMIT 0 , 20


# List of Lecturers and how much courses they teach
SELECT user.firstname, user.lastname, user.email, count( role.id ) AS courses
FROM mdl_course course
JOIN mdl_context context ON ( course.id = context.instanceid
AND context.contextlevel =50 )
JOIN mdl_role_assignments assign ON ( context.id = assign.contextid )
JOIN mdl_role role ON ( assign.roleid = role.id )
JOIN mdl_user user ON ( assign.userid = user.id )
WHERE role.shortname = 'editingteacher'
GROUP BY user.firstname, user.lastname, user.email
ORDER BY user.lastname, user.firstname

# Courses with MMS streaming media for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.shortname
FROM mdl_resource r, mdl_course c
WHERE r.reference LIKE '%mms://%'
AND c.shortname LIKE '%\_2010%'
AND r.course = c.id
#List of courses and number of activities for specfic year or term (assuming year code is built in to courses) (sorted by total num_activities )
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_wiki
GROUP BY course) AS courseCount
GROUP BY courseCount.course) AS cCount ON cCount.course = m.id
WHERE m.shortname LIKE '%\_2010%'
ORDER BY cCount.totalcount ASC

Deploying a loadbalanced Apache front end

This particular configuration is being used to support a large Moodle Installation.
The front end comprises of:
1 Loadbalancer
3 webservers
The load balancer virtual IP

Prerequisites:
Fully built and configured CentOS Apache-based webservers
One clean CentOS server to act as loadbalancer

IP's
Webserver 1- 192.168.100.1
Webserver 2 - 192.168.100.2
Webserver 3- 192.168.100.3
Loadbalancer - 192.168.100.5
Virtual IP- 192.168.100.10

On Each Webserver

1.         Open Terminal Interface
2.         Create  specific test file on each webserver to allow the Loadbalancer to check.
echo foo >  /var/www/html/test.html

3.        Create unique identifiers on each webserver to ensure the load balancing algorithm works. These files will be deleted later on.
On each webserver x (replace x with associated number)
echo "This is  Webserver X" > /var/www/html/index.html

4.         Create a loopback interface with the virtual IP to terminate on each webserver:
nano /etc/sysconfig/network-scripts/ifcfg-lo:0
DEVICE=lo:0
IPADDR=192.168.100.20
NETMASK=255.255.255.255
ONBOOT=yes
NAME=loopback

5.        Configure kernel to announce ARP requests/send responses
nano /etc/sysctl.conf
Modify the following entries
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.eth0.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.eth0.arp_announce = 2

6.        Reload modified kernel parameters and bring up loopback:
sysctl -p
ifup lo:0

On the Loadbalancer

1.        Open command line
2.        Install necessary packages
yum install -y heartbeat heartbeat-ldirectord ipvsadm

3.         Configure ldirectord and heartbeat in autostart list.
chkconfig --add ldirectord
chkconfig --del heartbeat
4.     Modify kernel to allow IP forwarding
nano /etc/sysctl.conf
find the following parameter:
net.ipv4.ip_forward = 0
change the parameter:
net.ipv4.ip_forward = 1
5.     Reload modified kernel parameter:
sysctl -p

6.        Configure secondary ethernet interface for Virtual IP:
nano /etc/sysconfig/network-scripts/ifcfg-eth0:0
DEVICE=eth0:0
BOOTPROTO=none
ONBOOT=yes
HWADDR=3a:5d:23:ad:67:47 <>
NETMASK=255.255.255.0
IPADDR=192.168.100.10
GATEWAY=192.168.100.1
TYPE=Ethernet



7.        Create ldirector configuration file:
nano /etc/ha.d/ldirectord.cf
checktimeout=10
checkinterval=2
autoreload=no
logfile="/var/log/ldirectord.log"
quiescent=no
virtual=192.168.100.10:80
               real=192.168.100.1:80 gate
               real= 192.168.100.2:80 gate
               real= 192.168.100.3:80 gate
               service=http
               request="test.html"
               receive="foo"
               scheduler=wlc
               protocol=tcp
               checktype=negotiate
8.         Restart the ldirectord service:
service ldirectord restart
9.     Test the configuration:
ipvsadm -l
Expected output:
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
 -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  yoursite.com:http wlc
 -> web03:http   Route   1      412        0
 -> web02:http   Route   1      411        0
 -> web01:http   Route   1      411        0


That should be it! Your DNS may need to be configured to point your external IP to the Virtual IP.

-n