36 members from hitting 65,000

[Onkel Neal]

Question: should we go back and delete accounts that:


have 0 posts
joined after 2009
show no activity including log-in for a year
There's a good chance we have a couple thousand spam accounts camped out in the db.

[Betonov]

I joined after 2009 :o

[Betonov]

But I agree on 1st and 3rd,
Maybe give them a warning, something a bot wont be able to respond

[Skybird]

Your place, your rules, Neal. ;) Do as you pleases.

[kranz]

Neal, if you give me a small discount in your shop, I can make 36 fake accounts.
about deleting accounts: kick some as*-lickers first :D

[Platapus]

Question: should we go back and delete accounts that:


have 0 posts
joined after 2009
show no activity including log-in for a year



Is this an "and" list (all three need to be true) or an "or" list (only one needs to be true) for deletion?

My opinion?

Members should not be deleted simply because they have not posted in a year. Readers are also part of our community. This addresses item 1

If a member has not logged in for a year, I would still keep their account active unless a newer member would like that screen name. A member that does not log in costs the site nothing but the space to record their account information.

Is there a risk with a "spam account" that does not log in?

I do not understand why joining after 2009 is a factor. Can you explain that.

Basically, my worthless opinion is to keep everyone on the membership list unless they actively tell you they wish to be removed.

Figures that list total number of members on an internet site are of limited use, unless that number helps you with selling advertisement. I would think that average log-ins per day would be more important.

I echo the phrase "your site, your rules" :salute:

If you are askin, I say leave the dead accounts alone unless there is a compelling reason to delete them.

[FIREWALL]

I could give a SHIP. I come here to enjoy SubSim. :woot:..:salute:

[Torplexed]

I could give a SHIP. I come here to enjoy SubSim. :woot:..:salute:

Yeshure Neal *hic* We sure appresheates your hoshpitality. :()1:

[Oberon]

I'm not a spam account.


Bump.

[Philipp_Thomsen]

I'm pretty sure he meant people that doesn't have post and joined before 2009, not showing any activity in a year.

[geetrue]

Just send a fake bill for $1.00 to each member for services rendered over the years and then wait and see how many dollars you get back in the mail :know:

[Reece]

If there is no activity (no log-ins) and zero posts for over a year then I would delete them, no normal member would join and do nothing!:hmmm:

[GoldenRivet]

i think that an account with no activity, no posts, no log ins etc for a year or maybe even 18 months should be deleted.

just my opinion.

[FIREWALL]

Don't they like to call themselves LURKERS ?

WPL ? I don't think it's a good idea. :nope:

Old members I enjoy hearing from. :yep:

[RickC Sniper]

Your place, your rules, Neal. ;) Do as you pleases.

This +1

[FIREWALL]

This +1

Short One ? + 1 :har:

[August]

i think that an account with no activity, no posts, no log ins etc for a year or maybe even 18 months should be deleted.

just my opinion.

It is mine as well.

Lurkers can lurk without signing in.

[geetrue]

It is mine as well.

Lurkers can lurk without signing in.

True too ... aren't we suppose to be captains or something like that.

What if the captain didn't show up since 2009?

[August]

True too ... aren't we suppose to be captains or something like that.

What if the captain didn't show up since 2009?

He'd be relieved of command at least. The forum equivalent is account deletion. Nothing stopping someone from creating a new account either.

[FIREWALL]

I just don't like where this is going. :nope:

If your selling phones or pures ok. :yep: But just poking around....:p2:

[FIREWALL]

Question: should we go back and delete accounts that:


have 0 posts
joined after 2009
show no activity including log-in for a year
There's a good chance we have a couple thousand spam accounts camped out in the db.


Hey Neal am I missing something ? :salute:

[gimpy117]

send them a PM first warning them that if there is no reply they will get the delete

[FIREWALL]

send them a PM first warning them that if there is no reply they will get the delete

That makes sense. gimpy117 Then again you usally make good sense. :yep:

[Penguin]

I guess it's time to upgrade to 17-bit forum software :D

[Onkel Neal]

Is this an "and" list (all three need to be true) or an "or" list (only one needs to be true) for deletion?



Yes, all three would need to be true to qualify for deletion, if we go that route. My intention is to remove accounts that were created, never posted, and have been abandoned. If someone made a forum acount 2 years ago and has not logged in once during that time, it's likely it could be a spam account. Not 100%, but still, what's the point in keeping the account, and ... (see below)




My opinion?

Members should not be deleted simply because they have not posted in a year. Readers are also part of our community. This addresses item 1

If a member has not logged in for a year, I would still keep their account active unless a newer member would like that screen name. A member that does not log in costs the site nothing but the space to record their account information.

Is there a risk with a "spam account" that does not log in?

I do not understand why joining after 2009 is a factor. Can you explain that.

Basically, my worthless opinion is to keep everyone on the membership list unless they actively tell you they wish to be removed.

Figures that list total number of members on an internet site are of limited use, unless that number helps you with selling advertisement. I would think that average log-ins per day would be more important.

I echo the phrase "your site, your rules" :salute:

If you are askin, I say leave the dead accounts alone unless there is a compelling reason to delete them.

Fair enough. I know, my site, my rules, in the absolute sense, but I share the site with you guys, so you should have some input. Besides, I know from experience that many times, the forum members have better ideas than me, so I consider it wise to consult you.

There is a problem with spammers here. There is a lot going on behind the scenes, it's a war out there. Hunter and the other mods, and several key members are helping detect them and delete them. But with all the attention the spam world is paying to this forum, I am afraid there are inactive spam member accounts that simply have spam sig links, without a single post, and the spam trackers are giving them credit, and consequently, increasing the attractiveness of this site for more spam.

[CCIP]

I vote for a bit of spring cleaning. :salute:

Lurkers can re-register if it really matters to them.

[Platapus]

the spam trackers are giving them credit, and consequently, increasing the attractiveness of this site for more spam.

I never considered that.

Off with their heads! :D

[Penguin]

There is a problem with spammers here. There is a lot going on behind the scenes, it's a war out there. Hunter and the other mods, and several key members are helping detect them and delete them. But with all the attention the spam world is paying to this forum, I am afraid there are inactive spam member accounts that simply have spam sig links, without a single post, and the spam trackers are giving them credit, and consequently, increasing the attractiveness of this site for more spam.

Ah, now I get the system behind it, as I didn't get how these measures would discourage spammers. I didn't know that an inactive account still can raise the credit in the world of spammers other than to see how many boards can be infiltrated through one email. So that's what you can also do: to cross-reference the email adresses of those 0-post accounts with known spam-adresses.

If only sigs with links are the problem, how would it be either to grant "sig rights" to people with at least a couple of posts - most spam I see here gets deleted before they post more than 3 - or to revoke those sig rights if an account gets reported for spam.

[magic452]

Someone can lurk all they want without becoming a member, you become a captain so you can participate. No log in, no participation, no need to worry.
A PM sounds like enough, than delete.

If a deleted account holder wants to participate then they can re register.

Magic

[Stealhead]

Sounds fair if they get deleted after a PM warning(for inactivity) then they must not care.

I also agree with the others the true lurkers lurk for a year or so then join then lurk some more then finally post and either A)stop lurking and become more active or B)go back to lurking again if its B they some are what wasted space and can just keep on lurking as a none member.

Wow my 666th post on the day after the supposed rapture day mark of the beast I never would have guessed the anti-christ would be mug shot from PTO II.(one of my favorite games is that supposed to be Admiral Spruance? in my Avatar)

[Sailor Steve]

I'm not a spam account.
I am, but I'm real good at it.

My opinion? Unless clearing out unused accounts serves some actual purpose, leave 'em. If they take up space, dump 'em.

Oh, I'm so helpful, I am. :sunny:

[Takeda Shingen]

I agree with the sentiment that we should be purging the DB of inactive accounts. I would qualify an inactive account as being unused for a period greater than 12 months. A warning of account deletion is counterproductive, as it adds to Neal's already heavy workload.

[Onkel Neal]

20 to go...

[frau kaleun]

Shoot 'em all and let God sort 'em out.













Wait, what were we talking about again?

[gimpy117]

That makes sense. gimpy117 Then again you usally make good sense. :yep:

really I though i was the village idiot here? maybe i need to try harder :D

http://www.youtube.com/watch?v=r2d0phypLrg

[d@rk51d3]

Time to clean house. :up:

[Herr-Berbunch]

Spring-clean away! As long as all three boxes are ticked otherwise we'd miss the re-emergence of people like the Avon Lady amongst others.

[Feuer Frei!]

Well, after a 12 mth, or more inactivity of an account, there are indeed questions that should be asked by the site owner.
And i believe it should be a responsibility (and courtesy) of the member in question, that if they are going to be inactive for that length of time that they should email or pm the site owner and indicate the aforementioned.
"Dear Neal, please keep my account active as i will be going overseas for the next 12 mths to fight terrorism and will not have internet access as i will be residing in caves in the foothills of Pakistan".
You get my drift.
It's a 2-way street.
The member should have the common decency and foresight to notify if no activity of account for a lengthy period of time and the site owner is responsible in keeping the site active and uptodate and in good working order, with an abundance of active members.
My thoughts.

[Raptor1]

I don't think people's accounts should be deleted just because they haven't shown up for a while. Though if it's only people who haven't logged in for a long time and have never posted, then it would make sense to clean them out if the forum/Neal benefit from it...

[CCIP]

I think the only ones suggested for deletion though are the accounts that are inactive AND have 0 posts, not those that are simply inactive... :hmmm:

[HunterICX]

0 posts and non-active for over a year should be removed from the DB imo.

HunterICX

[Platapus]

Shoot 'em all and let God sort 'em out.













Wait, what were we talking about again?


We had to destroy the membership roster in order to save it. :O:

[Herr-Berbunch]

...and maybe set an upper threshold, like 47k posts. Obviously spamming and neither use nor ornament to the site.






:03:

[Oberon]

http://img641.imageshack.us/img641/3269/cybermendeletecampaignb.jpg

[Armistead]

I don't think people's accounts should be deleted just because they haven't shown up for a while. Though if it's only people who haven't logged in for a long time and have never posted, then it would make sense to clean them out if the forum/Neal benefit from it...

Yep, people get busy and we often get that "I'm back" thread after a year of no show.

I think Neal is considering the political pull of membership and a possible bid for congress.

[Torplexed]

Yep, people get busy and we often get that "I'm back" thread after a year of no show.

I think Neal is considering the political pull of membership and a possible bid for congress.

Can I donate through Subsim for the future honorable Senator Stevens? :shucks:

[Armistead]

Can I donate through Subsim for the future honorable Senator Stevens? :shucks:

I'm sure he'll post a direct link when he's ready to accept money..
Rumor has it, he's smarter than Sarah Palin, so he should have a chance in some office.

[Onkel Neal]

That's just a rumor.

2 to go...

[nikimcbee]

:yeah: Clean out the DB. What about auto deleting spam robots, such as the ones pushing phones-und-stuff? The ones that post the same thing, then never post again.

[Onkel Neal]

Threads: 96,739, Posts: 1,523,860, Captains: 65,000, Captains visited in the last 24 hours: 10,851
Ahoy to our newest member, skook (http://www.subsim.com/radioroom/member.php?u=279290)

...for now. :hmmm:

[nikimcbee]

So what are you going to do with all of your newfound DB space?

[stoppro]

I guess I'll have to post more often. I'll go get some beer I'll be right back....

[August]

I'd vote early and often for Neal.

[nikimcbee]

I'd vote early and often for Neal.

I'd even be a community organizer for Neal.

Texas BBQs for all.:woot:

[krashkart]

Texas BBQs for all.:woot:


Is this a lifetime guarantee or just a one-off "buy my vote" sort of arrangement? I mean, if I was guaranteed a Texas BBQ to be redeemed at any point in my life, that would be right up my alley. :hmmm:

Otherwise, if it's a only one-off thing then I must insist on having said Texas BBQ served to me by authentic Cowboys cheerleaders. :D

My votes don't come cheap, ya see? :arrgh!:

[nikimcbee]

Is this a lifetime guarantee or just a one-off "buy my vote" sort of arrangement? I mean, if I was guaranteed a Texas BBQ to be redeemed at any point in my life, that would be right up my alley. :hmmm:

Otherwise, if it's a only one-off thing then I must insist on having said Texas BBQ served to me by authentic Cowboys cheerleaders. :D

My votes don't come cheap, ya see? :arrgh!:

Now, if you vote for Senator Stevens, I could see a good career in your future. We could probably arrange for Krashkart to be senior beer testor at [insert krashkart's favorite beer here] brewery.

http://1.bp.blogspot.com/_7nQCnPaagKA/TQqY8QfHCLI/AAAAAAAAAIE/TTlLp8PdUi4/s1600/mel_brooks.jpg

[Platapus]

If Neal is elected, can I be appointed Minister of Punctuation?

[Sailor Steve]

If Neal is elected, can I be appointed Minister of Punctuation?
Only if I get to be Secretary of Grammar.

Though I'll settle for Undersecretary of Usage.

[Anthony W.]

I've been a member for... How long? And only been active for the last few months

[frau kaleun]

Only if I get to be Secretary of Grammar.

Though I'll settle for Undersecretary of Usage.

I don't want to be Undersecretary of anything until I see what the Secretary I'm gonna be under looks like.

[Sailor Steve]

I don't want to be Undersecretary of anything until I see what the Secretary I'm gonna be under looks like.
You've seen my pictures. Be afraid. Be very afraid.

[DarkFish]

I guess it's time to upgrade to 17-bit forum software :DThat made me ROFL:har:
:yeah:

[Onkel Neal]

According to my records, only 19,395 have made one or more posts. (http://www.subsim.com/radioroom/memberlist.php)

[Anthony W.]

According to my records, only 19,395 have made one or more posts. (http://www.subsim.com/radioroom/memberlist.php)

Probably because you have to be a member to access the downloads section

[Sailor Steve]

Probably because you have to be a member to access the downloads section
Good point. The majority of folks probably join up just so they can get to the mods.

[August]

Is there a way the board can require an intro post before granting membership?

[Feuer Frei!]

According to my records, only 19,395 have made one or more posts. (http://www.subsim.com/radioroom/memberlist.php)
Out of how many? (current active and inactive members).

[kranz]

Is there a way the board can require an intro post before granting membership?
I vote for an IQ test.
If your IQ is lower than 160- sorry, you won't be able to post here.(unless you are Skybird :DL)

[Betonov]

I vote for an IQ test.
If your IQ is lower than 160- sorry, you won't be able to post here.(unless you are Skybird :DL)

Now that would be boring, only me and Skybird.

[Onkel Neal]

Ok, I changed the Captains List so now all members show up. Before, it only showed members with at least 1 post.

My daughter and I are perusing the list, sorted by Join date in reverse, so we can see the newest members. Skipping back to April, and sizing up the most sketchy username, then checking the member's profile...


tom0428h

Hi, my name is Tom and I recently set up a website supplying wholesale jewelry (http://www.diggfind.com). Hope to meet some good people here.



couponcode65 ? ORLY?
Dell Coupon Codes (http://boldcoupons.com/dell-coupon-codes/)

ppl001
Speed Dating (http://www.speed8.be) chaise lounge (http://www.chaiseloungechairs.org) Backlinks (http://www.autobacklinkservice.com)


SEO Liverpool (Vickie says, SEO does not stand for "Silent Engineering Officer" :O:)
Simple web design is a Liverpool based SEO company who are experts in SEO. SEO Liverpool (http://www.simple-webdesign.co.uk) - Do you run a Liverpool based company that needs expert SEO help?


And so it goes... now you can see why Subsim is so popular with SEOs, spambots and spamho's. The spam has to go.

[Herr-Berbunch]

You could at least destroy the hyperlinks from those examples :doh:

Oooh, hang on, Dell coupon codes... :yeah:



:nope:

:O:

[joea]

Good job Neal! I think the way you're going is the right one-0 posts and one year inactive = delete.

Now are non-Merricuns allowed to get the free Texas bbq if you're elected? Given we would have voted for you if we could. I'll bring some good Euro brews to add some variety. :yeah: Maybe I'll be appointed Ambassador for overseas hosers and get an invite that way eh.

[Betonov]

Now are non-Merricuns allowed to get the free Texas bbq if you're elected? Given we would have voted for you if we could. I'll bring some good Euro brews to add some variety. :yeah: I'll be appointed Ambassador for overseas hosers eh.

I'll bring the cevapcici and ajvar.

I won't request an office, but can I get a congresional charter for exclusive import of eastern and south-european food into the US :D

[Jimbuna]

Ok, I changed the Captains List so now all members show up. Before, it only showed members with at least 1 post.

My daughter and I are perusing the list, sorted by Join date in reverse, so we can see the newest members. Skipping back to April, and sizing up the most sketchy username, then checking the member's profile...




Good idea...keep a sharp eye for anybody going by the name of 'Steed' :03:

[Jimbuna]

Ok, I changed the Captains List so now all members show up. Before, it only showed members with at least 1 post.

My daughter and I are perusing the list, sorted by Join date in reverse, so we can see the newest members. Skipping back to April, and sizing up the most sketchy username, then checking the member's profile...



Good idea....keep a sharp eye for somebody going by the name of 'STEED' :DL

[Feuer Frei!]

Good idea....keep a sharp eye for somebody going by the name of 'STEED' :DL
STEED=sketchy.
Trouble is, he'd agree with you :O:

[Jimbuna]

STEED=sketchy.
Trouble is, he'd agree with you :O:

It might also be worth checking if your Vendors secondary account :DL

[Feuer Frei!]

It might also be worth checking if your Vendors secondary account :DL
In his absence he pays me a generous commission. :O:

[Anthony W.]

We should send a friendly email notification first. Something like... "We the kind, generous people of Subsim have taken your account hostage and will feed it to the midget spidersquidopods if you do not make a legitimate post within the next day."

FEAR THE ALMIGHTY SPIDERSQUIDOPODS!

[Herr-Berbunch]

We should send a friendly email notification first. Something like... "We the kind, generous people of Subsim have taken your account hostage and will feed it to the midget spidersquidopods if you do not make a legitimate post within the next day."

FEAR THE ALMIGHTY SPIDERSQUIDOPODS!


A legitimate post? Here you go (and also turning it into another lolcat thread) :D

http://images.cheezburger.com/completestore/2009/9/14/128974626577315188.jpg

[frau kaleun]

Good point. The majority of folks probably join up just so they can get to the mods.

I think if you look at the Statistics in someone's profile it shows # of downloads in addition to # of posts.

So the criteria could include whether or not someone's downloaded something in addition to whether or not they've posted. That would allow lurkers who created a valid account in order to download something to be retained as members. Seems unfair to delete an account if someone made it for a valid reason. I seem to remember I initially made my account to access the downloads, not to post. I don't know that I even had the intention of ever posting anything.* :hmmm:






*You can all stop laughing now.

[Betonov]

*You can all stop laughing now.

WE CAN'T
http://meowcheese.com/files/lolpics/2008/06/laughing-cat.jpg

[AdeptCharge]

We should send a friendly email notification first. Something like... "We the kind, generous people of Subsim have taken your account hostage and will feed it to the midget spidersquidopods if you do not make a legitimate post within the next day."

FEAR THE ALMIGHTY SPIDERSQUIDOPODS!Hardly. :doh:

[Anthony W.]

Or we could just make it so you don't have to register to dowload, but have one of those letter things.

And a speed cap - and registered users get their speed caps taken off after 100 posts or something

[Onkel Neal]

I think if you look at the Statistics in someone's profile it shows # of downloads in addition to # of posts.

So the criteria could include whether or not someone's downloaded something in addition to whether or not they've posted. That would allow lurkers who created a valid account in order to download something to be retained as members. Seems unfair to delete an account if someone made it for a valid reason. I seem to remember I initially made my account to access the downloads, not to post. I don't know that I even had the intention of ever posting anything.* :hmmm:



Trew. I do not want to disturb legit members even if they only joined to d/l. And that's one of the reasons that registration is necessary for downloading, they may become active members and enhance the site.


What I really need is more SQL background. Anyone here know SQL?


User.Joindate >1291165261
User.Lastactivity - User.joindate < 86400
User.Totalposts <2
Sigparsed.Signatureparsed <> ""
Sigparsed.signatureparsed IS NOT NULL

Something like that should help me isolate spammers and still allow dormant accounts to remain intact.

[krashkart]

Now, if you vote for Senator Stevens, I could see a good career in your future. We could probably arrange for Krashkart to be senior beer testor at [insert krashkart's favorite beer here] brewery.

http://1.bp.blogspot.com/_7nQCnPaagKA/TQqY8QfHCLI/AAAAAAAAAIE/TTlLp8PdUi4/s1600/mel_brooks.jpg

I see... yes, I could see myself testing beer. I still get the free Texas BBQ, right? :DL

[Jimbuna]

In his absence he pays me a generous commission. :O:

LOL...nice one :DL

[Penguin]

Trew. I do not want to disturb legit members even if they only joined to d/l. And that's one of the reasons that registration is necessary for downloading, they may become active members and enhance the site.

What I really need is more SQL background. Anyone here know SQL?

User.Joindate >1291165261
User.Lastactivity - User.joindate < 86400
User.Totalposts <2
Sigparsed.Signatureparsed <> ""
Sigparsed.signatureparsed IS NOT NULL

Something like that should help me isolate spammers and still allow dormant accounts to remain intact.

Maybe I can help a little.
Is your second command meant like it looks? You would select everyone who had his last log-in in in the first 24 hours after joining, if a member logged in after this period, he would not be selected. I think you know from experience if spammers log in another time, or just create an account and never check back.

To check if someone was inactive for more than a year, it should look like User.Lastactivity - User.joindate > 31536000

I think the <>"" can do the job to check if there is a sig (I guess that signaturparesed stores it in plain text), nicer would be Sigparsed.signatureparsed NOT LIKE ' '

As you obviously check two tables, User and Sigparsed, don't forget the JOIN command.

Cheers,
Penguin

[STEED]

Good idea...keep a sharp eye for anybody going by the name of 'Steed' :03:

Good idea....keep a sharp eye for somebody going by the name of 'STEED' :DL

Are now I see why jim so far ahead, double poster, what cheek. :O:

[krashkart]

Are now I see why jim so far ahead, double poster, what cheek. :O:


Perhaps we can

[krashkart]

Are now I see why jim so far ahead, double poster, what cheek. :O:


learn a thing or two

[krashkart]

Are now I see why jim so far ahead, double poster, what cheek. :O:


from the master. :D

[Anthony W.]

But I wanna use the squidopods...

[Onkel Neal]

Maybe I can help a little.
Is your second command meant like it looks? You would select everyone who had his last log-in in in the first 24 hours after joining, if a member logged in after this period, he would not be selected. I think you know from experience if spammers log in another time, or just create an account and never check back.

To check if someone was inactive for more than a year, it should look like User.Lastactivity - User.joindate > 31536000



Yes, sir, my intention is to look back at members who joined since Dec 1, 2010, have 0 posts, have a signature, and their last activity was 24 hours from their join date. We find many of the sig spammers simply make an account and never post or log in again.

I want to minimize deleting actual people, so if a guy joined Dec 20 and his last activity was Dec 23, then he stays. I don't mind a guy being inactive for 6 months, as long as he is not a spammer. Does that make sense?

I think the <>"" can do the job to check if there is a sig (I guess that signaturparesed stores it in plain text), nicer would be Sigparsed.signatureparsed NOT LIKE ' '

As you obviously check two tables, User and Sigparsed, don't forget the JOIN command.

Cheers,
Penguin

Yes, I am studying up on the JOIN command, internal and external. I'm a real noob with SQL so I have to build this query from scratch. Anything you have to say on the subject is welcome, thanks, Penguin. :salute:

[Penguin]

Yes, sir, my intention is to look back at members who joined since Dec 1, 2010, have 0 posts, have a signature, and their last activity was 24 hours from their join date. We find many of the sig spammers simply make an account and never post or log in again.

I want to minimize deleting actual people, so if a guy joined Dec 20 and his last activity was Dec 23, then he stays. I don't mind a guy being inactive for 6 months, as long as he is not a spammer. Does that make sense?

Yes, I am studying up on the JOIN command, internal and external. I'm a real noob with SQL so I have to build this query from scratch. Anything you have to say on the subject is welcome, thanks, Penguin. :salute:

Ok, this makes sense, if the spammer doesn't log in a second time.(didn't check out the spam hunt thread yet to look at actual spammer accounts ;))
EDIT: Found this one while randomly looking for spammers: http://www.subsim.com/radioroom/member.php?u=276634 He logged in 3 days after he registered , however most found had their last activity on the date of registration.

So the command to get all the infos about this account would look like this, this is the explicit statement, you can make it shorter, but this is better for the comprehension

SELECT *(all infos, or whatever you need)
FROM User AS U1 INNER JOIN Sigparsed AS S1 (just abbreviations to type less)
ON U1.UserID = S1.UserId (UserId, or something simular would be your unique key that you can find in both tables)
WHERE U1.Joindate >1291165261
AND (U1.Lastactivity - U1.joindate ) < 86400
AND U1.Totalposts < 2
AND S1.Signatureparsed NOT LIKE ' '
AND S1.signatureparsed IS NOT NULL
ODER BY U1.Username ;

Here is some material to learn SQL: A lecture by David Malan, who teaches at a little, unknown school on the East Coast. I think he is very articulate and intelligible: http://cs75.tv/2010/fall/#l=lectures&r=about&v=lectures/4/lecture4 At the lower left corner you'll find also the second part (lecture5)

If you are more of a beginner, there is also a SQL lecture in the introduction to Computer Science class found here: http://cs50.tv/2010/fall/ If a lecture is broken you may want to play with the course dates in the upper drop-down menu to access courses from previous years.

[Onkel Neal]

Thanks so much for the help. I am going to have a look at those course, I find SQL very interesting, I just never got on track with a tutorial that I could follow.

Question: in your query, you stated
SELECT *(all infos, or whatever you need)
The info I would want to select would be userID, signature, homepage, right?


FROM User AS U1 INNER JOIN Sigparsed AS S1 (just abbreviations to type less)
Abbreviations for what? Or did you simply give the join names really short names, like U1 ? That makes sense.

AND U1.Totalposts < 2
That means any member with 1 post will not be selected, right? I can change that to < 1 and select all the 0 post users, right?

Listen, I really appreciate the help.
Neal

[Penguin]

Thanks so much for the help. I am going to have a look at those course, I find SQL very interesting, I just never got on track with a tutorial that I could follow.

Question: in your query, you stated
SELECT *(all infos, or whatever you need)
The info I would want to select would be userID, signature, homepage, right?

np!
With the star it would show you all infos available (all columns) from both tables. If you want to use this statement to find the homepages it would just be something like this: SELECT U1.userpage - this would be usefull for a list of the userpages. To select more, just put a comma between, select U1.userpage, S1.signature, U1.shoesize, etc


FROM User AS U1 INNER JOIN Sigparsed AS S1 (just abbreviations to type less)
Abbreviations for what? Or did you simply give the join names really short names, like U1 ? That makes sense.

yes, it just gives short names for the tables in this search query, helps to avoid typing long names for the lazy ones ;) This is helpfull if you want to join several tables. If you want to use the real table names, just delete the "AS..."


AND U1.Totalposts < 2
That means any member with 1 post will not be selected, right? I can change that to < 1 and select all the 0 post users, right?


In the example, it would select all users with no or one posts. I thought this would make sense, as we saw today, many spammers make one post, however I think those accounts get vaporized eventually :DL.
If you want to select the users with no posts, you use either "= 0", "<1", or "IS NULL". It depends on how the totalposts column looks if a new user is created. If the db puts a 0 (numeric zero) into that column in the moment a new user registers, you can use one of the first two statements. If the column is empty untill the first postcount is written into it, you have to use the IS NULL statement.

[razark]

Subsim:
Come for the spam hunts, stay for the SQL tutorials.

[Onkel Neal]

Hahaha :har: Nice!

[Paco]

Hi,

I'm coming from the SPAM-Hunt thread (http://www.subsim.com/radioroom/showpost.php?p=1670122&postcount=107) to this one.


AND (U1.Lastactivity - U1.joindate ) < 86400



perhaps a little bit easier to handle:

AND (DATEDIFF(U1.Lastactivity, U1.joindat) < "amount of days you want")

BTW, the SQL looks very good for hunting spammers :salute:

[Penguin]

Thanks for your input Paco, that's the reason why I put up the code here: always better if some more pair of eyes take look over it - no offence if you are a pirate :arrgh!: ;)

I am not sure though if Datediff works if the dates are stored as a timestamp and not in date format, without having to convert it - otherwise Datediff is certainly more elegant!

[Onkel Neal]

Chinese Prisoners Allegedly Forced to Play 'World of Warcraft' (http://www.pcworld.com/article/228716/Chinese_Prisoners_Allegedly_Forced_to_Play_World_o f_Warcraft.html) :har:


Don't get any ideas!


Yeah, we got the query working, weeded out almost 1000 spam accounts, probably another 3000 to go.

[geetrue]

You SQL guys are all a like ... for all we know you could be the same men in black that show up after UFO sightings :yep:

[flatsixes]

All this talk about squirrels makes me hungry.
http://www.strangecosmos.com/images/content/15912.jpg

[frau kaleun]

http://28.media.tumblr.com/tumblr_llmg4kqNWF1qemhs1o1_r1_500.gif

[Herr-Berbunch]

http://image.spreadshirt.com/image-server/image/composition/4122883/view/1/producttypecolor/377/type/png/width/280/height/280/secret-squirrel-large_design.png

[Torplexed]

http://4.bp.blogspot.com/_2TCYQxSUqGQ/SiUONC4kv9I/AAAAAAAABgs/DLLngo1N2MY/s400/super_squirrel.jpg

[Jimbuna]

http://feellikeanutcom.ipage.com/store/media/Animated-Squirrel

[krashkart]

http://www.digitalneophyte.com/Art/brother_sql-colorless.gif

[Onkel Neal]

:Kaleun_Salivating:Well, after the big spam purge, 3 days away from 60,000

[magic452]

:Kaleun_Salivating:Well, after the big spam purge, 3 days away from 60,000

5,000 gone:yeah: boy you did some big time house cleaning :up:

Magic

[Reece]

I just looked at the first member listed in the captains roster, joined 2009 last activity 2009, posts 0, shouldn't this type be deleted as well or hasn't it been done yet? reading Neal's last post can be taken either way!:hmmm:

[Onkel Neal]

No, we're not taking out inactive members. As long as they are not spammers, they stay. Most likely, they will never return, and if they do, they are locked out of their account because they forgot their password and the email is old and no longer accessible. But a small percentage of them may wander back round, and we may as well leave their account available. I can think of many forums I joined years ago, and from time to time, I visit them. Glad to have my username intact and unmolested.

:Kaleun_Wink: