mass deletion of users that have never logged into site

Re: mass deletion of users that have never logged into site

by Saujanya Patel -
Number of replies: 6
The following query should help find people who've never logged in: 
select id, concat(firstName," " ,lastName) as name, username, date_format(from_unixtime(lastaccess), '%Y-%m-%d') as lastAccess,
date_format(from_unixtime(lastlogin), '%Y-%m-%d') as lastLogin, date_format(from_unixtime(currentlogin), '%Y-%m-%d') as currentLogin
from user
where (currentlogin='0' and lastlogin='0');
I'm trying to bulk delete users from the user table who have never logged in, but I'm not sure what tables their id/userid exist in... any hints?
Average of ratings: -
In reply to Saujanya Patel

Re: mass deletion of users that have never logged into site

by Dale Davies -
Cheers, I fiddled with this a bit and added "deleted=0" to the WHERE clause too...

SELECT id, concat( firstname, " ", lastname ) AS name, lastaccess, lastlogin, currentlogin
FROM mdl_user
WHERE currentlogin = 0
AND lastlogin = 0
AND lastaccess = 0
AND deleted = 0

This seems to bring up the same results as the Browse Users section in Moodle.

Also, I guess if you wanted to delete these then you'd need to use the following SQL...

UPDATE mdl_user
SET deleted=1
WHERE currentlogin = 0
AND lastlogin = 0
AND lastaccess = 0
AND deleted = 0
Average of ratings: -
In reply to Dale Davies

Re: mass deletion of users that have never logged into site

by Dale Davies -
Just thought Id best add that of course this wont actually remove the users, Im actually looking for a way to do that. Could do with pruning the database slightly!

Would be really interested to hear if anyone know what tables would also need to be edited.

Just thinking out loud here.... Entries such as grades could be removed perhaps, but conversations in chat history, forums and messages etc would need to be attributed to a different user (a special user perhaps with the name "This user has been deleted").

I might do an audit of the tables and see if I can identify what would need to be done, I'll be back!
Average of ratings: -
In reply to Dale Davies

Re: mass deletion of users that have never logged into site

by James Oxnam -
To mass delete users, use the Bulk user actions under Accounts.

Just follow your nose, so to speak.

Nga mihi nui,

James

--------------------------------------------------
James Oxnam

Flexible Learning Systems Support
Education Services
Nelson Marlborough Institute of Technology
--------------------------------------------------

Average of ratings: -
In reply to James Oxnam

Re: mass deletion of users that have never logged into site

by James Lagnese -
Bulk actions does not allow you to just choose "Never" only. If I have to pick a date and include never, it will include everyone. It would be nice just to be able to pick "Never" and cull that list of users.
Average of ratings: -
In reply to James Lagnese

Re: mass deletion of users that have never logged into site

by Tony Dod -

Using these filters I have just successfully found unused accounts, then was able to delete them using the bulk user option:

First access is before Sunday, 1 January 1995, 12:00 AM (Never included)

Last access is before Thursday, 18 May 1995, 12:00 AM (Never included)

Last Login is before Thursday, 18 May 1995, 12:00 AM (Never included)

Average of ratings: -
In reply to Tony Dod

Re: mass deletion of users that have never logged into site

by Nancy K Hoke -
Tony - Hi - I am trying to pull up all accounts with Never in the login field. I tried using your setup - and I am a little confused. I am in Moodle 1.9 and in order to get the field for Never Included - there must be a second date for First Access is After and Last Access is After and Last Login is After. I tried making the dates the same in both fields and the search did not work. Thank you so very much for any help you can provide. Nancy K. Hoke - Khalifa University
Average of ratings: -