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 ou=MemberGroupB,o=stooges where createTimestamp >= CurrentTimestamp -10 days

To find all the 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

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) : 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.



 

 

 

Related Links:

Select statement

Update statement

Insert statement

Delete statement examples

 


Ldapsoft Home page LDAP Admin Tool Home Page