Answers.Body: <p>Spitballing as I haven't tried this, but if the <code>CiviGroup Roles Sync</code> module allows you to sync a Drupal role into a CiviCRM group, you could:</p>
  
  <ol>
  <li>Add all blocked accounts to a (new/temporary) Drupal role.</li>
  <li>Sync that role to a group in CiviCRM.</li>
  <li>Load all contacts in that group in the CiviCRM UI and delete/trash them.</li>
  <li>Delete Drupal users (and temp role) if desired.</li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-02T04:11:43.883Z
Answers.Text: That sounds super sensible - i forget that Group sync can run in that direction
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-10-02T17:47:18.700Z
Answers.Text: This is a great idea. I don't have a role created for blocked accounts. I'm trying to think of the other half of this, which would automatically give all blocked accounts a special role. Maybe a Drupal rule would suffice? Thanks for the ideas!
Answers.UserDisplayName: Christia
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-02T04:03:31.223Z
Answers.LastActivityDate: 2018-03-02T04:03:31.223Z
Answers.UserDisplayName: Laryn
Answers.Body: <p>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</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-28T01:11:48.303Z
Answers.Text: I found the module but seems it needs some branches pulling back to main so pls ping me in a week if this hasn't been updated
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-28T01:52:41.043Z
Answers.Text: I can build something like that. I already have views that use VBO and civicrm entities, but I was hoping to use it with spambot. I'll mull on that, good idea.
Answers.UserDisplayName: Christia
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-02-28T00:48:46.250Z
Answers.LastActivityDate: 2018-02-28T00:48:46.250Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>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</p>
  
  <pre><code>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%';
  </code></pre>
  
  <p>Then get them in to Trash (avoiding any exceptions eg 1119, etc) with</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-02T04:00:00.413Z
Answers.Text: I think there is also a custom search included for same first and last name in this extension: https://civicrm.org/extensions/aug-searches
Answers.UserDisplayName: Laryn
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-01T20:37:03.883Z
Answers.LastActivityDate: 2018-03-01T20:37:03.883Z
Answers.UserDisplayName: petednz - fuzion
Body: <p>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.</p>
  
  <p>I fear that there are thousands of bogus records in our CiviCRM with no way to confirm their legitimacy. </p>
  
  <p>Drupal 7 and CiviCRM 4.7.27</p>
  
Tags: <drupal><contacts><cms-integration><spam>
Title: Dealing with CiviCRM contact records of Drupal blocked accounts
ThreadText: # Dealing with CiviCRM contact records of Drupal blocked accounts
  
  (Asked by: username_0 on 2018-02-28)
  
  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
  
  **Tags:** drupal, contacts, cms-integration, spam
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2018-03-02. Score: 1 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_2**: That sounds super sensible - i forget that Group sync can run in that direction  
  > **username_0**: This is a great idea. I don't have a role created for blocked accounts. I'm trying to think of the other half of this, which would automatically give all blocked accounts a special role. Maybe a Drupal rule would suffice? Thanks for the ideas!  
  
  ---
  
  ### Answer by username_2 on 2018-02-28. Score: 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
  
  > **Comments:**
  > **username_2**: I found the module but seems it needs some branches pulling back to main so pls ping me in a week if this hasn't been updated  
  > **username_0**: I can build something like that. I already have views that use VBO and civicrm entities, but I was hoping to use it with spambot. I'll mull on that, good idea.  
  
  ---
  
  ### Answer by username_2 on 2018-03-01. Score: 1
  
  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
  
  > **Comments:**
  > **username_1**: I think there is also a custom search included for same first and last name in this extension: https://civicrm.org/extensions/aug-searches  
  
  ---
  
  