How to do a recursive function with KQL

Question

I have table in Sentinel for all employees.

Each lines has an name, employee ID and a direct supervisor ID.

I want to be able to give the supervisor ID, and from there, have a recursive loop that will verify all employee who has that supervisor as a direct supervisor, and so on.

Basically: I want to know everyone under Alice (ID=1).

Bob (2), Carl (3) and Dave (4) have Alice has direct supervisor. This is easy to retrieve (where DirectorSupervisor == 1).

Under Bob (2), I have Eric (5), Frank (6), etc.

I want to be able to retrieve everyone, directly or indirectly under Alice. Many branches.

Any clue on how to achieve this?

Answer 1

Hi @GuyP Dubois , unfortunately Kusto does not support recursion. You'd have to do it by iteratively expanding the hierarchy using a series of union operations or by leveraging a materialized view if you are able to preprocess the data.

For a high-level overview:

1. Start by defining the initial set of employees who directly report to the given supervisor.
2. Iteratively find employees who report to the employees found in the previous step.
3. Continue this until no more employees are found.

For example:

let SupervisorID = 1;
let Level1 = Employees 
             | where DirectSupervisorID == SupervisorID;
let Level2 = Employees 
             | where DirectSupervisorID in (Level1 | project EmployeeID);
let Level3 = Employees 
             | where DirectSupervisorID in (Level2 | project EmployeeID);
let Level4 = Employees 
             | where DirectSupervisorID in (Level3 | project EmployeeID);
// Add more levels as needed, up to a reasonable maximum depth.
union Level1, Level2, Level3, Level4
| distinct Name, EmployeeID, DirectSupervisorID

Level1 retrieves employees directly supervised by Alice (SupervisorID = 1). Level2 retrieves employees supervised by anyone in Level1, and so on. You can add more levels if necessary, though this approach may not scale well for very deep or very wide hierarchies.

For a more dynamic approach, you might consider using a loop or a stored procedure in a more traditional programming environment, preprocess the data, and then load it into Sentinel.

Please let me know if you have any questions and I can help you further.

If this answer helps you please mark "Accept Answer" so other users can reference it.

Thank you,

James

Answer 2

If you have Entra/AAD as your Directory you may already have this staff to manager relationship, if you turned on UEBA in Sentinel?

IdentityInfo
| summarize peopleCount = count_distinct(AccountName) by Manager