Answers.Body: <p>You can use Search Builder to make Smart Group for contacts where Sort Name like @ and First Name null</p>
  
  <p>Name or Email LIKE - '%@%' ...AND...
  First Name Is Null ...AND...
  Contact Type = Individual</p>
  
  <p>But I would then use VBO myself so that you can delete any Drupal users that are orphaned (ie no longer have a related civi contact)</p>
  
  <p>We built a View to do this which I can fire through to you if you have trouble creating it from scratch. Ping me in redmine (gratis)</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-13T01:21:02.070Z
Answers.LastActivityDate: 2018-09-13T01:21:02.070Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>You could use Search > Search Builder and search for Individuals whose First Name and Last Name are NULL (when this is the case, Civi will use the email address as the Display Name), select all of the search results and then create a Smart Group. You would then periodically review the contacts of the smart group by navigating to Contacts > Manage Groups and then clicking on the Contacts hyperlink to the right-hand side of the group and perform the desired actions.</p>
  
  <p><a href="https://i.sstatic.net/e7twR.jpg" rel="nofollow noreferrer"><img src="https://i.sstatic.net/e7twR.jpg" alt="enter image description here"></a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-13T13:08:09.697Z
Answers.Text: OK, but ... my Search Builder is broken : I don't have any "Select field", but only "Select Record Type" and "Operator" (runnning 5.4.0) : you can reproduce this bug on the demo configuration (https://civicrm.demo.civihosting.com, in 5.0.0), this way : Search Builder > Select "Individual", then go to Support > About CiviCRM, and then back : "Select Field" has disappeared : I have the same situation, but directly, without this forth and back operation. I will edit my Question to be more precise.
Answers.UserDisplayName: Jacques-Marie Moranne
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-13T14:44:30.810Z
Answers.Text: @Jacques-MarieMoranne You should open a separate question about why your Search Builder is broken.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-14T08:00:16.573Z
Answers.Text: Jon : OK, done : https://civicrm.stackexchange.com/questions/26511/search-builder-is-broken-civi-5-4-0-drupal-7-59
Answers.UserDisplayName: Jacques-Marie Moranne
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-14T08:01:14.693Z
Answers.Text: Any other solution than Search Builder ?
Answers.UserDisplayName: Jacques-Marie Moranne
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-14T18:04:29.870Z
Answers.Text: The only other way to search on NULL values other than Search Builder would be to use the API explorer which might be a bit more complex if you do not have a good understanding of the Civi DB structure. You will find documentation HERE: https://docs.civicrm.org/dev/en/latest/api/
Answers.UserDisplayName: Tamar Meir - Cividesk
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-15T08:42:48.813Z
Answers.Text: Tamar : yes, I feared that. I have never used API Explorer (I need to try), but, of course, I also thought, via PHPMyAdmin/SQL, directly list, export, and delete the pertainig lines in the database (civicrm_contact table), then delete the pertainig users (via Drupal People Delete menu). But I am not very fond of that solution : only in desperation.
Answers.UserDisplayName: Jacques-Marie Moranne
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-12T21:17:51.947Z
Answers.LastActivityDate: 2018-09-12T21:17:51.947Z
Answers.UserDisplayName: Tamar Meir - Cividesk
Body: <p>Though I use Captcha and reCaptcha, I have a lot of dummy users and Contacts which I can identify by the fact that their sort_name = display_name = email (or sort_name contains an "@").</p>
  
  <p>How can I delete those Users and Contacts ?</p>
  
  <p>My original idea was to create a Dynamic Group of those, then delete them after exporting their list to excel to delete the relevant Users. But, how to create such a Dynamic Group (based on what search criteria ?) ? </p>
  
  <p>An alternative would be to create a View (filtered on "sort_name contains '@'"), then use VBO to add them to a Group : I tried, but could not succeed).</p>
  
  <p>I tried using the Search Builder, but it is broken : I have openned a new post for that : <a href="https://civicrm.stackexchange.com/questions/26511/search-builder-is-broken-civi-5-4-0-drupal-7-59">Search Builder is broken (Civi 5.4.0 / Drupal 7.59)</a></p>
  
Tags: <contacts><user-account><search-builder>
Title: How to clean the Contacts and relevant Users
ThreadText: # How to clean the Contacts and relevant Users
  
  (Asked by: username_0 on 2018-09-12)
  
  Though I use Captcha and reCaptcha, I have a lot of dummy users and Contacts which I can identify by the fact that their sort\_name = display\_name = email (or sort\_name contains an "@").
  
  How can I delete those Users and Contacts ?
  
  My original idea was to create a Dynamic Group of those, then delete them after exporting their list to excel to delete the relevant Users. But, how to create such a Dynamic Group (based on what search criteria ?) ?
  
  An alternative would be to create a View (filtered on "sort\_name contains '@'"), then use VBO to add them to a Group : I tried, but could not succeed).
  
  I tried using the Search Builder, but it is broken : I have openned a new post for that : [Search Builder is broken (Civi 5.4.0 / Drupal 7.59)](https://civicrm.stackexchange.com/questions/26511/search-builder-is-broken-civi-5-4-0-drupal-7-59)
  
  **Tags:** contacts, user-account, search-builder
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2018-09-13. Score: 1
  
  You can use Search Builder to make Smart Group for contacts where Sort Name like @ and First Name null
  
  Name or Email LIKE - '%@%' ...AND...
  First Name Is Null ...AND...
  Contact Type = Individual
  
  But I would then use VBO myself so that you can delete any Drupal users that are orphaned (ie no longer have a related civi contact)
  
  We built a View to do this which I can fire through to you if you have trouble creating it from scratch. Ping me in redmine (gratis)
  
  
  ---
  
  ### Answer by username_2 on 2018-09-12. Score: 1
  
  You could use Search > Search Builder and search for Individuals whose First Name and Last Name are NULL (when this is the case, Civi will use the email address as the Display Name), select all of the search results and then create a Smart Group. You would then periodically review the contacts of the smart group by navigating to Contacts > Manage Groups and then clicking on the Contacts hyperlink to the right-hand side of the group and perform the desired actions.
  
  [![enter image description here](https://i.sstatic.net/e7twR.jpg)](https://i.sstatic.net/e7twR.jpg)
  
  > **Comments:**
  > **username_0**: OK, but ... my Search Builder is broken : I don't have any "Select field", but only "Select Record Type" and "Operator" (runnning 5.4.0) : you can reproduce this bug on the demo configuration (https://civicrm.demo.civihosting.com, in 5.0.0), this way : Search Builder > Select "Individual", then go to Support > About CiviCRM, and then back : "Select Field" has disappeared : I have the same situation, but directly, without this forth and back operation. I will edit my Question to be more precise.  
  > **username_3**: @Jacques-MarieMoranne You should open a separate question about why your Search Builder is broken.  
  > **username_0**: Jon : OK, done : https://civicrm.stackexchange.com/questions/26511/search-builder-is-broken-civi-5-4-0-drupal-7-59  
  > **username_0**: Any other solution than Search Builder ?  
  > **username_2**: The only other way to search on NULL values other than Search Builder would be to use the API explorer which might be a bit more complex if you do not have a good understanding of the Civi DB structure. You will find documentation HERE: https://docs.civicrm.org/dev/en/latest/api/  
  > **username_0**: Tamar : yes, I feared that. I have never used API Explorer (I need to try), but, of course, I also thought, via PHPMyAdmin/SQL, directly list, export, and delete the pertainig lines in the database (civicrm_contact table), then delete the pertainig users (via Drupal People Delete menu). But I am not very fond of that solution : only in desperation.  
  
  ---
  
  