Select Statements Basics:
The SQLLDAP SELECT statement queries data from Active Directory/LDAP. The statement begins with the SELECT keyword. The basic SELECT statement has 4 clauses:SELECT
FROM
WHERE
Query Scope
The SELECT clause specifies the attributes that are retrieved. The FROM clause specifies the entries accessed. The WHERE clause specifies the condition. The WHERE clause is optional; if missing, all the entries within the container will be returned depending upon the query scope. The QUERY SCOPE is new for ldap query, if missing the default is subtree scope and will return all the subentries (you can change the default from the radio buttons at the bottom of sql editor)
To select all the entries within an entry (including entry and all its subentries) you type sql statement as:
Select * from DC=LDAPSOFT,DC=COM subtreescope
To select only the immediate children you type sql statement as:
Select * from DC=LDAPSOFT,DC=COM onelevelscope
To select the entry only you type sql statement as:
Select * from DC=LDAPSOFT,DC=COM basescope
SQL Statement examples:
Query |
Results |
SELECT FROM OU=Chicago,OU=US,DC=LDAPSoft where badPasswordTime >= CurrentTimestamp - 8 days |
Finds all entries entered a bad password in the last 8 days |
SELECT FROM OU=Chicago,OU=US,DC=LDAPSoft where createTimeStamp >= CurrentTimestamp - 8 days |
Finds all entries created in the last 8 days |
SELECT FROM OU=Chicago,OU=US,DC=LDAPSoft where objectCategory='Person' and objectClass='User' and lockoutTime<= CurrentTimestamp - 8 days and lockoutTime!=0 subtreescope |
Returns all active directory disabled users |
select * from DC=LDAPSoft where objectCategory='Person' and objectClass='user' and userAccountControl:1.2.840.113556.1.4.803:=2 |
Return all active directory user entries with lockout time less then equal to 8 days |
select * from DC=LDAPSoft where objectCategory='Person' and objectClass='user' and userAccountControl:1.2.840.113556.1.4.803:!=2 |
Returns all active directory users not disabled |
SELECT cn, sAMAccountName, distinguishedName from OU=Chicago,OU=US,DC=LDAPSoft WHERE objectClass='person' AND NOT memberOf='*' |
Returns cn, sAMAccountName and distinguishedName of all objects of class "person" in OU=Chicago,OU=US,DC=LDAPSoft that are not members of any group (except their "primary" group) |
SELECT cn, sAMAccountName, distinguishedName from OU=Chicago,OU=US,DC=LDAPSoft WHERE objectClass='person' AND objectClass='user' AND (accountExpires=0 OR accountExpires=9223372036854775807) |
Returns cn, sAMAccountName and distinguishedName of all users objects that do not expire in OU=Chicago,OU=US,DC=LDAPSoft |
SELECT sAMAccountName, distinguishedName from OU=Chicago,OU=US,DC=LDAPSoft WHERE objectCategory='person' AND objectClass='user' AND userAccountControl=546 |
Returns sAMAccountName and distinguishedName of all users objects in OU=Chicago,OU=US,DC=LDAPSoft with a value of 546 assigned to the userAccountControl attribute |
SELECT sAMAccountName, distinguishedName from OU=Chicago,OU=US,DC=LDAPSoft WHERE memberOf = 'CN=SoftwareTeam,OU=Chicago,OU=US,DC=LDAPSoft ' |
Returns sAMAccountName, distinguishedName of all objects in OU=Chicago,OU=US,DC=LDAPSoft that are members of group CN=SoftwareTeam,OU=Chicago,OU=US,DC=LDAPSoft |
SELECT sAMAccountName, distinguishedName, whenCreated from OU=Chicago,OU=US,DC=LDAPSoft WHERE objectCategory='person' AND objectClass='user' AND whenCreated>='20081001000000.0Z' |
Returns sAMAccountName, distinguishedName and whenCreated for all users in OU=Chicago,OU=US,DC=LDAPSoft created after October 1, 2008 |
SELECT * fromOU=Chicago,OU=US,DC=LDAPSoft WHERE objectCategory='person' AND objectClass='user' AND description IS NULL |
Returns all attributes of users in OU=Chicago,OU=US,DC=LDAPSoft that have no value assigned to the description attribute |
SQL Statement examples:
Query |
Results |
Select * from DC=LDAPSOFT,DC=COM subtreescope |
All entries within DC=LDAPSOFT,DC =COM are returned including DC=LDAPSOFT,DC=COM and all its subentries |
Select * from DC=LDAPSOFT,DC=COM WHERE createTimeStamp='20080307210341.904000Z' |
All entries within DC=LDAPSOFT,DC=COM where createtimestamp is 20080307210341.904000Z |
Select * from DC=LDAPSOFT,DC=COM WHERE dc ='ldapso ft,dc=com' subtreescope |
All entries within DC=LDAPSOFT,DC=COM where dc ='ldapsoft,dc=com' |
Select * from DC=LDAPSOFT,DC=COM WHERE cn='1testGroup' subtreescope |
All entries within DC=LDAPSOFT,DC=COM where cn='1testGroup' |
SELECT * FROM cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM WHERE objectclass like 'AIX%' subtreescope |
All entries within DC=LDAPSOFT,DC=COM where object class starts with AIX |
SELECT COUNT(*) FROM cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM subtreescope |
Count of all the entries within cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM - If the count exceeds the number of max results it will show you the max results - you can increase the count of the max results from the Max Results textbox. |
SELECT COUNT(*) FROM cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM onelevelscope |
Count of all the immediate children of cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM |
Select * from DC=LDAPSOFT,DC=COM where createTimeStamp >=20071120000000 and createTimeStamp<=20071121000000 and objectclass='container' subtreescope |
All entries within DC=LDAPSOFT,DC=COM created between 20071120000000 and 20071121000000 and objectclass is container |
SELECT * FROM cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM where sn IS NULL subtreescope |
All entries within cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM where sn is null or the sn attribute does not exist |
SELECT * FROM cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM where sn IS NOT NULL subtreescope |
All entries within cn=users,cn=test,cn=testuser,DC=LDAPSOFT,DC=COM where sn is not null |
SQL Statement examples (Time or Date Based) :
Query |
Results |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp <= CurrentEndofDay and createTimestamp >=CurrentStartofDay |
Finds all records created today in ou=MemberGroupB,o=stooges container |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp <= CurrentEndofDay -1 days and createTimestamp >=CurrentStartofDay -1 days |
Finds all records modified today in ou=MemberGroupB,o=stooges container |
SELECT FROM ou=MemberGroupB,o=stooges where modifyTimestamp <= CurrentEndofDays and modifyTimestamp >=CurrentStartofDay |
All entries within DC=LDAPSOFT,DC=COM where dc ='ldapsoft,dc=com' |
SELECT FROM ou=MemberGroupB,o=stooges where modifyTimestamp <= CurrentEndofDay -1 days and modifyTimestamp >=CurrentStartofDay -1 days |
Finds all records modified yesterday in ou=MemberGroupB,o=stooges container |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 days |
Finds all records created in the last 10 days in ou=MemberGroupB,o=stooges container |
SELECT FROM ou=MemberGroupA,o=stooges where createTimestamp != '20090122164948Z' |
Finds all records not created on 20090122164948Z in ou=MemberGroupA,o=stooges container |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 days |
Finds all records created in the last 10 days |
Select * from DC=LDAPSOFT,DC=COM where createTimeStamp >=20071120000000 and createTimeStamp<=20071121000000 and objectclass='container' subtreescope |
All entries within DC=LDAPSOFT,DC=COM created between 20071120000000 and 20071121000000 and objectclass is container |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 days |
Finds all records created in the last 10 days |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -1 months |
Finds all records created in the last 1 month |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 years |
Finds all records created in the last 10 years |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -1 hours |
Finds all records created in the last hour |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 minutes |
Finds all records created in the last 10 minutes |
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -30 seconds |
Finds all records created in the last 30 seconds |
SQL Statement examples (Count) :
Query |
Results |
Select count (*) from o=stooges subtreescope |
Finds a count of all entries in container o=stooges |
Select count (*) from o=stooges subtreescope where createTimestamp >= CurrentTimestamp -2 months |
Finds a count of all the entries created in the container o=stooges in the last 2 months |
SQL Statement Examples (Using Not Operator):
Query |
Results |
Select from ou=MemberGroupA,o=stooges where postalCode= '60660' and mail != 'test@test.com' and sn!='test' |
Finds all LDAP users with postal code = 60660 and mail not equal to 'test@test.com' and sn not equal to 'test' |
SQL Statement examples (Using IS NULL or IS NOT NULL Operator) :
Query |
Results |
SELECT FROM ou=MemberGroupB,o=stooges where (objectClass= 'person' or objectClass= 'organizationalPerson' or objectClass= 'inetOrgPerson') and userPassword IS NULL |
Finds all LDAP users with no password in container ou=MemberGroupB,o=stooges |
SELECT FROM ou=MemberGroupB,o=stooges where (objectClass= 'person' or objectClass= 'organizationalPerson' or objectClass= 'inetOrgPerson') and userPassword IS NOT NULL |
Finds all Ldap Users with password in container ou=MemberGroupB,o=stooges |
SQL Statement examples (Custom Controls) : Custom LDAP controls can be added using ldapcontrols function
Query |
Results |
Select cn,creatorsName,createTimeStamp,modifiersName, modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectClass='user' and isDeleted=TRUE add ldapcontrols('1.2.840.113556.1.4.417') |
To find all Deleted Active Directory Users. To retrieve all deleted users custom control 1.2.840.113556.1.4.417 is added to the sql statement |
Select cn,creatorsName,createTimeStamp,modifiersName, modifyTimeStamp,sAMAccountName,userAccountControl from RootDSE where objectClass='user' and isDeleted=TRUE add ldapcontrols('1.2.840.113556.1.4.417', '1.2.840.113556.1.4.419') |
Example of adding multiple custom controls. |
See Also: