Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Cleanup orphaned & spam usersCleanup orphaned & spam users
Previous
 
Next
New Post
10/13/2015 7:02 PM
 

Can anybody give me some pointers on how to find and remove users that are 1) left over from the spam attacks DNN sites suffered some months ago, and 2) user accounts that have not been accessed for a long period?

I have multiple sites, each with approx 3000 user accounts and I don not want to have to go through each one a row at a time.

The one thing going for me is that each user account should only list New Zealand as their country, which will help filter many of the spam one's out.

 

TIA

MartyNZ

 
New Post
10/14/2015 2:33 AM
 

I have used this type of query to find inactive users or in my terms users who have created an account and not logged in at all or logged once and not since:

SELECT *

FROM Users INNER JOIN

aspnet_Membership INNER JOIN

aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId ON Users.Username = aspnet_Users.UserName INNER JOIN

UserPortals AS UserPortals_1 ON Users.UserID = UserPortals_1.UserId

WHERE DATEDIFF(mi, aspnet_Membership.CreateDate, aspnet_Users.LastActivityDate) <5  AND Users.UserId<>4

ORDER BY LastLoginDate desc

Note that part on red above. There might be some userid's that you don't want to mark unauthorized so list all of those there.

What this does it finds all users who have created an account and not done anything or done something within 5 minutes after that and after that have had no activity. You can change this number to something else as well.

After verifying that these are the users I want to remove, I have enhanced the query to:

UPDATE UserPortals

SET Authorised = 0

WHERE (UserId IN

(SELECT DISTINCT UserPortals_1.UserId

FROM Users INNER JOIN

aspnet_Membership INNER JOIN

aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId ON Users.Username = aspnet_Users.UserName INNER JOIN

UserPortals AS UserPortals_1 ON Users.UserID = UserPortals_1.UserId

WHERE DATEDIFF(mi, aspnet_Membership.CreateDate, aspnet_Users.LastActivityDate) <5)) AND UserId<>4

Changed the SELECT query a bit (yellow part) not to hit too many times same user.

After executing UPDATE statement your inactive users will be marked as Unauthorized and you can review those once more in DNN user accounts management UI and delete those from there. That will take care of that everything will be cleared correctly from the database.

Before running the UPDATE statement verify the SELECT part that correct users are really selected.

Furthermore, you can also enhance SELECT part to include some date range like 

AND (aspnet_Users.LastActivityDate < CONVERT(DATETIME, '2010-12-31 23:53:00', 120) AND aspnet_Users.LastActivityDate > CONVERT(DATETIME, '2010-10-30 23:53:00', 120))

Just pick your favorite date & times on above.

 

 

 

 

 

 
New Post
10/14/2015 3:55 AM
 
you might need to remove the user folders manually as well.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
10/14/2015 4:23 AM
 
True, that is sort of bug in Dnn. I think it was cleaning the possible profile related files, but not the actual user directory.
 
New Post
10/14/2015 6:11 AM
 
if you delete all empty folder inside /users, you will remove (empty) folders of existing users as well - but there is no harm, as they get recreated upon first login.
However, a DNN full file sync will remove folder entries and folderpermission entries, which may slow down your system significantly, if there are tenthousands.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Cleanup orphaned & spam usersCleanup orphaned & spam users


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out