Post reply

Iterate though Sub OU of main OU

  • February 17, 2016 at 8:30 am

    #399737

    Does anyone know a way in sql to iterate trough a whole Active Directory OU Structure. I am trying to get all the computers in the computer OU but it has many sub-OU. Originally they wrote in sql but I noticed that it does not grab all the computers in the OU. The existing query is below.

    BEGIN

    --Create a temp table to return the AD query

    Create table #TempAD (cn varchar(max), info varchar(max), os varchar(max))

    Insert #TempAD (cn, info, os)

    Select cn, info, operatingSystem from OPENQUERY(ADSI, 'Select cn, info, operatingSystem

    from ''LDAP://????????/OU=Computers,OU=??,OU=??,DC=??,DC=??,DC=???,DC=???''')

    where INFO is not null

    Insert #Eol (RecId, SN, Model, CName, OS, Uname)

    SELECT dr.intRecId

    ,SUBSTRING(info,CHARINDEX('SN=',info,1)+ 3 ,CHARINDEX(';',info,CHARINDEX('SN=',info,1))-CHARINDEX('SN=',info,1)-3) AS SN

    ,SUBSTRING(info,CHARINDEX('Sys=',info,1)+ 4 ,CHARINDEX(';',info,CHARINDEX('Sys=',info,1))-CHARINDEX('Sys=',info,1)-4) AS SY

    ,cn

    ,os

    ,Case when dr.intrecId IS NULL then 'NExist' Else mn.strFullname + ' ' + mn.strRank ENd

    FROM#TempAD ad

    LEFT JOIN Drat_Received dr on dr.strSN = SUBSTRING(info,CHARINDEX('SN=',info,1)+ 3 ,CHARINDEX(';',info,CHARINDEX('SN=',info,1))-CHARINDEX('SN=',info,1)-3)

    LEFT JOIN Drat_Issued i on i.intRecId = dr.intRecId

    LEFT JOIN tblPersonnel mn on mn.intPersonnelId = i.intIssuedTo

    whereinfo like @Search and dr.intRecId not in (select intRecId from drat_EOL)

    Drop table #TempAD

    END

  • February 17, 2016 at 9:32 am

    #1859298

    It's been awhile since I wrote an LDAP query...

    If you can post some sample data for what of #tempAD I'm sure I can help you.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply