Direct OU in SCCM


SCCM stores AD organizational unit in the DB exposing it to v_RA_System_SystemOUName view. The problem is, that for each objects you’ll find direct and all parent OUs of the computer. I’ve created a view, that shows direct OU only. Here it is, just change SMS_ENT to your SCCM DB name.


 


CREATE VIEW [dbo].[DirectOU]


AS


SELECT     A.ResourceID, A.System_OU_Name0


FROM        SMS_ENT.dbo.v_RA_System_SystemOUName AS A INNER JOIN


                         (SELECT    ResourceID, MAX(LEN(System_OU_Name0)) AS len


                           FROM         SMS_ENT.dbo.v_RA_System_SystemOUName


                           GROUP BY ResourceID) AS B ON A.ResourceID= B.ResourceID AND LEN(A.System_OU_Name0) = B.len


 


GO


—————-


OR


 


SELECT ResourceID, MAX(System_OU_Name0)


                 FROM SMS_AAA.dbo.v_RA_System_SystemOUName


GROUP BY ResourceID


  



Comments (1)

  1. alexsemi says:

    SELECT ResourceID, MAX(System_OU_Name0)

                     FROM SMS_AAA.dbo.v_RA_System_SystemOUName

    GROUP BY ResourceID