Skip to content

SQL Query Active Directory using a CTE

March 26, 2012

My company uses object class for its pager numbers so I created this CTE the combines both datasets.

First create the linked server.

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

..then query the data.

WITH    a AS ( SELECT   givenName AS [First Name], sn AS [Last Name], sAMAccountName AS Username,
                        displayName AS [Display Name], mail AS Email, telephoneNumber AS Phone, mobile,
                        physicalDeliveryOfficeName AS Location, department
               FROM     OPENQUERY(ADSI, 'SELECT  givenName,  
												sn,  
												sAMAccountName,  
												displayName,  
												mail,  
												telephoneNumber,  
												mobile,  
												physicalDeliveryOfficeName,  
												department 
										   FROM ''LDAP://DC=[Your Domain],DC=COM''
										   WHERE objectClass=''Person''
										   AND objectClass = ''User''')
               WHERE    ( givenName IS NOT NULL )),
        b AS ( SELECT   givenName AS [First Name], sn AS [Last Name], mail AS Pager
               FROM     OPENQUERY(ADSI, 'SELECT  givenName,  
												sn,  
												sAMAccountName,  
												displayName,  
												mail,  
												telephoneNumber,  
												mobile,  
												physicalDeliveryOfficeName,  
												department
										   FROM ''LDAP://DC=[Your Domain],DC=COM''
										   WHERE objectClass=''Person''
										   AND (objectClass = ''Contact'')')
               WHERE    ( givenName IS NOT NULL ))
    SELECT  a_1.Username, a_1.[First Name], a_1.[Last Name], a_1.[Display Name], a_1.Email, b_1.Pager, a_1.Phone,
            a_1.Location
    FROM    a AS a_1
            LEFT OUTER JOIN b AS b_1 ON a_1.[First Name] = b_1.[First Name]
                                        AND a_1.[Last Name] = b_1.[Last Name]
    ORDER BY a_1.Username
Advertisements

From → CodeProject, SQL

One Comment
  1. Very interesting information!Perfect just what I was searching for!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: