View Issue Details

IDProjectCategoryView StatusLast Update
0003716mantisbtsqlpublic2006-05-12 17:45
Reporterhacker Assigned To 
PrioritynormalSeverityfeatureReproducibilityalways
Status acknowledgedResolutionopen 
PlatformIntelOSLinuxOS VersionDebian Unstable
Product Version0.18.2 
Summary0003716: Add a function to the "Manage Users" page to purge users older than 'n' days/years
Description

Currently, our system has 935 users in it, most of which who have never reported bugs at all, but are community people interested in development and bugs filed.

So I came up with a quick query to purge users older than 1 year, who have only ever logged in once, from the database.

Steps To Reproduce

SELECT id,username,login_count,last_visit
FROM mantis_user_table
WHERE login_count = 1
AND last_visit < CURDATE() - INTERVAL 1 YEAR
ORDER BY last_visit DESC;

Additional Information

Note, this does not take into account that any of these users may have logged in once, reported a bug (or added a bugnote to an existing bug), and never logged in again.

I will try to update the query to handle that situation and post a patch here, unless someone else wants to give it a go.

Enjoy.

TagsNo tags attached.

Activities

hacker

hacker

2004-04-03 09:39

reporter   ~0005338

Actually, I just realized MySQL has a NOW() function:

NOW() - INTERVAL 1 YEAR

hacker

hacker

2004-04-03 10:36

reporter   ~0005339

Slightly updated query to handle some of this, but NOT yet sanitized to KEEP users who are REPORTER on all projects, but DEVELOPER on some other projects. Stay tuned as I figure that one out:

SELECT A.id,A.username
FROM mantis_user_table A
LEFT JOIN mantis_bug_table B
ON A.id=B.reporter_id
WHERE B.reporter_id is NULL
ORDER BY id DESC;

It looks like we also have to go through mantis_project_user_list_table, and make sure u.username doesn't match A.username above, and that l.access_level is = 25.

Fun fun!

grangeway

grangeway

2004-08-27 13:06

reporter   ~0007244

a Purge utility should only purge users who have never generated an event though - e.g. not created a bug, added a bug note etc.

hacker

hacker

2006-05-12 17:45

reporter   ~0012827

Here's how I managed to do this...

SELECT u.id, u.last_visit
FROM mantis_user_table u
LEFT JOIN mantis_bug_table b
ON u.id = b.reporter_id
LEFT JOIN mantis_bugnote_table c on u.id = c.reporter_id
WHERE b.reporter_id IS NULL
AND c.reporter_id IS NULL
AND u.last_visit < '2004-01-01';

Then:

DELETE FROM u using mantis_user_table u L
EFT JOIN mantis_bug_table b
ON u.id = b.reporter_id
LEFT JOIN mantis_bugnote_table c on u.id = c.reporter_id
WHERE b.reporter_id IS NULL
AND c.reporter_id IS NULL
AND u.last_visit < '2004-01-01';

This worked out great for me.