Articles in this section

Examples for querying Microsoft Active Directory

Anitha Abraham
Updated

The following examples will help you to get started and query the Microsoft Active Directory.

Table 1 – Group

Group – Example queries

SELECT

If you want to

Retrieve group records.

You can use operators =, >= , <=, !=, LIKE, AND, and OR in the SELECT statement.

Example

 
SELECT * FROM Group WHERE  GroupType != '-2147483644' AND  ObjectClass = 'top;group' LIMIT 5

INSERT

If you want to

Add a group record.

  • Required fields: Provide RDN and ObjectClass.

  • All fields can be specified except Id, DN, and BaseDN.

Example

 
INSERT INTO Group (RDN, ObjectClass) VALUES ('CN=Domain Admins', 'group')

UPDATE

If you want to

Update columns in a group record.

You can update all columns except Id, DN, and BaseDN by providing the Id in the WHERE clause.

Example

 
UPDATE Group SET Member = 'CN=SUPPORT_388945a0,CN=Users,DC=MyDC' WHERE Id = '1|CN=HelpServicesGroup,CN=Users,DC=MyDC'

DELETE

If you want to

Delete group records.

Provide the Id of the Group in a DELETE statement.

Example

 
DELETE FROM Group WHERE Id = '1|CN=HelpServicesGroup,CN=Users,DC=MyDC'

Table 2 – User

User – Example queries

SELECT

if you want to

Retrieve user records.

You can use operators =, >= , <=, !=, LIKE, AND, and OR in the SELECT statement.

Example

 
SELECT * FROM User WHERE Title LIKE '%abc%' AND AdminCount != '1' LIMIT 5

INSERT

If you want to

Add a user record.

  • Required fields: Provide RDN and ObjectClass.

  • All fields can be specified except Id, DN, and BaseDN.

Example

 
INSERT INTO [User] (RDN, ObjectClass) VALUES ('CN=TestUser', 'Top; Person; OrganizationalPerson; User')

UPDATE

If you want to

Update columns in a user record.

You can update all columns except Id, DN, and BaseDN by providing the Id in the WHERE clause.

Example

 
UPDATE User SET PostalCode = '94042' WHERE Id = '1|CN=NewUser,CN=Users,DC=MyDC'

DELETE

If you want to

Delete user records.

Provide the Id of the user in a DELETE statement.

Example

 
DELETE FROM User WHERE Id = '1|CN=NewUser,CN=Users,DC=MyDC'

Table 3 – Top

Top – Example queries

SELECT

If you want to

Retrieve top records.

You can use operators =, >= , <=, !=, LIKE, AND, and OR in the SELECT statement.

Example

 
SELECT * FROM Top WHERE  CN != 'NewUser' AND  BaseDN = 'CN=Users,DC=MyDC' LIMIT 5

INSERT

If you want to

Add a top record.

  • Required fields: Provide RDN and ObjectClass.

  • All fields can be specified except Id, DN, and BaseDN.

Example

 
INSERT INTO Top (RDN, ObjectClass) VALUES ('CN=NewUser', 'top;person;organizationalPerson;user;inetOrgPerson')

UPDATE

If you want to

Update columns in a top record.

You can update all columns except Id, DN, and BaseDN by providing the Id in the WHERE clause.

Example

 
UPDATE Top SET Description = 'test' WHERE Id = '1|CN=NewUser,CN=Users,DC=MyDC'

DELETE

If you want to

Delete top records.

Provide the Id of the Top record in a DELETE statement.

Example

 
DELETE FROM Top WHERE Id = '1|CN=NewUser,CN=Users,DC=MyDC'

Table 4 – Person

Person – Example queries

SELECT

If you want to

Retrieve person records.

You can use operators =, >= , <=, !=, LIKE, AND, and OR in the SELECT statement.

Example

 
SELECT * FROM Person WHERE  ObjectClass = 'top' AND  CN  LIKE '%NewUser%' LIMIT 5

INSERT

If you want to

Add a person record.

  • Required fields: Provide RDN and ObjectClass..

  • All fields can be specified except Id, DN, and BaseDN.

Example

 
INSERT INTO Person (RDN, ObjectClass) VALUES ('CN=Domain Admins', 'Person')

UPDATE

If you want to

Update columns in a person record.

You can update all columns except Id, DN, and BaseDN by providing the Id in the WHERE clause.

Example

 
UPDATE Person SET Description = 'desc' WHERE Id = '1|CN=NewUser,CN=Users,DC=MyDC'

DELETE

If you want to

Delete person records.

Provide the Id of the Person in a DELETE statement.

Example

 
DELETE FROM Person WHERE Id = '1|CN=NewUser,CN=Users,DC=MyDC'