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

• Anonymous
  August 18, 2009
  SELECT ResourceID, MAX(System_OU_Name0)                 FROM SMS_AAA.dbo.v_RA_System_SystemOUNameGROUP BY ResourceID