Automating Moodle Cohort Access Expiry: Add User by Email and Remove After 1 Year

Managing Moodle access manually works for a few users, but it becomes risky when memberships are time-based. For example, if a user is added to a Moodle cohort called OneMembership, and that membership should expire after 1 year, you should not depend on manual reminders.

A better approach is to automate it.

In this tutorial, we will create a complete Moodle cohort automation workflow:

1. Add a user to a Moodle cohort using email ID.
2. Track the cohort membership date using Moodle’s cohort_members.timeadded.
3. Find users whose membership is older than 1 year.
4. Remove expired users from the cohort using Moodle Web Service API.
5. Run the script automatically using cron.

Moodle provides web service functions for cohort operations such as adding cohort members, getting cohort members, and deleting cohort members. These include core_cohort_add_cohort_members, core_cohort_get_cohort_members, and core_cohort_delete_cohort_members. (Moodle Docs) Moodle REST API calls normally use /webservice/rest/server.php with parameters like wstoken, wsfunction, and moodlewsrestformat=json. (Moodle Docs)

---

Use Case

Assume we have a Moodle cohort called:

OneMembership

From the Moodle URL:

https://www.devopsschool.xyz/cohort/assign.php?id=122&returnurl=...

The cohort ID is:

122

So our target cohort is:

Cohort Name: OneMembership
Cohort ID: 122

The goal is:

When a user has completed 1 year from the date they were added to the OneMembership cohort, automatically remove that user from the cohort.

---

Part 1: Add User to Moodle Cohort by Email

Moodle cohort API does not directly add a user by email. It needs the Moodle user ID.

So the process is:

Email ID → Find Moodle User ID → Add User ID to Cohort

---

Step 1: Get Moodle User ID from Email

Use this API call:

curl -X POST "https://www.devopsschool.xyz/webservice/rest/server.php" \
  -d "wstoken=YOUR_MOODLE_TOKEN" \
  -d "wsfunction=core_user_get_users_by_field" \
  -d "moodlewsrestformat=json" \
  -d "field=email" \
  -d "values[0]=USER_EMAIL_HERE"

Example:

curl -X POST "https://www.devopsschool.xyz/webservice/rest/server.php" \
  -d "wstoken=YOUR_MOODLE_TOKEN" \
  -d "wsfunction=core_user_get_users_by_field" \
  -d "moodlewsrestformat=json" \
  -d "field=email" \
  -d "values[0]=student@example.com"

Expected response:

[
  {
    "id": 12345,
    "username": "student",
    "email": "student@example.com",
    "firstname": "Student",
    "lastname": "Example"
  }
]

Here the Moodle user ID is:

12345

---

Step 2: Add User to OneMembership Cohort

Use core_cohort_add_cohort_members.

curl -X POST "https://www.devopsschool.xyz/webservice/rest/server.php" \
  -d "wstoken=YOUR_MOODLE_TOKEN" \
  -d "wsfunction=core_cohort_add_cohort_members" \
  -d "moodlewsrestformat=json" \
  -d "members[0][cohorttype][type]=id" \
  -d "members[0][cohorttype][value]=122" \
  -d "members[0][usertype][type]=id" \
  -d "members[0][usertype][value]=12345"

Replace:

YOUR_MOODLE_TOKEN
12345

with your actual Moodle token and user ID.

---

Part 2: Why We Need Expiry Automation

When users are added manually to a cohort, it is easy to forget who should be removed later.

For membership-based learning platforms, this creates problems:

User paid for 1 year
User added to cohort
1 year completed
But user still has access

This can cause:

• Unauthorized course access
• Revenue leakage
• Manual admin burden
• Confusion in course enrolments
• Incorrect active user reports

So instead of manually checking users, we can automate the removal.

---

Part 3: How Moodle Stores Cohort Membership Date

Moodle stores cohort membership records in the cohort members table. The table links users to cohorts; Moodle schema references describe cohort_members as the table that links a user to a cohort. (examulator.com)

Usually the table is:

mdl_cohort_members

Important fields:

cohortid
userid
timeadded

The timeadded field stores when the user was added to the cohort.

Before writing automation, verify your table structure:

DESCRIBE mdl_cohort_members;

You should see fields similar to:

id
cohortid
userid
timeadded

If your Moodle table prefix is different, replace mdl_ with your actual prefix.

---

Part 4: SQL to Find Expired OneMembership Users

This query finds users who were added to cohort ID 122 more than 1 year ago.

SELECT 
    cm.cohortid,
    c.name AS cohort_name,
    u.id AS userid,
    u.email,
    FROM_UNIXTIME(cm.timeadded) AS added_on
FROM mdl_cohort_members cm
JOIN mdl_cohort c ON c.id = cm.cohortid
JOIN mdl_user u ON u.id = cm.userid
WHERE cm.cohortid = 122
  AND cm.timeadded <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR));

Example output:

cohortid | cohort_name   | userid | email               | added_on
122      | OneMembership | 12345  | student@example.com | 2025-06-01 10:30:22

This means the user has completed 1 year in the cohort and should be removed.

---

Part 5: Remove One User from Moodle Cohort Using API

Use core_cohort_delete_cohort_members.

curl -X POST "https://www.devopsschool.xyz/webservice/rest/server.php" \
  -d "wstoken=YOUR_MOODLE_TOKEN" \
  -d "wsfunction=core_cohort_delete_cohort_members" \
  -d "moodlewsrestformat=json" \
  -d "members[0][cohortid]=122" \
  -d "members[0][userid]=12345"

If successful, Moodle may return an empty response or no error.

---

Part 6: Full Automation Script

Now let us create a script that:

1. Connects to the Moodle database.
2. Finds users in OneMembership older than 1 year.
3. Calls Moodle API to remove each expired user.
4. Logs every action.

Create a script file:

mkdir -p /home/scripts
nano /home/scripts/remove_expired_onemembership.sh

Paste this script:

#!/bin/bash

# Moodle details
MOODLE_URL="https://www.devopsschool.xyz"
TOKEN="YOUR_MOODLE_TOKEN"
COHORT_ID="122"

# Moodle database details
DB_HOST="localhost"
DB_NAME="YOUR_MOODLE_DB_NAME"
DB_USER="YOUR_DB_USER"
DB_PASS="YOUR_DB_PASSWORD"
DB_PREFIX="mdl_"

# Log file
LOG_FILE="/home/scripts/remove_expired_onemembership.log"

echo "===== Run started: $(date) =====" >> "$LOG_FILE"

mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -N -B -e "
SELECT 
    u.id,
    u.email,
    FROM_UNIXTIME(cm.timeadded)
FROM ${DB_PREFIX}cohort_members cm
JOIN ${DB_PREFIX}user u ON u.id = cm.userid
WHERE cm.cohortid = ${COHORT_ID}
  AND cm.timeadded <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR));
" | while read USER_ID EMAIL ADDED_DATE ADDED_TIME
do
    echo "Expired user found: $EMAIL | UserID: $USER_ID | Added: $ADDED_DATE $ADDED_TIME" >> "$LOG_FILE"

    RESPONSE=$(curl -s -X POST "$MOODLE_URL/webservice/rest/server.php" \
      -d "wstoken=$TOKEN" \
      -d "wsfunction=core_cohort_delete_cohort_members" \
      -d "moodlewsrestformat=json" \
      -d "members[0][cohortid]=$COHORT_ID" \
      -d "members[0][userid]=$USER_ID")

    echo "Remove API response for $EMAIL: $RESPONSE" >> "$LOG_FILE"
done

echo "===== Run completed: $(date) =====" >> "$LOG_FILE"
echo "" >> "$LOG_FILE"

Save and exit.

Now make it executable:

chmod +x /home/scripts/remove_expired_onemembership.sh

Run it manually:

/home/scripts/remove_expired_onemembership.sh

Check the log:

cat /home/scripts/remove_expired_onemembership.log

---

Part 7: Safer Version with Dry Run Mode

Before removing real users, it is better to test with a dry run.

Create another safer version:

nano /home/scripts/remove_expired_onemembership_dryrun.sh

Paste this:

#!/bin/bash

MOODLE_URL="https://www.devopsschool.xyz"
TOKEN="YOUR_MOODLE_TOKEN"
COHORT_ID="122"

DB_HOST="localhost"
DB_NAME="YOUR_MOODLE_DB_NAME"
DB_USER="YOUR_DB_USER"
DB_PASS="YOUR_DB_PASSWORD"
DB_PREFIX="mdl_"

DRY_RUN="true"

LOG_FILE="/home/scripts/remove_expired_onemembership_dryrun.log"

echo "===== Dry run started: $(date) =====" >> "$LOG_FILE"

mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -N -B -e "
SELECT 
    u.id,
    u.email,
    FROM_UNIXTIME(cm.timeadded)
FROM ${DB_PREFIX}cohort_members cm
JOIN ${DB_PREFIX}user u ON u.id = cm.userid
WHERE cm.cohortid = ${COHORT_ID}
  AND cm.timeadded <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR));
" | while read USER_ID EMAIL ADDED_DATE ADDED_TIME
do
    echo "Would remove: $EMAIL | UserID: $USER_ID | Added: $ADDED_DATE $ADDED_TIME" >> "$LOG_FILE"

    if [ "$DRY_RUN" = "false" ]; then
        RESPONSE=$(curl -s -X POST "$MOODLE_URL/webservice/rest/server.php" \
          -d "wstoken=$TOKEN" \
          -d "wsfunction=core_cohort_delete_cohort_members" \
          -d "moodlewsrestformat=json" \
          -d "members[0][cohortid]=$COHORT_ID" \
          -d "members[0][userid]=$USER_ID")

        echo "Remove API response for $EMAIL: $RESPONSE" >> "$LOG_FILE"
    fi
done

echo "===== Dry run completed: $(date) =====" >> "$LOG_FILE"
echo "" >> "$LOG_FILE"

Make it executable:

chmod +x /home/scripts/remove_expired_onemembership_dryrun.sh

Run:

/home/scripts/remove_expired_onemembership_dryrun.sh

Check dry-run results:

cat /home/scripts/remove_expired_onemembership_dryrun.log

Once everything looks correct, change:

DRY_RUN="true"

to:

DRY_RUN="false"

---

Part 8: Automate with Cron

Open crontab:

crontab -e

To run daily at 1 AM:

0 1 * * * /home/scripts/remove_expired_onemembership.sh

To run every hour:

0 * * * * /home/scripts/remove_expired_onemembership.sh

Daily is usually enough for membership expiry. Hourly is useful if you want expiry to happen closer to the exact completion time.

---

Part 9: Required Moodle Web Service Setup

In Moodle admin, make sure Web Services and REST protocol are enabled. Moodle’s official web service guide describes creating an external service, adding functions to it, generating a token, and calling the service using POST requests. (Moodle US)

Your Moodle service token should have access to these functions:

core_user_get_users_by_field
core_cohort_add_cohort_members
core_cohort_get_cohort_members
core_cohort_delete_cohort_members

For this automation, the most important one is:

core_cohort_delete_cohort_members

Without this function, the removal API call will fail.

---

Part 10: Recommended Folder Structure

A clean structure:

/home/scripts/
├── remove_expired_onemembership.sh
├── remove_expired_onemembership_dryrun.sh
└── remove_expired_onemembership.log

Recommended permissions:

chmod 700 /home/scripts/remove_expired_onemembership.sh
chmod 700 /home/scripts/remove_expired_onemembership_dryrun.sh

Do not expose this script publicly because it contains database credentials and Moodle API token.

---

Part 11: Troubleshooting

1. API returns invalid token

Check:

wstoken

Make sure the token is correct and belongs to a user who has permission to manage cohorts.

---

2. API function not allowed

If you see an error saying the function is not available, add this function to your Moodle external service:

core_cohort_delete_cohort_members

---

3. No users are removed

Run the SQL manually:

SELECT 
    u.id,
    u.email,
    FROM_UNIXTIME(cm.timeadded) AS added_on
FROM mdl_cohort_members cm
JOIN mdl_user u ON u.id = cm.userid
WHERE cm.cohortid = 122;

This will show all users in the cohort and their added date.

Then check expired users:

SELECT 
    u.id,
    u.email,
    FROM_UNIXTIME(cm.timeadded) AS added_on
FROM mdl_cohort_members cm
JOIN mdl_user u ON u.id = cm.userid
WHERE cm.cohortid = 122
  AND cm.timeadded <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 YEAR));

If no rows are returned, no user has completed 1 year yet.

---

4. Wrong table prefix

Some Moodle installations do not use mdl_.

Check your Moodle config.php:

grep prefix /path/to/moodle/config.php

Example:

$CFG->prefix = 'mdl_';

Use that prefix in your script.

---

5. Database login issue

Test database login manually:

mysql -h localhost -u YOUR_DB_USER -p YOUR_MOODLE_DB_NAME

If login fails, fix DB username, password, host, or database name.

---

Part 12: Best Practices

Do not directly delete users from mdl_cohort_members using SQL. Use SQL only for reading and reporting. Use Moodle’s official web service API to remove users from cohorts because API-based changes are safer and more compatible with Moodle’s internal processes.

Also:

• Test first on staging.
• Run dry-run mode before enabling real removal.
• Keep logs.
• Use a dedicated Moodle API user.
• Give that API user only the minimum required permissions.
• Protect the script because it contains secrets.
• Backup the database before first production run.

---

Final Workflow Summary

The full automation looks like this:

User buys 1-year membership
        ↓
Admin/API adds user email to Moodle
        ↓
Email is converted to Moodle user ID
        ↓
User is added to OneMembership cohort ID 122
        ↓
Moodle stores cohort membership date
        ↓
Daily cron checks users older than 1 year
        ↓
Expired users are removed using Moodle API
        ↓
User loses cohort-based course access

---

Conclusion

Moodle cohorts are very useful for giving course access to a group of users. But when access is subscription-based, cohort membership should not remain forever.

By combining:

Moodle Web Service API
Moodle cohort_members table
MySQL query
Bash script
Cron job

you can build a clean automation that removes users from the OneMembership cohort exactly after their 1-year access period is completed.

This approach reduces manual work, prevents forgotten access, keeps Moodle clean, and makes your membership system more reliable.