Hi!
Before I get to my problem, let me explain the purpose.
My ultimate goal is tocompile a list of Azure Active Directory members and include this list in the Power Bi report.
I have an Azure Active Directory group which contains also nested groups.
There are currently around 300 users in that group, but it will grow over time.
I have a Poweshell script, with which I can create a csv file with all this information. However, I need an automated solution, that will check AAD every day, create a list and save it in a location, to which the Power BI report can connect to. I want to avoid saving files locally etc. so I decided to give up the powershell script and look for another way to retrieve the member list.
I was thinking about using Power Automate, but I have to admit that I have no knowledge about it and I am not sure if it is suitable for what I want to achieve.
I tried this solution from @Expiscornovus
https://powerusers.microsoft.com/t5/Using-Flows/PowerAutomate-Loop-through-an-AAD-Security-Group-to-find-all/m-p/1743578
and it seem to work fine, but I have two problems adapting it to my case.
For each user I need information about AAD groups. So I have my main AAD group to which I connect, then inside that group there several other groups and inside them there are several other groups. I need to know what groups each user belongs to. Example:
Group(s) | User Id | Display Name |
MainGroup-->SecondaryGroup-->DetailedGroup101Developer | 123456 | Adam Xyz |
MainGroup-->SecondaryGroup-->DetailedGroup101BusinessUser | 123456 | Adam Xyz |
MainGroup-->SecondaryGroup-->DetailedGroup105Admin | 678900 | Alexander Qwe |
I have the following request:
https://graph.microsoft.com/v1.0/groups/<MainGroupId>/transitiveMembers/microsoft.graph.user?$expand=memberOf($select=displayName)&$select=Id, displayName, Department, JobTitle, Mail, MailNickName
but it lists ALL user's groups, while I only need users and nested group information from the <Main Group>
So my questions are as follows:
1. Is it possible to get data in the form I need it?
2. Is the solution proposed by @Expiscornovus suitable for a list of 300 users with the potential for more? At first glance, that flow only seems to load 100 rows? So should one more step be added that somehow reads the table in batches and appends the resulting csv file?
Apologies for a long post and thank you in advance!
Hi @MoOnan,
In the current example (from that other thread) the microsoft.graph.user OData cast is used. This way only nested user objects will be retrieved. You could remove that microsoft.graph.user OData cast or replace it by a microsoft.graph.group OData cast. This would retrieve an overview of all groups with the main group.
Regarding your second question, retrieving the groups for a single user. I would approach that slightly different. I would suggest to look into the List a user's memberships (direct and transitive) method instead.
https://graph.microsoft.com/v1.0/users/<userid>/transitiveMemberOf/microsoft.graph.group?$select=id,displayName
That will still not answer your question of which groups are part of the main group though.
For that part you could use an intersection and cross reference the list of groups from query A (all groups of maingroup) with the list of groups from query B (all groups from user 3).
Thank you for explanation!
And regarding my second problem, do you know if it is possible to get information about user's groups but only those inside my <Main Group>?
Expand=memberOF doesn't do what I need, it shows all groups of the user. What I need is only groups inside Main Group, so for example I have this structure:
So for User 3 I would need such information:
Main Group -> Secondary Group B -> Detailed Group 102 Developers | User 3
Main Group -> Secondary Group B -> Detailed Group 102 Admin | User 3
And next to it all details about the users, which I get from this request:
https://graph.microsoft.com/v1.0/groups/<MainGroupId>/transitiveMembers/microsoft.graph.user?$select=Id, displayName, Department, JobTitle, Mail, MailNickName&$top=999
Is it possible to get it in one request?
Hi @MoOnan,
Yes, by default that method will list 100 rows. You can add a $top query parameter and retrieve a maximum of 999 per page.
Try something like:
https://graph.microsoft.com/v1.0/groups/<MainGroupId>/transitiveMembers/microsoft.graph.user?$expand=memberOf($select=displayName)&$select=Id, displayName, Department, JobTitle, Mail, MailNickName&$top=999
When dealing with more than 999 users you will need to build in some logic to retrieve the results per page. More about handling paging can be found over here:
https://learn.microsoft.com/en-us/graph/paging?context=graph%2Fapi%2F1.0&view=graph-rest-1.0
WarrenBelz
146,776
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,093
Most Valuable Professional