Move all accounts with certain Drupal role into CIviCRM group

Question

We are trying to delete all spam accounts out of both Drupal and CiviCRM. We have a module set up to find all spam account in Drupal and set them to the role of "spam account". There are now about 40,000 accounts with that role.

Now, we are trying to deal with those accounts on the Civi side. I am trying to use CiviCRM Group ROles Sync to sync all of the Drupal accounts with the role of spam to the CiviCRM group of Spam, but keep getting a "503 First byte timeout" error. I was told that the error is caused by it taking longer than 60 seconds to respond, which would make sense considering there are 40,000 accounts. But, I tried it with 20 accounts, and still got that error.

Is there another way to go about syncing the Drupal and Civi roles/groups, or another way to deal with spam accounts in CiviCRM?

I am using Drupal 7 and CiviCRM 4.6.28.

Answer 1

If you're feeling brave, and you've got a test site to try this out on first, this should be doable almost instantly via a SQL statement.

To simplify this query, first look up the group ID (SELECT id FROM civicrm_group WHERE name = 'Spam Group') and the Drupal role ID (`SELECT rid FROM role WHERE name = 'spam account').

Note: If you have separate databases for Drupal and CiviCRM, you'll need to preface references to tables with the database name. I'm assuming some basic SQL savviness here - my apologies if this isn't helpful as a result!

Once you have the role ID and group ID (we'll assume "12" and "23" respectively below), do the following:

INSERT INTO civicrm_group_contact (group_id, contact_id) 
SELECT '23', civicrm_uf_match.contact_id
FROM users_roles
JOIN civicrm_uf_match ON uid = contact_id
WHERE rid = 12;

Answer 2

in the past, I've used this simple php script to get rid of spam accounts/contacts in bulk.

I use a csv file, created by doing a search in civi and then exporting that as csv, but in your case you could also just replace the csv with code that creates an array from all users in the role you created.

here is the source of my script, save it as delete-bad-contact-and-users.php if you want to use it.; I run this via ssh using drush. Put my csv and the script somewhere in the drupal site directory and then drush php-script delete-bad-contact-and-users.php

it will spit out the cid and uid of each account as it deletes them so you will know it's running and when it's done

<?php

// This is a brute force script to delete civicrm contacts
// and drupal user accounts 
// initially created to get rid of a ton of spam accounts 
// on a site we (Openflows.com) were responsible for cleaning up

// it takes a csv file as input via drush 

// from within the site's root, whereever you have the csv 
// drush php-script delete-bad-contact-and-users.php 
// sudo might be necessary depending on the server/site 


// the only column in the csv we care about here is the 
// civiCRM Contact's cid 


// this function borrowed from 
// http://www.oodlestechnologies.com/blogs/Converting-CSV-file-into-an-Array-in-PHP

function ImportCSV2Array($filename){
  $row = 0;
  $col = 0;

  $handle = @fopen($filename, "r");
  if ($handle){
    while (($row = fgetcsv($handle, 4096)) !== false) {
      if (empty($fields)) {
        $fields = $row;
        continue;
      }

      foreach ($row as $k=>$value) {
        $results[$col][$fields[$k]] = $value;
      }
      $col++;
      unset($row);
    }
    if (!feof($handle)) {
      echo "Error: unexpected fgets() failn";
    }
  fclose($handle);
  }

  return $results;
}


$csv_source =  "users_to_kill.csv";

civicrm_initialize();

//import spreadsheet

  $csvArray = ImportCSV2Array($csv_source);


//loop over rows
  foreach ($csvArray as $row){

//set contact id to variable
//print_r($csvArray);
  $cid_to_kill = $row['cid'];
//get uid from contact id, set into variable
$uid_to_kill = CRM_Core_BAO_UFMatch::getUFId($cid_to_kill);
print " contact id: ".$cid_to_kill; 
print "-----";
print "uid: ".$uid_to_kill;
print "------ \n";
//delete contact

    $result = civicrm_api3('Contact', 'delete', array(
      'sequential' => 1,
      'id' => $cid_to_kill,
      'options' => array('skip_undelete' => 1,'check_permissions' => 0),
    ));

//delete user

      if (isset($uid_to_kill)) {
        user_delete($uid_to_kill);
      }


  }