Kategorie: 
Translate EN
My script-based query tool “Carmen” for Active Directory has been downloaded more than 10,000 times since its release. Now I added an English version for an international audience.
Carmen lets you query Active Directory using SQL-style syntax. This makes it easier for most admins to get data from AD as SQL is more common to use than LDAP. Since its very beginning, Active Directory has supported SQL queries – as only few people seem to know.
Carmen is an HTA application written in HTML and VBSript. It is “safe” for your environment as it only reads data but cannot write to AD. Simply launch the tool by double-click and fill in the three data fields SELECT, FROM, and WHERE. (In most cases you can leave the FROM clause as it is since it already contains your domain name.)
Carmen SQL Queries for AD English Version (4,1 KiB, 3.170-mal heruntergeladen, letzte Änderung am 30. Mai 2011)
Here are some sample queries:
| All users | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE objectClass=’user‘ and objectCategory=’person‘ |
| Alle users from Hannover whose phone numbers contain "123", sorted by name in reverse order | SELECT name, telephoneNumber FROM ‚LDAP://DC=contoso,DC=com‘ WHERE objectClass=’user‘ and objectCategory=’person‘ and l=’Hannover‘ and telephoneNumber=’*123*‘ ORDER BY name DESC |
| Disables users (unless other flags are set!) | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE objectClass=’user‘ and objectCategory=’person‘ and userAccountControl=’5142 |
| All groups | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE objectClass=’group‘ |
| All domain Global Groups | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE groupType=‘-21474836462 |
| All Domain Local Groups | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE groupType=‘-21474836442 |
| All Builti groups | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE groupType=‘-21474836432 |
| All Universal Groups | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE groupType=‘-21474836402 |
| All computers | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE objectClass=’computer‘ |
| All computers with Windows XP and Service Pack 2 | SELECT * FROM ‚LDAP://DC=contoso,DC=com‘ WHERE objectClass=’computer‘ and operatingSystem=’Windows XP*‘ and operatingSystemServicePack=’Service Pack 22 |
| All sites and subnets in Active Directory | SELECT name,objectClass FROM ‚LDAP://CN=sites,CN=configuration,DC=contoso,DC=com‘ WHERE objectClass=’site‘ or objectClass=’subnet‘ |
| The FSMO role owners | SELECT name,fSMORoleOwner FROM ‚LDAP://DC=contoso,DC=com‘ WHERE fSMORoleOwner=’*‘ |
http://faq-o-matic.net/?p=3193
