Active Directory/LDAP Select Statements

Home | Tutorials | Compare

 

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
subtreescope

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
subtreescope

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



See Also:


How to run Select Statements