Charcoal HQ

Anyone here good with SEDE?

pretty

I'm trying to indentify bursts of upvotes for single users (over 10 in 10 minutes) here

data.stackexchange.com/stackoverflow/query/708817/…

[ SmokeDetector | MS ] Username similar to website in answer: Is there a 4K action camera solution that does NOT have perspective distortion? by Motion Pro on video.SE

It doesn't seem to be working. Could you take a look and tell me where I failed @Glorfindel?

12:10 PM

That won't work. Vote timestamps have been reduced to date-only, no time information.

fp- by Glorfindel

Awh

shucks

Q: Why is vote time missing in the SE data dump and SEDE?

15

Is there a particular reason why vote times are omitted in the data dump? IMO this could be a very useful data-set. Is this one of the steps that was done to anonymize the data, or does it simply not exist? This would be a very good addition to future dumps to help build time-based relationships...

A: Why is vote time missing in the SE data dump and SEDE?

9

This was done to anonymize the data

...I couldn't resist the urge to edit.

@Glorfindel So now I have this: data.stackexchange.com/stackoverflow/query/708819/…

It's supposed to identify users that have gained 20 or more votes per day and have sub 2000 reputation

It's not working correctly I don't think

12:21 PM

Nope. Because the only votes which have the UserID stored, are the 'Favorite' and 'BountyStart' votes.

Byte Commander

Spammy? askubuntu.com/q/944967/367990

(that's the owner of the vote)

!!/watch viewnhanh\.com

@Glorfindel Added viewnhanh\.com to watchlist

@Glorfindel Oh so I need to join to post id and then to user id to get which user the vote was for

Yes ~~To find out who benefited from the vote, you need to link the Posts table~~, and don't forget to filter on VoteTypeId = 2.

12:23 PM

CI on 9ef57ba succeeded. Message contains 'autopull', pulling...

[ SmokeDetector ] SmokeDetector started at rev 9ef57ba (SmokeDetector: Auto watch of viewnhanh\.com by Glorfindel --autopull) (running on ArtOfCode/EC2)

@ByteCommander yes

Restart: API quota is 8011.

It's gone now

@Magisch Is this what you're after? data.stackexchange.com/stackoverflow/query/708830

@Rob you can use AS [User Link] for magic links: data.stackexchange.com/stackoverflow/query/708833

12:30 PM

[ SmokeDetector | MS ] Bad keyword in body, bad keyword in title, blacklisted website in body, blacklisted website in title, repeated URL at end of long post: getmenshealth.com/pro-test-180/ by lucy lynton on astronomy.SE

tpu- by Glorfindel

@Glorfindel Ah yes, forgot about that - for some reason I was thinking it only works for posts

but that does look like what Magisch is looking for.

[ SmokeDetector | MS ] Bad keyword in body, bad keyword in title, blacklisted website in body, blacklisted website in title, repeated URL at end of long post: getmenshealth.com/pro-test-180/ by user251061 on apple.SE

!!/blacklist-keyword verutum\W?rx

@tripleee Blacklisted verutum\W?rx

12:41 PM

@SmokeDetector k

tpu- by Glorfindel

CI on 4410fdd succeeded. Message contains 'autopull', pulling...

[ SmokeDetector ] SmokeDetector started at rev 4410fdd (SmokeDetector: Auto blacklist of verutum\W?rx by tripleee --autopull) (running on ArtOfCode/EC2)

Restart: API quota is 7827.

!!/blacklist-website-force getmenshealth\.com

@tripleee Blacklisted getmenshealth\.com

this is already blacklisted by a more general regex but it helps some scenarios to have the site explicitly blacklisted

110/110 over one year

[ SmokeDetector ] SmokeDetector started at rev 4410fdd (SmokeDetector: Auto blacklist of verutum\W?rx by tripleee --autopull) (running on ArtOfCode/EC2)

Restart: API quota is 7811.

12:44 PM

and remarkably still below auto 7 days ago

CI on e351072 succeeded. Message contains 'autopull', pulling...

[ SmokeDetector ] SmokeDetector started at rev e351072 (SmokeDetector: Auto blacklist of getmenshealth\.com by tripleee --autopull) (running on ArtOfCode/EC2)

Restart: API quota is 7810.

This is what I came up with:

data.stackexchange.com/stackoverflow/query/708836/…

!!/watch livetecs\.com

@tripleee Added livetecs\.com to watchlist

@Magisch this user is indeed suspicious :) I hadn't even noticed he was suspended (and it's not because of voting irregularities).

12:48 PM

[ SmokeDetector | MS ] Bad keyword with email in answer: How do I remove google contacts? by Felipe on android.SE

[ SmokeDetector ] SmokeDetector started at rev 77da32f (SmokeDetector: Auto watch of livetecs\.com by tripleee --autopull) (running on ArtOfCode/EC2)

Restart: API quota is 7772.

fp- by ArtOfCode

@Glorfindel I was looking at the same thing, I recall he was in trouble because of somewhat aggressive but presumably well-intentioned activity in SOCVR before

@SmokeDetector f

@Glorfindel It's all users under 2k rep that recieved 20 or more upvotes in a single day on 10 or more different posts

I put a filter so that it doesn't catch suspended high rep users now

[ SmokeDetector | MS ] Blacklisted website in body, link at end of body, link following arrow in body: Shipping scales like the Dymo S180 can weigh up items 180kg by salysfrankln on askubuntu.com

[ SmokeDetector | MS ] Bad keyword in body, bad keyword in title, blacklisted website in body, link at end of body, repeated URL at end of long post: Considering Pro Test 180 makes them look leaner, by uah on askubuntu.com

[ SmokeDetector | MS ] Bad keyword in body, blacklisted website in body, link at end of body, pattern-matching website in body, repeated URL at end of long post: the blood pumping to your by dfgdfg on askubuntu.com

tpu- by DavidPostill on Shipping scales like the Dymo S180 can weigh up items 180kg [MS]

tpu- by DavidPostill on Considering Pro Test 180 makes them look leaner, [MS]

tpu- by DavidPostill

@Magisch before that it turned out to be a good way to find interesting suspended users ...

I notice Borodin too (though that's a shorter suspension)

@tripleee You can easily fork it by replacing the reputation part of the where with u.Reputation = 1

12:54 PM

@Magisch yeah I realize (-:

Surely I don't need to tell you that since you're better in SQL then me anyways

[ SmokeDetector | MS ] Bad keyword in title, blacklisted website in body, link at end of body, pattern-matching website in body, repeated URL at end of long post: Muscle Boost XT@Male Muscle Shop by saadinomber on askubuntu.com

@Magisch actually I don't think so, my SQL really sucks

tpu- by suraj

[ SmokeDetector | MS ] Bad keyword in body: Is my RNA-seq experimental design correct to use it for SNP calling? by Nina G on biology.SE

suraj

@SmokeDetector why

1:00 PM

@suraj Body - Position 489-497: Illumina

fp- by suraj

@SmokeDetector my bad (added by yours truly Jul 17)

CI on 17d3c8d succeeded. Message contains 'autopull', pulling...

[ SmokeDetector ] SmokeDetector started at rev 17d3c8d (tripleee: illumina: add \W?sd to fix FPs --autopull) (running on ArtOfCode/EC2)

Restart: API quota is 7592.

[ SmokeDetector | MS ] Manually reported answer (batch report: post 1 out of 2): Web application to show busy/free information from a Google Calendar by Lauren on productivity.SE

[ SmokeDetector | MS ] Manually reported answer (batch report: post 2 out of 2): How do I motivate myself to walk every day? by Lauren on productivity.SE

tpu- by Glorfindel on How do I motivate myself to walk every day? [MS]

tpu- by Glorfindel

spam? stackoverflow.com/questions/18506146/…

@Magisch I think that's showing all users with >20 votes and <2000 reputation

Since Upvoting_Occured is going to return the last day they had a vote, rather than the day they had 20 votes

!!/watch viviangist\.com\.ng

1:07 PM

@Glorfindel Added viviangist\.com\.ng to watchlist

@TajyMany You need a beefier machine :)

@PetterFriberg No idea. I don't have 10K to see deleted stuff on SO.

[ SmokeDetector | MS ] Bad keyword with email in answer, email in answer, pattern-matching email in answer: Can cancelling of the newly opened credit cards increase a credit score? by lena on money.SE

@PetterFriberg Bhargav already deleted it as spam

tpu- by Glorfindel

1:07 PM

ok thanks, yeah we got it from the Nato bot, (it was still alive when I posted :D)

CI on b3eecf7 succeeded. Message contains 'autopull', pulling...

[ SmokeDetector ] SmokeDetector started at rev b3eecf7 (SmokeDetector: Auto watch of viviangist\.com\.ng by Glorfindel --autopull) (running on ArtOfCode/EC2)

Restart: API quota is 7545.

@Rob It groups by CreationDate ( so by every day)

[ SmokeDetector | MS ] Link at end of answer: Как после загрузки скриншота с экрана в файл его анализировать в пикселях? by AGV on ru.stackoverflow.com

And only selects the day where 20+ votes occured

@PetterFriberg Hey Petter

@SmokeDetector f

1:10 PM

I made a script to find potential serial upvoting

naa- by suraj

@Magisch Oh, I'm completely blind - my bad

Missed that part

@Magisch \o, nice

[ SmokeDetector ] SmokeDetector started at rev b3eecf7 (SmokeDetector: Auto watch of viviangist\.com\.ng by Glorfindel --autopull) (running on ArtOfCode/EC2)

Restart: API quota is 7531.

@Magisch if you like to output alert in chat, your welcome in SOBotics (we monitor mortarboard badge for that)

1:12 PM

@PetterFriberg My script outputs all users under 2000 reputation that obtained over 20 upvotes on over 10 different posts in a day

seems like a good method to find socks

It's a reasonably good predictor of serial upvote recievers

I'm using it as part of my ongoing effort to flush out people the serial voting script didn't find

yeah our current method which is not that effective simple alerts when someone get a mortarboard badge (hence 200 points), we check if low-rep with no bounty, it's often worth a flag.

@PetterFriberg You're welcome to incorporate my script into your reporting

I'm still toying with some other heuristics so it may yet change

[ SmokeDetector | MS ] Bad keyword in body, bad keyword in title, blacklisted website in body, pattern-matching website in body: This desires your body pro test 180 by Ausrrich on graphicdesign.SE

tpu- by Glorfindel

1:16 PM

@Magisch join us in SOBotics in which language is it?, one should probably make a specific bot for that (the badge bot, is more a general watching badges...)

@PetterFriberg It's a SEDE query

aah ok, nice yeah pass a link, lets see if anybody picks it up in bot dev

[ SmokeDetector | MS ] Bad keyword in body, bad keyword in title, blacklisted website in body, link at end of body, pattern-matching website in body: measure muscle boost x the calories in your by kashokni on superuser.com

tpu- by Glorfindel

1:30 PM

[ SmokeDetector | MS ] URL-only title, bad keyword in body, bad keyword in title, link at end of body, pattern-matching product name in body, +5 more: firstnutritionfacts.com/dxl-male-enhancement/ by sadas on askubuntu.com

tpu- by Henders

Dang

My serial upvoter script is scarily accurate

10/10 people I've checked so far have all had unreversed serial upvoting going on

Nice :)

1:46 PM

API quota rolled over with 7113 requests remaining. Current quota: 19999.

stackoverflow: 104
math: 58
superuser: 16
askubuntu: 14
travel: 13
codegolf: 13
serverfault: 12
physics: 11
salesforce: 9
english: 9
electronics: 8
security: 5
sharepoint: 5
mathematica: 5
magento: 4
gis: 4
gaming: 4
tex: 4
bitcoin: 4
wordpress: 4
unix: 4
ru.stackoverflow: 3
mathoverflow.net: 3
pt.stackoverflow: 3
es.stackoverflow: 3
stats: 3
workplace: 3
meta: 3
android: 3
movies: 3
drupal: 3
ell: 3
interpersonal: 2
photo: 2
rus: 2
arduino: 2
gamedev: 2
scifi: 2
skeptics: 2
fitness: 2
emacs: 2

1:56 PM

[ SmokeDetector | MS ] Title has only one unique char: ................................ by Dattier on math.SE

[ SmokeDetector | MS ] Title has only one unique char: .......................................... by Dattier on math.SE

[ SmokeDetector | MS ] Title has only one unique char: ......................................... by Dattier on math.SE

Jack Parkinson

What is this guy doing?

[ SmokeDetector | MS ] Title has only one unique char: ......................................... by Dattier on math.SE

tp- by ArtOfCode on ................................ [MS]

tp- by ArtOfCode on .......................................... [MS]

tp- by ArtOfCode on ......................................... [MS]

tp- by ArtOfCode

[ SmokeDetector | MS ] Title has only one unique char: ......................................... by Dattier on math.SE

tp- by ArtOfCode

Mods alerted

[ SmokeDetector | MS ] Title has only one unique char: ......................................... by Dattier on math.SE

tp- by ArtOfCode

[ SmokeDetector | MS ] Title has only one unique char: ................................. by Dattier on math.SE

...

2:10 PM

[ SmokeDetector | MS ] Title has only one unique char: ......................................... by Dattier on math.SE

tp- by ArtOfCode

tp- by ArtOfCode on ................................. [MS]

I definitely already responded to those...

NOT BEFORE ME MUAHAHAHA

[ SmokeDetector | MS ] Mostly dots in title, title has only one unique char: ................................... by Dattier on math.SE

tp- by ArtOfCode

@ArtOfCode I already responded to the one above before I wrote '...'

Maybe FIRE had an error..

shrug

2:15 PM

rug

oh, it's probably because multiple records

Ah

Explains it

aaand suspended

SD - v 2- 2v - v - v 4- 4v 3- k - k - k

Nice

2:27 PM

!!/report mathoverflow.net/q/278430/70594

[ SmokeDetector | MS ] Manually reported question: Why can't Congress pass a law to prevent the press from continuing to criticize the President? by ansain on mathoverflow.net

SD k

2:42 PM

[ SmokeDetector | MS ] URL in title, bad keyword in body, bad keyword in title, pattern-matching product name in body, pattern-matching website in body, +3 more: firstnutritionfacts.com/dxl-male-enhancement/ by asdsdf on graphicdesign.SE

tpu- by Glorfindel

quartata

2:55 PM

@SmokeDetector lolololololol

3:07 PM

[ SmokeDetector | MS ] Username similar to website in answer: Does penetration by mistake constitutes zina? by Seekers Elite on islam.SE

3:39 PM

[ SmokeDetector | MS ] Pattern-matching website in body: Fade repeating after replacing image while scrolling with javascript by exceso on stackoverflow.com

3:59 PM

fp- by DavidPostill

fp- by DavidPostill on Does penetration by mistake constitutes zina? [MS]

@Magisch Linky?

@DavidPostill data.stackexchange.com/stackoverflow/query/708836/…

4:15 PM

@Henders Thanks. Will have a play with it.

@Magisch Switching sites doesn't work :/

Because you need to change the profile URL at the top I think...

@DavidPostill You can recompose in AU

I think

@Henders That fixes it for me. Thanks.

@DavidPostill data.stackexchange.com/superuser/query/708942/…

@Magisch Thanks.

4:23 PM

I also have one for voting rings and one for serial downvoting now

Getting the hang of SEDE slowly

@Magisch Link? :O

That's really impressive

data.stackexchange.com/stackoverflow/query/708918/… <-- Voting Ring Identifier

data.stackexchange.com/stackoverflow/query/708937/… <-- Serial Downvoting Identifier

A voting ring being a group of accounts that all vote for each other?

It's serial upvoting by multiple accounts

Oh

4:25 PM

My query filters for patterns of equally many votes on multiple posts in a single day on low rep users

Yes I'm somewhat obsessed with serial voting

17 hours, still going strong

@TajyMany did you want me to connect and debug that query?

@ArtOfCode I wish I could let you. my life has been frozen for 17 hours as this xml file (posts.xml) has been importing

It's 53 GB, but I never knew it would take 17 hours?!?

that sounds broken

should i.... restart?

4:37 PM

No

No, I'd probably leave it

How beefy is your environment?

you could open another DB client, though, and query the posts table to see how far it's got

16 GB RAM, i7-7800k, GTX 1070

not the beefiest

but it's good

I'll try querying it

What type of hard drive?

4:39 PM

DB import will be blocking on CPU

and IO

SSD?

[ SmokeDetector | MS ] Potentially bad keyword in answer: How would I validate digital signature for PDFs in linux? by Leila Holmann on superuser.com

HDD

Hmmm the Posts table doesn't exist

maybe I should start a quick Google Cloud instance and just do it there

with plenty memory and cores

17 hours seems excessive for that. I have similar stats (worse GPU), but have an SSD. It can import Posts.xml in less than 2 hours

I'll try out Google VM and let you know

4:40 PM

[ SmokeDetector | MS ] Bad keyword in title: Excel formula for ideal body weight by khutch on stackoverflow.com

I've got to go for now, though.

Goodbye :)

@SmokeDetector crappy, but disclosed

(I'll be back in an hour)

fp- by ArtOfCode

4:57 PM

I contacted shog with my queries

Here's hoping he'll find them useful

5:09 PM

fp- by DavidPostill on How would I validate digital signature for PDFs in linux? [MS]

[ SmokeDetector | MS ] Pattern-matching website in answer: How to show different sizes on select of particular PRODUCT in Woocommerce by Akhil Sarikonda on stackoverflow.com

5:39 PM

tpu- by DavidPostill

5:54 PM

[ SmokeDetector | MS ] Repeating characters in answer: Synonym for "compelling"/"inviting" starting with R by Carl Witthoft on english.SE

@ArtOfCode Getting the server ready now.

@JF That's a good idea. I'll get to that soon.

fp- by Glorfindel

6:18 PM

I think a server with 130 GB RAM and 22 Cores should cut it @Andy @ArtOfCode?

And 200 GB SSD

Instance deployed. I'll install MySQL, import, and let you know so you can debug, @ArtOfCode

50 GB big enough for the dump?

It's 200 GB, sorry

CaffeineAddiction

so something like

@TajyMany I'm not a mysql expert, but I doubt it's going to use all those cores on import

Well, it's better to have more regardless. I'll google search if there's a way to make it use them, if they're required that is.

How many posts are there in Posts.xml?

31,017,889

?

this file is so big, it even takes time to split it up into 1 GB chunks

wow

@ArtOfCode Maybe try debugging in SEDE?

6:55 PM

[ SmokeDetector | MS ] Few unique characters in answer, no whitespace in answer: How do you access a URL in an array and send a user to it by sammyam on stackoverflow.com

[ SmokeDetector | MS ] Username similar to website in answer: How do I connect my local DNS server to provider's? by xobotun on superuser.com

7:11 PM

Restart: API quota is 16394.

tpu- by DavidPostill on How do you access a URL in an array and send a user to it [MS]

[ SmokeDetector | MS ] Few unique characters in body: bad website bad website bad website bad website by ahsiban on stackoverflow.com

7:31 PM

@ArtOfCode I've finally got it imported.

But only 900,000 rows. It's enough for me anyway

I'll try running the query you snet

sent

[ SmokeDetector | MS ] Pattern-matching product name in body: LTspice gain enhancement sallen and key highpass simulation by BRogers on electronics.SE

fp- by thesecretmaster

[ SmokeDetector | MS ] Bad keyword in body: Find min/max x,y in each vector<vector<Point>> by Magda1993 on stackoverflow.com

fp- by DavidPostill

New metasmoke user 'Marox44' created

[ SmokeDetector | MS ] Email in answer: How to say one minute past midnight in military time? by Tailor Industries on english.SE

[ SmokeDetector | MS ] Bad keyword with email in body: I am having error while fetching data from quandl using python by akib007 on stackoverflow.com

fp- by DavidPostill on How to say one minute past midnight in military time? [MS]

8:36 PM

[ SmokeDetector | MS ] Bad keyword with email in answer: How is IRC so secure/untraceable that hackers and pirates use it to communicate? by Alex Ferreira on security.SE

tpu- by DavidPostill

9:35 PM

[ SmokeDetector | MS ] Few unique characters in body, title has only one unique char: --------------- by SamEsla on money.SE

tp- by ArtOfCode

tpu- by rene