User Deleted, Posts remain, how to remove?

[GB_Techie]

Hello,

I've had someone sign up for an account, give the details to multiple users and then the account was deleted.  I'm trying to find all of the IPs associated with the user and the only way I can figure out how to do that is to look at the IPs of their posts. 

Is there a way to find all of a deleted users posts?  Or another way to find all of the associated IP addresses?

Thanks.

[CapadY]

I think the only way is using a MySQL order in PHPMyAdmin.
I can't check the right database fieldnames at this moment but it should more or less look like this:

Select distinct  ip_adress from posts where user_id is X

For x use the proper user id

It will result a list of all used IP-adresses used by that user-id

[Arantor]

Well... the posts table is called smf_messages, the poster's IP is in poster_ip, but the user's id is removed when their account is deleted, so you're literally looking at DELETE FROM smf_messages WHERE poster_ip = 'x.y.z.a' and you will need to run recount all stats from maintenance after doing it.

Probably a quicker way would be to create a new account, reattach all the deleted posts to the new account, then delete the new account, only this time selecting to delete all their posts.

[GB_Techie]

How do I know what the user ID or email address were to reattach with?  I don't know that the end display name is the user id, and I have no idea what the email address was. 

If I do manage to reattach the posts, will it show the original IP address posted from?

I have several personal messages that were reported to admin, is there anyway I can tell the IP of those messages?

The account was created and deleted before I had any access to it.  I don't want to delete the posts, just ban those IPs that were used to make the posts.

[Arantor]

Browse for some of the posts... The username used will be posted in thread, there will be a link underneath for emailing and it should have the IP address in the bottom right corner of the post.

[GB_Techie]

The problem with that is that it's many users who have posted using the account, a breach of our rules so I want to get all of the different IPs.  Only way to do that is to search for all posts by that user but can't find a way to do that as we have over 1.2 million posts.

[rugrat]

Can you open your database file and search the user in there? That would show the username in each post and beside it the IP address.

[GB_Techie]

I haven't tried that because Arantor said above that the user id wouldn't be there any more?  If I've gotten that wrong, can you give me step by step for phpmyadmin?

Cheers!

[Arantor]

The user *id* isn't, but the user *name* is.

[GB_Techie]

Aha...okay, what do I do?  I can navigate okay in phpmyadmin and have run the odd query, but usually with detailed instructions!

p.s. love the sig line.

[Arantor]

Well, go into phpMyAdmin, find smf_messages then...

SELECT * FROM smf_messages WHERE poster_ip = 'x.y.z.a';

[rugrat]

I'm a noob here myself so I may be talking out my arse So take it for what it is worth.

I would think as Arantor suggested, creating a new member and in Admin > Maintenance > Forum Maintenance > Members, Reattribute User Posts would do about the same. Then you could then just view all post by that member and gather the IP's.

I'm not familiar with phpmyadmin, I just pull my database to my computer and search that way. Yours may be too big to do that though.

[Arantor]

Well, that would have been the plan if it was only a single account being troublesome; this sounds like it's multiple accounts that have all been deleted, so matching by IP is probably better.

[live627]

What about matching by guest from a specific date? Or even an email?

[GB_Techie]

It is just a single account, but the password for the account was shared with a lot of people so it has multiple IP addresses associated.  If doing a reassign will mean that I can look at all the posts and they'll have the original IP addresses then that's great, but I don't know what the original user id or email address were for the deleted account.  Is there a way of finding that out?  The account was live for about 5 days.

Database is 300mb tarred and zipped so I think it'd have to be phpmyadmin.

[live627]

Well then I think the easiest solution is to delete all guest postings from now to six days ago.

DELETE FROM smf_members WHERE poster_time > 1298760324 AND id_member = 0;

You have a backup in case something went wrong? Please say yes...

And the date goes back even further because that's the most recent one I found and I'm too lazy to do some math The date in human readable text offset to your timezone is February 26, 2011, 05:45:24 PM (Correct time offset for anyone viewing this )

[GB_Techie]

Would that I could...because of this mess several long time users have left and I would lose all of their goodbye posts.  Is there really no way to find the original USERID or email address?  I think that would solve my problem best as then I could reallocate posts to a new user.

Yes, I have a daily backup in duplicate!

[Arantor]

User ID and user name are two DIFFERENT THINGS.

User id is the number internally used, user name is stored in the post along with user email and IP address. In fact, I already pointed out how you could find both the user name and email address used on the posts... just LOOK AT ONE OF THEM IN THE FORUM!

[live627]

Hmm, I'm starting to scratch my head thinking of a solution. To answer -- um, wait, I just looked at my test database and found the email actually IS recorded with the post! Whew...

So now we modify the query like so:

DELETE FROM smf_members WHERE poster_email = '[email protected]'

Certain things to note: The email address must be surrounded in apostrophes. Obviously, you should replace my dummy with the offending one on your side.

[Arantor]

I wouldn't just randomly do that, because it screws up counts, not to mention could break relationships in topics Which is why I was suggesting reattaching then deleting the user.

[rugrat]

Looking through my recycle board and database, it actually looks like the email address is wiped out once you delete the member. See attachment posted above. The email address should be between the username and ip address I believe. But the username remains That should be enough to reassign to a new user I believe, but I've never tired it.

[CapadY]

When I understand it well topicstarter want to ban all used IP-adresses with this account. So I think deleting the posts is not realy a solution.
First at all he want to have a list with all used IP's at this account.
Next query will give all used IP's as result:

select distinct poster_ip from smf_messages where poster_email = "used email address"

After running this query you know which  IP's to ban.

[Arantor]

Looking through my recycle board and database, it actually looks like the email address is wiped out once you delete the member.

No it's not. Under the name you'll see an email link...

[GB_Techie]

User ID and user name are two DIFFERENT THINGS.

User id is the number internally used, user name is stored in the post along with user email and IP address. In fact, I already pointed out how you could find both the user name and email address used on the posts... just LOOK AT ONE OF THEM IN THE FORUM!

To be honest, I thought you were talking about something else since it didn't work...which is why I kept asking, but I'll explain.

Browse for some of the posts... The username used will be posted in thread, there will be a link underneath for emailing and it should have the IP address in the bottom right corner of the post.

I've just clicked on the email link below a 'guest' post of a deleted member and the email field is empty (whereas for active members it gives me the email address).  Is the name that is left up the username as I always thought it was the display name as a lot of our members change their display name before they delete so it doesn't look like they made the post.  The IP address is there as usual but it would mean manually finding each of the hundred messages posted.

If I have misunderstood then I appreciate your help thus far and would be grateful if you would explain what I've missed.

[rugrat]

OP, here is what I just did on my forum.

Created a new user "testing101" in Admin > Members > Registration.

Then went to Maintenance > Forum Maintenance > Members, and in Reattribute User Posts I selected Username of, instead of Email address of. In the Username of field, I input the username of the account, now a guest account, that was deleted. In the Attribute posts to member field, I input my new user testing101. I unchecked Add posts to users post count. Then click the Reattribute button. The system will prompt you if you want to do this.

After I did this, the deleted guest account was renamed to testing101. I then went to the Profile page of testing101 and selected Show Posts. This gave me a list of all post. You have to open each post to get the IP addresses.

When finished, I deleted user testing101 and the old guest account was renamed back to what it was.

Hope that helps

[GB_Techie]

Thanks Rugrat, I know how to reallocate posts and I've tried it but it doesn't seem to be the username which is displayed as this guest....or it's not recognising it.  I'll just have to ask people to rtm as many posts as possible.