LDAP Select Statements

Select Statements Basics:

The SQLLDAP SELECT statement queries data from 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 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	To find all the 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	To find all the records created yesterday in ou=MemberGroupB,o=stooges container
SELECT FROM ou=MemberGroupB,o=stooges where modifyTimestamp <= CurrentEndofDays and modifyTimestamp >=CurrentStartofDay	To find all the records modified today in ou=MemberGroupB,o=stooges container
SELECT FROM ou=MemberGroupB,o=stooges where modifyTimestamp <= CurrentEndofDay -1 days and modifyTimestamp >=CurrentStartofDay -1 days	To find all the records modified yesterday in ou=MemberGroupB,o=stooges container
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 days	To find all the records created in the last 10 days in ou=MemberGroupB,o=stooges container
SELECT FROM ou=MemberGroupA,o=stooges where createTimestamp != '20090122164948Z'	To find all records not created on 20090122164948Z in ou=MemberGroupA,o=stooges container
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	To find all the records created in the last 10 days
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -1 months	To find all the records created in the last 1 month
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 years	To find all the records created in the last 10 years
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -1 hours	To find all the records created in the last hour
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 minutes	To find all the records created in the last 10 minutes
SELECT FROM ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -30 seconds	To find all the records created in the last 30 seconds

SQL Statement examples (Count) :

Query	Results
Select count (*) from o=stooges subtreescope	To find a count of all entries in container o=stooges
Select count (*) from o=stooges subtreescope where createTimestamp >= CurrentTimestamp -2 months	To find 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'	To find 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	To find 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	To find 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') subtreescope	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') subtreescope	Example of adding multiple custom controls.