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.
WITH cteEmployees (DistinguishedName, EmpLevel) AS (
/* Top Block - Find Anchor Point (Top Manager) */
, 1 as EmpLevel
where v_R_user.Distinguished_Name0 = 'CN=Christopher Kibble,OU=Information Technology,OU=Paris,OU=France,DC=Europe,DC=ChristopherKibble,DC=com'
/* Recursive Block */
, cteEmployees.EmpLevel+1 as EmpLevel
from v_R_user emp
on emp.manager0 = cteEmployees.DistinguishedName
/* Select from Collected Data joining back to the v_R_user View */
on cteEmployees.DistinguishedName = v_R_user.Distinguished_Name0
Have a suggestion for an improvement? Please let me know in the comments!