Recursively Query User View in Configuration Manager
The following query will allow you to search the view
v_R_user in the Microsoft Configuration Manager database so that you can identify a manager and then find everyone who works for that manager all the way down the tree. I've found this useful in the past for joining with other information in the database to identify all the systems used by a specific group within an organization. This requires that you collect the manager name during user discovery. You can remove any fields that you don't discover. It also requires that you have managers identified in Active Directory for discovery.
1WITH cteEmployees (DistinguishedName, EmpLevel) AS ( 2 3 /* Top Block - Find Anchor Point (Top Manager) */ 4 select v_R_user.Distinguished_Name0 5 , 1 as EmpLevel 6 from v_R_user 7 where v_R_user.Distinguished_Name0 = 'CN=Christopher Kibble,OU=Information Technology,OU=Paris,OU=France,DC=Europe,DC=ChristopherKibble,DC=com' 8 9 UNION ALL 10 11 /* Recursive Block */ 12 select emp.Distinguished_Name0 13 , cteEmployees.EmpLevel+1 as EmpLevel 14 from v_R_user emp 15 join cteEmployees 16 on emp.manager0 = cteEmployees.DistinguishedName 17) 18 19/* Select from Collected Data joining back to the v_R_user View */ 20select v_R_user.cn0 21 , v_R_user.co0 22 , v_R_user.l0 23 , v_R_user.physicalDeliveryOfficeNam0 24 , v_R_user.company0 25 , v_R_user.department0 26 , v_R_user.title0 27 , v_R_user.user_name0 28 , v_R_user.User_Principal_Name0 29 from cteEmployees 30 join v_R_user 31 on cteEmployees.DistinguishedName = v_R_user.Distinguished_Name0
Have a suggestion for an improvement? Please let me know in the comments!