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!
No comments