CODEWITHSUNDEEP
sql-serveractive-directory
marianolp
marianolp

Reputation: 394

SQL script - get OU's ObjectGuid from AD user's attributes

I want to join organization unit guid (filtering objectClass = ''organizationalunit'') to user's organization (filtering objectClass = ''User'')... I do not see a matching code (an OU guid in Users'Attributes from AD) in order to match the dataset where I have all OUs objectClass = ''organizationalunit'')

-- dataset to get all OUs --I have an OU ObjectGUId here! it looks ok.
select *
FROM            OPENQUERY([ADSI], 
                         'SELECT 
                        Name, ADsPath,objectguid , parentguid       
    FROM ''myLDAP path'' 

    WHERE 
            objectClass = ''organizationalunit''
        
            ')
                          AS t

-- dataset to get users attributes--I do not see an OU ObjectGUId related to users OU structure
-- I checked here but no luck http://www.kouti.com/tables/userattributes.htm
select *
FROM            OPENQUERY([ADSI], 
                         'SELECT 
            userPrincipalName,ADsPath,
            Name, 
            displayName,
            objectCategory,
            SN, 
            givenName,
            ST, 
            SamAccountName, 
            DistinguishedName,
            ObjectGUID, 
            physicalDeliveryOfficeName,
            department,
            ObjectSID, 
            mail    ,preferredOU    , ou ,o
    FROM ''myLDAP path'' 

    WHERE 
            objectClass = ''User''
        
            ')

Upvotes: 1

Views: 949

Answers (1)

Lars Karlslund
Lars Karlslund

Reputation: 60

If I understand your question correctly you want to join a list of users with the organizational unit that they reside in.

Active Directory is a tree structure, and the distinguishedName is a path that describes where the object is.

So to move from an object to the container that object resides in, you remove the first element of the distinguishedName, which is separated by commas.

So the user with distinguishedName cn=user,ou=container,dc=domain,dc=local resides in the organizational unit ou=container,dc=domain,dc=local

So the solution is something more or less like SELECT RIGHT(distinguishedName, CHARINDEX(",", distingishedName) + 1) from the users, which you then join to the organizationalUnits distinguishedName.

Objects are not linked via objectGUIDs the way you suggest.

I'm sorry I can't offer you direct working SQL code, you're probably better at that than I am ;-)

Upvotes: 1

Related Questions