Select Statement Examples

 

Query

Results of Query

 

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

 

Active Directory Statement Examples

 

Query

Search For

 SELECT FROM OU=Chicago,OU=US,DC=LDAPSoft where badPasswordTime >= CurrentTimestamp - 8 days
subtreescope

To find out all the entries entered a bad password in the last 8 days

SELECT FROM OU=Chicago,OU=US,DC=LDAPSoft where createTimeStamp >= CurrentTimestamp - 8 days
subtreescope

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

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

To search for 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

To search for all active directory users not disabled

 

More Select Statement Examples (Time or Date Based)

 

Query

Search For

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

 

 

More Select Statement Examples (Count)

 

Query

Search For

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

 

More Select Statement Examples

 

Query

Search For

SELECT FROM ou=MemberGroupB,o=stooges where (objectClass= 'person' or objectClass= 'organizationalPerson' or objectClass= 'inetOrgPerson') and userPassword= '{SSHA}TExBUR9x6UOxAdHwgCHI8veC7Yfe8sTLBpw+Mw=='

To find all LDAP users with pasword {SSHA}TExBUR9x6UOxAdHwgCHI8veC7Yfe8sTLBpw+Mw==

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

 

More Select Statement Examples (Using Not Operator)

 

Query

Search For

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'

 

Related Links:

Select statement

Update statement

Insert statement

Delete statement examples

 

Ldapsoft Home page LDAP Admin Tool Home Page