I think I have something that if not perfect, at least covers what I need.
I realised 3 things:
1. Everyone has the role 'authenticated user' until you eliminate all other roles, then no one does.
2. Everyone is enrolled in the course with id=1 (the Front page), until you eliminate all other courses, then no one is enrolled.
3. Moodle does not seem to want to admit that someone who has never logged in could be enrolled in a class.
I'd love to be wrong on those.
This query returns everyone who has never logged in and was created before the oldest of the regular courses was deleted, so they would be in no currently available academic courses. My guess is that if they weren't modified after they were created, they won't have any active classes on the server. The date shown is 01/01/15 at 00:00 US Central Time
SELECT
id,
username,
firstname,
lastname,
email,
FROM_UNIXTIME (firstaccess, '%d/%m/%Y') AS 'First Access',
FROM_UNIXTIME (lastaccess, '%d/%m/%Y') AS 'Last Access',
FROM_UNIXTIME (lastlogin, '%d/%m/%Y') AS 'Last Login',
FROM_UNIXTIME (timecreated, '%d/%m/%Y') AS 'Time Created',
FROM_UNIXTIME (timemodified, '%d/%m/%Y') AS 'Time Modified'
FROM prefix_user
WHERE lastaccess=0
AND timecreated < 1420092000
AND timecreated=timemodified
ORDER BY lastname, firstname
This one shows the classes that people who were created and were last modified before my cut-off date. This brought me a list of inactive clubs and groups. Given that this list is very short, we can check for content before deleting.
SELECT DISTINCT
u.username AS 'User',
u.firstname AS 'First Name',
u.lastname AS 'Last Name',
FROM_UNIXTIME (u.lastaccess, '%m/%d/%Y') AS 'Last User Access',
c.id AS 'COURSE ID',
c.fullname AS 'Course Name',
FROM_UNIXTIME (c.timecreated, '%m/%d/%Y') AS 'Date Course Created'
FROM prefix_course AS c
JOIN prefix_course_categories AS ca
ON c.category = ca.id
JOIN prefix_user_lastaccess AS la
ON c.id = la.courseid
JOIN prefix_user AS u
ON la.userid = u.id
JOIN prefix_context AS ctx
ON c.id = ctx.instanceid
INNER JOIN prefix_role_assignments AS ra
ON ra.contextid = ctx.id
WHERE ctx.instanceid = c.id
AND u.timecreated < 1420092000
AND u.timecreated=u.timemodified
AND u.lastaccess < 1420092000
ORDER BY u.lastname, u.firstname