Created
December 9, 2016 10:48
-
-
Save xrisdoc/25ed82706c94bd4dd5d61e459ec27e1c to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment