• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

Create query in Active Directory Users Computers for a report

i need to do a custom search/LDAP query in active directory (through active directory users and computers) and i need it to reveal all email addresses in the domain, but WITHOUT users that have the word "experts" in the Company: field on the Organization tab for users.

i need to put this in a list to email out to personnel.

anyone know how?
0
JodyBear
Asked:
JodyBear
  • 6
  • 2
  • 2
  • +1
1 Solution
 
Mike KlineCommented:
So you can use something like this

(&(objectcategory=person)(objectclass=user)(!company=*experts*))

If you want to see those that do have experts you just remove the not/! symbol

See my screenshots

Outputting would be easier using a tool like adfind, powershell, ds tools.

Thanks

Mike
without-experts-in-company-field.png
with-experts-in-company-field.png
0
 
JodyBearAuthor Commented:
thanks - it looks good, but can you add what would be needed to see every user in the domain that has an email address, and display that address in the query.  thanks again.
0
 
JodyBearAuthor Commented:
also if you would describe how i can port that out to a report to email to one of my users
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Chris DentPowerShell DeveloperCommented:
In addition to the terms above?

If so:

(&(objectCategory=person)(objectClass=user)(!company=*experts*)(mail=*))

I believe you can add the E-mail to the column list in the output above (View / Choose Columns). If not, you can always use dsquery:

dsquery * -filter (&(objectCategory=person)(objectClass=user)(!company=*experts*)(mail=*)) -attr mail

"Should" work.

Chris
0
 
Mike KlineCommented:
Thanks Chris,  your hunch was right (as usual).  you can add e-mail to the column list

Thanks

Mike
choose-columns.png
0
 
pukyplayCommented:
hello,
i would highly recommend you to try adinfo tool

http://www.cjwdev.co.uk/Software/ADReportingTool/Info.html

it is free to use with some limitations for non-corporate use,or you can buy it and support new builds.
It has also export option to csv,so you can import your results to exel, Helped me a lot,realy good
0
 
JodyBearAuthor Commented:
i almost have it - thank you for all the input.  i couldn't get ADUC to work (i'm sure it was my fault), so i ended up using dsquery.  the last bit of help needed is using the query below but limiting the output to only users with     regency    or    miramar   in the office field of the user's attributes.  last bit of help anyone?

dsquery * -limit 10000 -filter "(&(objectClass=user)(objectClass=person)(!company=*experts*)(mail=*@domain.com))" -attr mail > c:\test7.txt
0
 
Chris DentPowerShell DeveloperCommented:
Like this:

dsquery * -limit 10000 -filter "(&(objectClass=user)(objectClass=person)(!company=*experts*)(mail=*@domain.com)(|(physicalDeliveryOfficeName=regency)(physicalDeliveryOfficeName=miramar)))" -attr mail > c:\test7.txt

I'm assuming you mean exact values, if you want wildcards, if it's to match a bit of the field, then add in * as appropriate.

HTH

Chris
0
 
JodyBearAuthor Commented:
it worked prior to that, but with your part included, i'm now getting a text file with 0 bytes.  i cut and pasted it, so i know the syntax is as you asked.  here is an example of my "office" field that i want to pin down what will only be in my query so you know what i'm workin with:

Miramar/Florida/US
     or
Regency/Florida/US
     or
PAFB/Florida/United States/North America

an example of what i jsut tried:

C:\Documents and Settings\jodavis>dsquery * -limit 10000 -filter "(&(objectClass
=user)(objectClass=person)(!company=*experts*)(mail=*@domain.com)(|(physicalDeli
veryOfficeName=*florida*)))" -attr mail > c:\test7.txt

i tried it with *miramar*, miramar*, *miramar and the same with regency, but each time they gave me 0 bytes in the file.  tried it with Florida as well, same thing.  no errors are reported after execution.

help :-)

0
 
JodyBearAuthor Commented:
MY MISTAKE :-)  i forgot to update the domain name with my actual domain.  i'll get back with you in a moment.
0
 
JodyBearAuthor Commented:
as soon as i replaced my domain name (hiding for privacy and all), i got the results i was looking for.  there were many ideas here that helped my result, but the defining answer was from Chris-Dent.  thank you all.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now