search-and-destroy-spammers.md

description
Monthly job to search and ban spammers from the platform

Search & destroy spammers

Job Info

{% hint style="info" %} Assignee: Benjamin Piouffle
Frequency: Monthly {% endhint %}

Procedure

Search for spammers

• Go to https://opencollective.com/search
• Or use a query like the following one:

SELECT concat('https://opencollective.com/', slug) AS profile, slug, name, website, description, "longDescription" FROM "Collectives"
WHERE  (
    slug LIKE '%keto%'  -- replate by searched keywords
    OR     website LIKE '%keto%'
    OR     description LIKE '%keto%'
    OR     "longDescription" LIKE '%keto%'
) AND "deletedAt" IS NULL

Out of the current research, the keywords that are interesting to look for are:

keto, pills, sex, drug, porn, casino, webcam

Ban spammers

Refer to this page for more info about banning users and collectives.

Logs

date	nb_deleted_profiles	deleted_users	nb_deleted_tiers	nb_deleted_members	nb_deleted_updates	nb_deleted_payment_methods	nb_deleted_connected_accounts	nb_deleted_conversations	nb_deleted_conversation_followers	nb_deleted_comments	nb_deleted_expenses	nb_deleted_applications	nb_deleted_orders	nb_deleted_notifications	nb_deleted_users	deleted_profiles_ids
2020-01-06	438	232	4	222	50	172	0	0	0	0	12	1	0	0	232	{70569,47767,47782,73216,73224,74094,70572,73238,66133,47874,47886,47894,75077,49114,75085,70091,74111,49125,69238,75076,51551,51552,51554,51559,75103,74403,21276,75110,27255,27257,27258,27259,57090,57087,25974,67019,57108,48105,48114,48137,48124,55619,55623,46031,77340,49261,49285,58618,45240,75293,58634,61253,50092,59919,50094,46887,59930,50111,46134,46138,77396,51149,50120,49417,49418,48411,50506,50520,48556,75304,50535,48568,39075,50836,77459,77461,40399,75812,73420,73428,48673,55732,55737,48689,55750,55756,50217,50223,55763,50250,45634,46503,47609,45676,47611,68381,48782,48775,47649,47653,45732,57282,48798,48807,57307,49756,55857,73473,75821,75824,73466,46632,49798,49824,44619,44630,44792,44789,44793,45896,44980,46770,50375,46771,46767,47186,45093,48981,49100,47645,55892,60101,55896,75447,55899,60106,51051,51269,51444,61416,77562,68972,53859,60136,53861,53001,53862,61428,60147,77589,77598,54821,54824,77627,57431,71503,62462,76045,57449,68307,62477,62489,62497,72239,72243,72244,56032,60221,60224,72265,56048,60235,62519,57540,62521,62523,67091,67092,67093,67097,67106,67105,52216,57575,62536,57585,57587,67129,52365,52378,52391,76121,52509,52537,53182,65589,52692,53867,77773,53873,65611,52849,54041,53883,77794,77796,53887,65620,58998,72353,77797,66331,65635,67218,66351,67915,67223,77843,67237,67496,67244,61698,53190,53335,77853,71537,57689,64669,67755,67536,53367,62668,62673,66424,67769,64705,67772,64706,67560,70099,71587,67567,73496,70103,71593,71595,67931,70114,60498,53644,53497,53650,53504,53526,53630,57805,76276,56381,76285,60550,68006,70779,67405,76360,57838,63632,66548,63886,66550,63888,70808,63890,63893,66559,72469,72474,72659,63904,63905,66573,72673,63675,64902,60657,61886,61887,64911,61890,72489,73061,58009,61901,69796,53886,53949,78044,78052,78061,60717,78065,78088,71001,78034,58121,54166,54167,76508,62980,58147,71767,69447,58154,59473,63007,60813,73110,60832,69417,59505,62054,59523,69435,59547,78295,62083,62096,62099,78315,78321,70389,78421,76717,58286,76726,56794,64089,76744,76755,56826,58334,58335,76789,76790,69592,61004,61006,59724,69599,66762,71197,78454,71200,71202,66767,61025,78471,54298,78481,78515,54296,54303,54320,54463,71995,58443,58444,61041,54498,71242,56967,61047,66770,61049,54583,58475,54609,54607,72829,66055,66062,59761,54791,54917,66799,63189,62272,62276,54955,62289,63210,55144,55150,66832,65398,66844,55405,55412,76957,78755,72075,72080,55517,78829,78830,76992,77002,79011,79009,79030,79032,79029,79028,69203,69199,79244,79240,79287,79286,79281,79299,69966,72081,72090,73671,73673,73927,73928,74607,74626,73956,72672,73726,74676,74682,74446,74879,75127,75337}
2020-02-17	867	520	11	454	129	323	1	33	33	50	66	3	1	0	520	{70569,75245,50053,69156,73219,73810,73225,70571,83568,69162,69165,74390,73235,66128,66131,74107,23028,69237,68762,68764,68765,68766,74406,28237,84696,67004,67029,67030,69379,69382,77326,84706,46021,84717,84754,84759,77351,84770,84771,85499,77359,77371,75285,50093,77391,77409,75430,75433,50213,80062,80115,85498,85502,45477,60007,45478,83569,83590,83604,40288,60021,83613,73423,73436,83646,70743,77524,83662,74132,74079,82426,82427,82455,82456,82457,83653,83658,82474,75822,46640,49797,68805,84943,84940,84967,84963,84968,84969,84970,84971,84972,84973,84981,84994,85027,84959,85411,85417,85138,68821,85171,85175,68841,77556,80256,51667,80266,80277,80283,77567,77574,77580,68990,75502,85182,85185,85188,81445,81455,81458,81465,81485,81503,81494,81499,85238,81482,77617,81498,54184,77623,80417,80411,77626,80419,80406,80407,85244,85261,85262,80424,80429,57441,68300,71507,62465,51699,68311,71523,68325,71529,85426,81633,56035,67041,76047,64447,72260,64451,80462,85448,68498,64461,68506,85469,80472,64466,80489,60248,80480,68521,56069,80484,67087,64492,67094,69141,76095,81638,81639,81640,72286,81649,81674,68588,76124,81686,81657,81689,81705,81673,52521,65590,77770,52663,52679,65597,66318,80586,80595,66326,77800,77805,72352,67466,67470,67215,64634,56222,77832,84069,84072,64640,84073,67225,67483,67226,67699,68121,59034,80699,84079,67489,84084,68127,80706,84077,68129,63502,77844,77851,64659,67259,61710,77863,64670,61717,71546,84093,84114,84103,84123,67526,18368,84124,84128,84130,84134,84136,84140,67534,64695,53352,64707,64709,72461,67926,70110,60487,67938,70118,60489,67944,60493,67952,67342,60502,60516,84358,76272,80712,80729,76278,76279,73883,65795,80743,80755,70763,80771,80772,80773,80777,70773,80788,63610,67396,76304,56417,63619,76358,67682,72998,66554,80919,80911,80920,72472,70820,72661,72482,66577,72667,63913,64880,76405,64881,63920,64884,64887,84445,72688,84458,61875,57985,84452,84461,84473,84467,71762,64908,69786,72487,53990,60674,72235,82782,82783,82795,82803,61911,69803,82819,82802,56547,69814,63750,60707,78007,78057,72533,68948,76493,82788,53999,56574,78074,70993,78083,78087,78080,78085,71003,70987,84491,84504,84507,84509,62975,71009,76515,73081,71773,73648,69390,65075,76542,71788,60818,73274,69406,73281,63022,70334,70335,62037,70339,70394,73295,63039,71831,65122,70326,65129,78272,70366,78289,65137,78299,62081,63074,82994,74479,82999,82219,82227,82221,69565,82243,83011,83036,83021,83049,76730,83070,64106,82275,82274,56844,69580,71189,69584,69588,64146,71988,61015,64161,78455,71205,78470,71207,78484,78490,72654,71214,76853,71223,71228,82286,82405,66025,66026,66033,58454,66039,72005,66043,56980,66773,72826,54605,66779,66784,82407,66065,62261,63188,57037,65367,65371,65377,63207,66825,66828,84652,70989,72054,66842,65412,80943,55394,85622,85627,80962,78785,65424,78793,80978,80968,76971,80991,80990,80992,81104,81102,81105,76993,77007,77009,79000,77010,77019,79051,77036,82832,82837,82825,69393,79215,79282,82987,82990,82669,69965,81118,81121,81123,81119,81148,81136,81163,81243,77093,82760,82761,85992,70324,85989,70552,70558,70560,82009,82003,82007,73658,82042,79462,79461,79491,79508,79520,82097,73666,85639,85642,85663,85638,85648,85661,79538,82213,85671,79539,73922,73923,85767,79705,79709,85809,74629,73960,74640,74647,81276,79714,79721,79736,73730,81286,79757,79785,79786,81851,79789,79810,81336,79848,79906,74845,73701,74669,81974,74849,74671,74853,81988,74091,74095,52392,79915,79929,79928,79922,79933,79948,79947,74877,79942,79965,79973,74890,74907,83293,83281,83080,83316,83091,75292,83344,83338,83380,83819,83820,83825,83821,83558,83831,83562,83839,83849,83846,83564,83865,83567,82484,83863,84064,84059,84066,83581,86007,86015,86019,86042,86062,86070,84766,88637,86623,90664,91774,88638,90360,91820,91791,88347,88644,89465,87566,86232,87628,86240,87626,87630,89199,91622,91626,92245,86261,86260,86287,86307,86328,86627,86535,88624,89440,88856,89242,89251,89246,89444,91786,92007,86556,92080,86536,86555,86558,86576,86581,86644,86699,92081,92085,92256,86787,86829,87530,90516,86897,92296,92302,86835,92297,92307,92319,89139,86840,89162,86803,88350,86809,86810,86880,86885,86889,86884,86899,86892,86903,86900,86917,88356,87880,87659,87690,87660,87116,90144,87138,87119,87270,87271,90150,87274,87292,87320,87327,87328,87330,87338,87340,87482,88317,92281,87557,87565,92333,92250,91946,92340,91952,92337,92254,92637,87930,87656,87671,87672,87673,87682,87718,87724,87735,87859,87864,92669,92656,87869,92686,87900,87905,87906,87931,87935,87949,87950,91506,91508,88712,91492,91507,91497,88735,88089,88100,88120,88139,88141,88155,88208,88439,88571,88586,88831,88787,88785,88793,88852,88850,88844,88845,89047,89160,89218,89450,89240,89263,89262,89265,89460,89420,89438,89528,89435,89439,89421,89424,89473,89530,89503,89526,89590,89607,89705,89725,89742,89754,89758,89766,89775,89787,89799,89943,89828,90166,90269,89915,89965,89983,89995,89990,90019,90020,90154,90158,90161,90179,90234,90239,90255,90425,90870,90873,90628,90883,90810,90824,90841,90840,92034,91699,92029,91705,91706,91141,91152,91170,91189,91190,91192,91726,92055,92056,92066,91740,91390,91369,91405,91743,91414,91960,91976,91757,91980,91724,91769,91771,91775,91787,91750,91770,92076,92083,92242,92259}
2020-04-02	413	332	6	116	14	67	0	61	62	77	17	3	0	0	332	{96269,96800,96810,96595,96523,97321,96539,96258,96283,96616,97077,96345,96883,96887,97329,96597,96814,96869,96893,97084,97097,98328,98374,98657,98663,98665,86888,98672,98680,98687,98407,98214,99003,99009,99046,99081,98701,98401,99006,98712,98710,98732,98746,98698,98752,97703,98745,97728,98772,98776,99175,97117,97338,97340,97339,96292,97136,97344,97260,97115,97367,97368,97379,97360,97383,97384,97420,97421,97752,97767,97770,97768,97765,97673,97755,97666,97907,97904,97914,97939,97905,97953,97982,97985,97986,98418,98424,98442,98441,98440,98468,96536,96877,98479,98495,98494,98133,96859,96634,98144,96641,96549,96560,96551,96665,96255,96305,96783,98157,96510,96528,96576,96538,96541,96680,96776,96790,96798,96855,96897,96901,98159,98175,98174,97063,97061,97071,97073,97137,98482,98180,97695,97709,97692,97310,97316,97675,98212,98230,98217,98390,97319,97322,97336,97532,97542,97662,97672,97674,97724,98140,98145,98197,98378,98388,98443,98474,98666,98688,98999,92762,98737,98750,98995,99018,99017,99056,99074,99083,99099,99388,99575,99581,99619,99620,100705,100706,100707,99669,99919,100415,99927,99929,100042,100719,100720,99931,99623,99658,100728,100731,99605,99592,99590,99616,99630,99635,99637,99641,99652,99676,99684,99689,99710,99724,99714,99894,100742,99898,100744,100745,100416,100842,100843,100748,99906,99921,99936,99947,99961,99971,99973,99974,101321,99988,100015,100304,100332,100338,101327,100481,100396,101025,101029,101027,100340,100381,100373,100384,100420,100445,100454,100450,100480,100674,100765,100685,100688,100687,101077,100779,100784,100797,101112,101010,101019,101024,101028,101042,101063,101073,101074,101097,101131,101133,101235,101330,101334,101347,101352,101369,101371,101374,101395,101407,101419,101673,101996,103215,105009,102384,102029,102391,105950,102499,106006,102003,102017,102015,102035,102328,102290,102307,102308,102347,103216,102367,102376,102382,96270,98664,98333,98754,96350,97345,97380,97754,97908,97671,97941,98220,99178,98409,98497,103575,97697,97698,98670,99935,99634,99653,99727,99922,99923,99924,99925,100305,104611,104613,100446,104061,102912,101370,102917,102920,102046,102393,102515,102927,102336,102590,102591,102767,103276,102707,103277,102768,103286,102799,102727,102732,102772,102972,103905,104725,105743,104559,104560,103171,103172,103249,103293,103309,103348,104256,103614,103615,105226,104299,103582,104235,104302,103599,103595,104306,103625,103636,103637,103734,103736,103857,103909,103933,103935,103943,103965,104084,104086,104236,104266,104401,104496,104500,104502,104505,104566,105052,105067,105254,105374,105377,105381,105522,105547,105560,105707,105739,105740,105770,105921,105922,105956,105994}
2020-06-26	332	262	4	92	38	58	1	67	67	68	3	0	1	0	262	{132146,132255,132264,132327,132330,132336,132339,132342,132346,132355,132374,132384,132390,132391,132392,132409,132412,132421,132433,132467,132499,132503,138277,138278,138303,135462,138320,135478,135481,138348,138349,138350,135510,138384,135544,132660,132661,132662,135583,135591,132728,135641,135652,135667,138552,138553,138554,138558,138563,138569,138573,138576,138577,135747,135754,138596,138597,138608,135784,135785,138629,138630,138637,138644,138645,135816,138651,138658,132923,132928,132931,132934,132936,132935,132938,132944,132956,132959,132960,132964,132965,135879,135882,132973,135885,132977,138720,135900,135949,135955,133100,133103,138877,136061,138881,133151,136075,138900,138913,138919,138930,138953,136138,136140,136143,136146,138968,138969,138973,138982,136178,136179,136181,139038,133328,133332,136238,133340,133343,133372,133377,133378,133380,136293,133399,133400,133457,133471,133480,139224,139226,136438,133550,139262,139275,139282,133597,139306,136531,136535,136576,136577,136591,136605,133716,133717,133730,133735,136654,133744,136660,136804,136931,136932,136933,136937,136974,136987,134083,134085,137006,137009,134090,137022,134107,134108,134112,134116,134117,134121,134158,134166,137109,134196,137151,134229,134236,134272,134296,137272,137279,137282,137285,137287,137288,137303,137343,137355,137356,134463,134466,134471,134472,137389,134483,134485,134496,137413,134499,134549,134560,134561,134564,134565,134569,134598,130893,134642,134645,130923,134648,134573,137558,137559,137582,137583,137606,131015,134750,134754,134755,134759,134766,134773,134782,134786,134802,134807,134810,137733,134840,134841,134849,131136,134851,131138,131141,137769,131163,134906,137771,131179,137772,137778,134918,134919,131193,137790,134938,134940,137798,134941,137802,131210,137813,131216,137820,137829,131236,137836,137845,131253,137854,137859,135018,135023,137889,137892,135032,135035,137897,137903,137906,137925,137931,137932,137942,137958,137974,131357,131377,138003,135145,138046,138058,135300,131555,135309,131556,131572,135312,131576,135314,131580,135326,135327,135338,135340,131594,131605,138199,135355,135356,131623,131625,131626,131631,135380,138219,138209,138227,138229,138230,138232,131652,131664,131674,131955,131956,131966,131972,131983,131999,132005,132006,132013,132037,132062,132075,132083,132086,132174}
2020-07-31	341	254	4	98	27	80	0	108	112	0	0	0	0	0	254	{140892,143586,143614,143617,143622,143679,143686,143636,143637,143642,140974,143712,140977,143715,143720,143721,143673,148762,143787,143788,146473,146494,146496,141108,146498,141112,146500,141119,146507,146535,146536,143890,146549,143905,141189,141195,143946,143947,141219,141230,143961,143965,143972,143971,143979,143995,144004,141255,144010,144041,146756,146771,141400,141411,146790,146794,141429,146819,141460,141465,141505,141509,141512,146891,144264,144266,144271,144288,141569,144329,146990,147012,147021,141677,141678,141679,147031,141683,147039,147040,141698,147055,147054,141711,147075,144458,147089,141731,144471,147096,141743,147098,141749,144479,144483,141779,144495,144512,144515,141805,147155,147156,141817,144525,144527,144537,144554,144561,147214,147217,144572,141909,141910,141935,141943,147351,147380,139435,142049,147383,147410,147412,147420,147421,147422,147428,142135,147446,149805,147465,139533,147471,139544,147477,142165,147478,147479,142166,142167,139551,139552,142171,139557,142184,142186,142191,139573,139574,142194,142195,139583,142201,139584,142206,139590,142208,142209,144903,142211,147524,144909,139598,147530,144916,144922,142233,142234,142236,144932,144933,144940,144941,144942,144944,147582,147585,142293,147601,147603,139683,145000,145024,142321,142325,145052,142342,147711,147727,147730,145130,142421,142456,139847,139844,139852,139864,142466,142479,142480,147756,142486,147757,139896,142493,139898,142496,139900,142497,142498,145238,142499,142503,147785,145244,145249,142546,147822,142563,147837,145286,145292,150117,142582,142585,142590,142596,142599,142604,145330,147908,147909,142646,145354,142651,145368,140118,140127,145457,140144,140150,140155,140157,140164,140169,140173,140176,140190,142794,148084,140219,140222,140224,142816,142823,142827,142829,142835,142850,142856,142867,142868,140277,145614,142898,148086,145621,148093,142899,145627,148099,148100,148101,142921,142944,145697,145700,148173,148174,148190,148199,145773,140456,143065,140459,140467,140468,140479,140484,140490,145831,140502,140509,140518,143128,143136,140549,143181,140592,143189,143191,143193,143194,143199,143204,140612,143217,148402,143241,148404,143258,148408,148417,148419,146056,146077,146079,143352,146081,146082,146091,146099,140778,146100,140788,146137,140791,146140,140796,140798,146155,140809,146161,140822,140825,140838,140840,140857,146272,140885,93498,87889}