AD Users and Groups

[siil-itman]

I have a monster spreadsheet that I export various info from our AD, anti-virus, 365 and other systems. The spreadsheet acts as a central dashboard and is used by our finance department for billing purposes across our organization as well as my department for system and user managment. Populating the spreadsheet takes me several days and is a pain in the ass. 

With help from these forums, I've managed to get dataflows setup for User, PC and AV info from our on-premise systems + 365 licensing info. I've then been able to build suitable reports off the dataflows and have everything refresh a couple of times a day automatically.

Within our infrastructure, we have a number of services which are tied in to AD groups, getting added to the appropriate group will give you access to that service using your AD login. I want to now setup a dataflow for the AD group information so I can build a report to display the relevant groups + number of members per group and members by company/department.

In the intial setup of the dataflow, I'm selecting Active Directory as the source and it's showing me our forest. I select a domain and it gives me the catagory list and objects but will only let me select 1 object

If I go into the "group" table and expand "group" & "top", if I include the "group.member" field, as well as attributes from the "top", it shows "this step will be evaluated outside of the data source" and when I click save and close, it removes the "group.member" field. I've also tried going in via the "user" table and expanding "top.memberOf" with the same result!

Can anyone advise how I can (if possible) setup a dataflow (or dataflows) that will allow me to get both the user and group data into 1 report in such a way that I can tie the two together?

[siil-itman]

 @lbendlin I don't have an issue with the speed of the connectors. We don't need realtime info, just something that auto refreshes a couple of times a day.

My problem is linking the info from the group table and user table in the top level catagories so I can show which users are in which groups.

I'm trying to move away from using scripts and csv files which is what I'm currently doing!

[lbendlin]

AD connectors in Power BI are notoriously slow, especially when your AD has more than a couple hundred objects  (ours has hundreds of thousands, for example)

In most cases powershell scripts are preferred. They can create CSV files that your dataflows can ingest easily and quickly.