Finding the OU of a system in SMS/ConfigMgr

SELECT     dbo.v_R_System.Name0 AS [Computer Name], A.System_OU_Name0
FROM         dbo.v_RA_System_SystemOUName AS A INNER JOIN
                          (SELECT     ResourceID, MAX(LEN(System_OU_Name0)) AS len
                            FROM          dbo.v_RA_System_SystemOUName
                            GROUP BY ResourceID) AS B ON A.ResourceID = B.ResourceID AND LEN(A.System_OU_Name0) = B.len INNER JOIN
                      dbo.v_R_System ON B.ResourceID = dbo.v_R_System.ResourceID

 

 

 

 

If you want just for one computer then…

 

SELECT     dbo.v_R_System.Name0 AS [Computer Name], A.System_OU_Name0
FROM         dbo.v_RA_System_SystemOUName AS A INNER JOIN
                          (SELECT     ResourceID, MAX(LEN(System_OU_Name0)) AS len
                            FROM          dbo.v_RA_System_SystemOUName
                            GROUP BY ResourceID) AS B ON A.ResourceID = B.ResourceID AND LEN(A.System_OU_Name0) = B.len INNER JOIN
                      dbo.v_R_System ON B.ResourceID = dbo.v_R_System.ResourceID
WHERE     (dbo.v_R_System.Name0 = 'Computer Name')