Dealing with CiviCRM contact records of Drupal blocked accounts

Question

Is there a way to automate removing the contact records of Drupal accounts that are blocked such as because of spam? I am using the spambot module to block accounts and remove their spam.

I fear that there are thousands of bogus records in our CiviCRM with no way to confirm their legitimacy.

Drupal 7 and CiviCRM 4.7.27

Answer 1

Spitballing as I haven't tried this, but if the CiviGroup Roles Sync module allows you to sync a Drupal role into a CiviCRM group, you could:

1. Add all blocked accounts to a (new/temporary) Drupal role.
2. Sync that role to a group in CiviCRM.
3. Load all contacts in that group in the CiviCRM UI and delete/trash them.
4. Delete Drupal users (and temp role) if desired.

Answer 2

We didn't find a great way of dealing with this. I think we found the best approach was to use Drupal Views and VBO and probably civicrm entities so that we could grab the civi records of the users who we wanted to cull, and then delete the civi record and then delete the user record. If I can dig out the View I will share

Answer 3

We also find a lot of spam contacts in civi have a first name which matches the last name. In which case we run this query to find them, export them if need be and let client eyeball them

SELECT civicrm_contact.id, civicrm_contact.first_name, 
civicrm_contact.last_name, civicrm_email.email
FROM civicrm_contact
LEFT JOIN civicrm_email 
ON civicrm_contact.id=civicrm_email.contact_id
WHERE civicrm_contact.first_name = civicrm_contact.last_name 
AND first_name NOT LIKE '%test%';

Then get them in to Trash (avoiding any exceptions eg 1119, etc) with

UPDATE civicrm_contact
SET is_deleted = 1
WHERE civicrm_contact.first_name = civicrm_contact.last_name
AND first_name NOT LIKE '%test%'
AND civicrm_contact.id NOT IN (1199,1231,1279,1322,1334);

The use the UI to 'delete permanently' to ensure all Activities are removed etc. Alternatively you could add them to a civi group to review and then use Delete Pemr