GetActiveDirectoryUsers.sql

SELECT CONVERT(int, objectGUID) AS UserID, LOWER(LEFT(userPrincipalName, LEN(userPrincipalName) - LEN('@my-domain.co.uk'))) AS Username, givenName AS FirstName, sn AS Surname, LOWER(mail) AS Email, 
	   CONVERT(nvarchar(20), telephoneNumber) AS DirectDial, CONVERT(nvarchar(20), pager) AS ExtensionNumber, CONVERT(nvarchar(20), mobile) AS MobileNumber, Department, title AS JobTitle, 
	   REPLACE(LOWER(givenName) + '-' + LOWER(sn) + '.jpg', ' ', '-') AS ProfileImageCSS,
	   LOWER(userPrincipalName) AS UsernameFull, userAccountControl AS AccountStatus, distinguishedName AS ADTreeRoute
FROM OPENQUERY(ADSI, 
     'SELECT objectSID, objectGUID, userPrincipalName, sAMAccountName, givenName, sn, mail, telephoneNumber, pager, mobile, physicalDeliveryOfficeName, Department, title, userAccountControl, distinguishedName
	   FROM ''LDAP://my-server/DC=my-domain,DC=co,DC=uk''
	   WHERE objectCategory = ''person'' AND objectClass = ''user''
	   ORDER BY SN ASC') AS ad
WHERE RIGHT(distinguishedName, LEN('OU=SBSUsers,OU=Users,OU=MyBusiness,DC=my-domain,DC=co,DC=uk')) = 'OU=SBSUsers,OU=Users,OU=MyBusiness,DC=my-domain,DC=co,DC=uk'