Databases: Remove users not seen for a while

Databases: Remove users not seen for a while

by Paul Andrews -
Number of replies: 13
Ello.

I've written this little PHP script that "Deletes" users not seen for 7 Months.

Note it doesn't remove the rows from the DB - it just mimics what Moodle does when deleting folks and sets the "Deleted" flag to 1.

This way we can run this script and remove any ex-students and staff from our Moodle - as once they leave their LDAP account is removed and thus they can't login. big grin

Also it means you can un-delete folks if you need to.

Anyway I thought I'd share - feel free to use as you see fit.

To install pop it in a sub directory in your wwwroot folder - this is important as the script picks up the DB connection from Moodle's config.php.

Cheers!

Paul.
Average of ratings: -
In reply to Paul Andrews

Re: Databases: Remove users not seen for a while

by Clare Smith -
Hi Paul

Thanks for this i will try it in my local PC test site! We are just in the process of deciding how to move forward in terms of upgrading and authentication as we have a had a college server test Moodle site running concurrently with Learnwise for a year now and are going with Moodle. We have been using manual authentication by uploading text files - Will this script remove these manually uploaded users also?

We seem to have sussed out the LDAP authentication settings, but short of doing a clean install and restoring backed up courses, I was wondering what to do about the many users in there which will no longer be College students.

When you say you can un-delete users also - do you mean that if the user logs in again (LDAP authenticated) then moodle picks up their previous information because the rows are still present in the database?

Can the 7 month limit be altered - is it just a case of changing the query below to say '3 MONTH' for example?
$query = 'SELECT id FROM mdl_user WHERE `lastaccess` < UNIX_TIMESTAMP( DATE_SUB( UTC_DATE, INTERVAL 7 MONTH ) )

Sorry for the many questions..it's a steep learning curve and it helps to have possible solutions to hand when trying to liaise with our Network Services team!
Many thanks,
Clare
Average of ratings: -
In reply to Clare Smith

please help

by vijesh g -
hi

I am  new to moodle.I want to display something from mysql database to my front end.Can u specify which function should i use for this in moodle.

i see so many functions in lib/blocklib.php but i dont know how to use.

Please give me a small example if possible

Thanks in advance

Regards

Vijesh
Average of ratings: -
In reply to Clare Smith

Re: Databases: Remove users not seen for a while

by Paul Andrews -
Hello Clare!

Sorry for the LONG delay - been a bit busy!

Answers to questions:

"Will this script remove these manually uploaded users also?" - Yes

"I was wondering what to do about the many users in there which will no longer be College students."  - This will fix it.

"When you say you can un-delete users also - do you mean that if the user logs in again (LDAP authenticated) then moodle picks up their previous information because the rows are still present in the database?" - Yes - but the Moodle admin has to go into the Moodle database (use phpmyadmin) and set that users deleted flag to 0 before this will happen.

"Can the 7 month limit be altered - is it just a case of changing the query below to say '3 MONTH' for example?
$query = 'SELECT id FROM mdl_user WHERE `lastaccess` < UNIX_TIMESTAMP( DATE_SUB( UTC_DATE, INTERVAL 7 MONTH ) )" - Yep.  I set it to 7 because Moodle will unenroll folks not seen for 6 months from courses - I wanted this to happen 1st before they are deleted - hence the 7 months.

Hope this helps!

Average of ratings: -
In reply to Paul Andrews

Re: Databases: Remove users not seen for a while

by Reggie Ryan -
Paul,

Your script worked wonders on a 1.6 installation I had. I then tried it on a 1.6.1 install, and got this error:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/glenview/public_html/deletescript/remove_users_nosee_7_months.php on line 27

I've got it in a sub-directory of my wwwroot (public_html), and have not changed a thing.

Any suggestions.... and thanks for the effort!

Reggie
Average of ratings: -
In reply to Reggie Ryan

Re: Databases: Remove users not seen for a while

by Đinh Lư Giang -
Does it work with Moodle 1.7 ?
I tried your script but nothing really happens. Got a page like that:

This script removes all users who haven't been seen for 7 Months

Connected
Got List
Processing UserIDs

and the status is "done".



Average of ratings: -
In reply to Đinh Lư Giang

Re: Databases: Remove users not seen for a while

by Iñaki Arenaza -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Try this one. It deletes (marks as deleted) all the users that haven't logged in any course for 'longtimenosee' (set it under Administration >> Configuration >> Variables).

Place it under any subdirectory of your $CFG->wwwroot.

Saludos. Iñaki.
Average of ratings: -
In reply to Iñaki Arenaza

Re: Databases: Remove users not seen for a while

by Đinh Lư Giang -
Dear Iñaki

Your script is excellent. Thanks so much.

Giang
Average of ratings: -
In reply to Iñaki Arenaza

Re: Databases: Remove users not seen for a while

by Sahmot Nikluag -
Hi -

I was very happy to find and install your version of the script.  But then I noticed some weird things in the course logs with many users having login failures.  I checked the users and though they were still listed as enrolled, their accounts had been deleted.  These were all users who had registered within the last two weeks! 

I checked the tables and for whatever reason these recent users have 0 for their lastlogin (but a normal timestamp for lastaccess).  I don't understand why that would be, but I thought adding a check for lastlogin > 0 would take care of these  happenings, i.e.:

  if ($users = get_records_select("user", "deleted=0 AND lastlogin > 0 AND lastlogin < $longtime"))

I'm not sure I understand why 0 would calculate as > $longtime, but I don't understand all that much about 0 anyway...

And the mystery remains, why did these users have a normal lastaccess time-stamp, but 0 for lastlogin?? My server has had no clock problems.  I did upgrade moodle recently, could that be the problem?
Average of ratings: -
In reply to Sahmot Nikluag

Re: Databases: Remove users not seen for a while

by Sahmot Nikluag -
Correction:

"I'm not sure I understand why 0 would calculate as > $longtime, but I don't understand all that much about 0 anyway..."

Duh: < $longtime. That's what I get for late night posting...

By the way, would using lastaccess be a better test anyway?
Average of ratings: -
In reply to Iñaki Arenaza

Re: Databases: Remove users not seen for a while

by Art Lader -
Uh oh. It worked great but I just I removed the guest user on a test site. Oops!

-- Art
Average of ratings: -
In reply to Art Lader

Re: Databases: Remove users not seen for a while

by Steve Bilton -
Hello,

I was wondering if anyone had a script that will remove all of those users' logs for users that are not enrolled on any courses AND removes the associated logs from the mdl_logs table??? I do not want a simple delete field, I literally want to remove the entire entry and corresponding log entries.

I have over 2000 students with only 400 active enrolled on courses.

My database is way overloading my server and dispite the numberous altercations and optomizations I have made:

I have added the index on "time" in order to speed up query based on this table field for the mdl_logs table. (not much effect however)

increase (doubled from ) TABLE CACHE
SLOW QUERIES changed from 10 seconds to 5 seconds
increased QUERY CACHE
JOINS - increased Current join_buffer_size = 156.00 K

key_buffer_size = 56M
query_cache_size=8M
sort_buffer_size=2M
read_rnd_buffer_size=512
join_buffer_size=2M
tmp_table_size=40M
max_heap_table_size=40M
table_cache=156
max_seeks_for_key=500

So the MySQL access is now lightening fast (95%) of the time, but hangs due to the large amount of requests to the mdl_logs table.

I want to reduce the size of my table by removing approximately 1000 users (min) and their corresponding mdl_logs entries.

I want to check for unenrolled students compare with last access time (if less than 6 months) and delete the entries in the mdl_logs for that user.

As an alternative I would like a removal script of specified user/course id's so i can input these manually and then run the associated logs removal.

Any help would be great!!

Thanks
Steve
Average of ratings: -
In reply to Iñaki Arenaza

Re: Databases: Remove users not seen for a while

by Manuel García -
Hi!, here is an update of your script, this execute exactly the same process that is follow by the regular way of users deletion. This change the delete field into 1, timemodified is updated with the current time, username is updated with email and timemodified values, email is leaved in blank and it cleans all the role_assignments entries for the user.

It was tested in 1.8.4. Try it!

Regards.

MG
Average of ratings: -