Reporting on Active Directory data. Not a question about AD loads or security.

[#1]

This question doesn't have anything to do with loading AD data or securing reports by AD.

I already have data from AD in a relational database and I'm trying to figure out the best way to create a view there or do something in QV script to allow me to provide clients with ability to see what AD users are members of AD groups. If it were just a matter of selecting a GROUP and showing the PERSON members of it that's no big deal. The problem happens with those groups that have other groups as members of them.

Some examples may help illustrate the problem. Below is the ER diagram of the database followed by some sample data.

As you can see, if I wanted just to show all the members of group VPs it's easy. However, if I want to show all the members of groups All Employees or Executives, it becomes much  more complicated.

Has anyone ever had to do something like this and how did you solve it?

I would prefer to do the work in the database instead of QV script because I think it would be faster, but I'm not having any luck so far. At this point I don't care where I do it as long as I can get the right answer

Thanks in advance for any help.

David

[MarcoWedel]

Hi,

you basically have to use the hierarchy prefix in your load of the ADGroups_SubGroups table.

This will translate the child/parent relations described there into a complete hierarchy.

Maybe this thread helps:

Using lookup

regards

Marco

[#3]

Thanks Marco.

I played around with that and I can see how it's a really useful feature that I'll probably use in future applications. However, it doesn't really help me with my current use case. I see that the hierarchy results in a table that has multiple new columns showing the children of a specific node.

In order to provide the functionality my clients want, I need to end up with a table that looks like this