Get and visualize GROUP MEMBERS & OWNERS with Power Automate & Power BI
Вставка
- Опубліковано 22 лип 2024
- This works for M365/Teams groups, Azure AD groups, and distribution groups. This can be useful for auditing membership, cleaning up stale groups, and generally getting visibility into your groups data! In this video, we also demo row-level security to filter groups on the group owner.
0:00 Intro, data preview
2:25 Create a scheduled Power Automate flow
8:35 Pagination for large queries
9:40 Get all group owners in tenant
16:07 Connect to the data in Power BI
19:00 Creating a link to manage users in Azure
20:25 Relate the groups to the members
21:00 Visualization
22:50 Dynamic row-level security for group owners - Наука та технологія
Thanks for the video Christine, this was really useful :)
Brilliant! Thanks Christine 🙂
Very impressive work!
This is great! Thanks. Unfortunately my flow fails as there's too many groups/members in my environment. Is there a way to get members from only one specific group?
Oh totally, there’s a separate action for that - I think it’s called “list group members” and it has you select a specific group. If you search for “Groups” there’s a whole set of actions to do things with specific groups. :)
Great video, but the resolution is too high --> hard to read what is happening
Noted, thanks :)
Thank you for this video. I'm trying to use this workflow to get group members from a security group in Entra. I'm hoping this will work. I think I added the Append to array variable groupMembers the way you described, but I'm not sure since I can't see the detail in the video. I keep getting a error message that says, Enter a valid JSON. Is this correct? {
"upn":"@{item()?['userPrincipalName']}",
"userid":"@{item()?['id']}",
"groupid"@{item()?['id']}",
"userrole": "Member"
}
Any ideas? Well, I answered my own question when I pasted my action here. I missed a : and " on groupid.
It's always the typos with the hand-typing JSON! :)
This is great, until I get to the 15:00 mark...I cannot find "body" in the list groups content.
If you're in modern UI, switch to classic. It's not in modern yet. Also make sure to search for it, it's not there unless you search. Lol.
Hi Christine, I was thinking to get list of users from Power BI workspaces, is there any way to do that? can you make a video for it?
User permissions are in the Power BI Admin API: learn.microsoft.com/en-us/rest/api/power-bi/admin/users-get-user-artifact-access-as-admin
The PBI admin API is on my to-do list for sure, it’s just a matter of time - been short on that lately. 😅
Is there a way to use a forms as trigger and to get the list of the groups that owned by the responder on forms.
I think you could probably use the HTTP action to call the Graph API to get this. I can't find an example in the documentation, but this thread has an example where they get groups for a specific user that would probably work. You'd use this on the MS Forms trigger for when a response is created and pull the submitter's username from the trigger (you might need to get their user ID from the "get user profile" first, I can't recall if the email will work directly).
edit - forgot the link: stackoverflow.com/questions/73048102/how-to-receive-list-of-groups-that-user-is-member-of-in-powerapp
the send an http action fails for with "internal server error".
I can use same query in graph explorer and get owners but not via send http action of O365 group.
The user creating flow is Teams and power platform admin, not sure why this isn't working.
Hmm, I'm not sure offhand why that would be. I vaguely recall testing this with a non-privileged test account so I don't think it's permissions. Is the HTTP connector blocked perhaps? Or double check the code, usually when I get internal errors with these it's because I forgot a comma or a bracket or something.
@@bi-ome appreciate your reply to my comment.
I figured out I had enclosed Group ID in the graph query within curly braces, after hours of trial removing those worked. Wish the error was more meaningful!
In my case I'm fetching Group IDs from SPO list as I need to only report on a handful of Team members.
I liked the way you're reporting the result of flow in P BI, really cool :)
Hi Christine thank you for your work. I am facing an issue, after I ran the first test, the HTTP request step only returns odata.context and value lines.
Here is no result like: odata.type, id, businessphones etc. and the flow runs around 10 minutes every time. Could you please let me know how can I fix this issue?
Are you in the new UI? Try toggling it off if you are, it's missing a lot of the dynamic content cards.
@@bi-ome Christine, thank you for you response. No, I am using the old UI. Is there any additional step I need to do before create this flow?
Assuming I have last modified date in the table, how can I list all Teams/Groups not modified in last 6 months, 1 year, etc.?
I'm not very good with DAX and not sure what formula to use for this.
I would just drop the modified date into the filter pane or a slicer - there's a relative date option there that will let you do last x days/months/years. You'd only need DAX if you were trying to measure something related to the modified date (like counting stale groups).
can someone paste the Append to array Variable values step?-
can see due to high resolution
tks
Sorry, I've started using UI zoom the last couple months for that reason! 😅
{
"userId": "@{items('Apply_to_each')?['Id']}",
"upn": "@{items('Apply_to_each')?['UserPrincipalName']}",
"managerUpn": "@{outputs('Get_manager_(V2)')?['body/userPrincipalName']}",
"displayName": "@{items('Apply_to_each')?['DisplayName']}",
"department": "@{items('Apply_to_each')?['Department']}"
}
Is there a way to make the query to run through a specific list of groups? instead through all of them? even with pagination, my organisation has way too much groups
Yes, you can either just put a list of the group IDs in an array and loop over those if you know which you want, or you can use filters on a Graph API query to get a list of specific groups on some criteria: learn.microsoft.com/en-us/graph/filter-query-parameter?tabs=http (then use the resulting group IDs in your loop).
@@bi-ome thank you! And do you know maybe a way to get a date of when user was added to the group?
I think you'd have to query the audit logs for that. I found a thread on it here but there's not a ton of info on this. The audit log history only goes back so far, too, so you'd have to store it when you query to build a history: stackoverflow.com/questions/76235207/how-can-i-find-when-a-user-was-added-to-an-azure-ad-group-using-the-microsoft-g
@@bi-ome and do you know maybe how to query GRAPH API through “run query against a dataset” with just one column with emails?
Why did you do this with power automate in the middle, instead of directly via MS Graph queries inside PowerBI only?
Thinking the method in the video means you've got priviledged data sitting in SharePoint in raw format in your JSONs
Querying APIs directly in PBI is a pain, particularly when you get into pagination… if I were to do it anywhere serious it’d be a real ETL tool to put it in a database table, but this isn’t a data engineering channel.
SharePoint is as secure as the site you store it in… I would not put this in a public site obviously, but I think it’d be fine in an IT team site or similar. In the grand scheme of things user and group membership is not particularly sensitive in comparison to what is regularly stored in SP in everyday use.
How to build a direct-query report and get the Group membership on-demand ?
There’s not a direct query source for this that I’m aware of. You could schedule this for once a day though, I can’t see a reason that you’d need it fresher than that in normal use.
Append to array Variable step is throwing an error "Enter a valid JSON"
Usually this means you have a typo somewhere in there - extra or missing comma, curly bracket, or quotation
What is the https action's URI? can you please post it here?
Here you go, this is to get owners - replace the bracket bit with your dynamic content: graph.microsoft.com/v1.0/groups//owners
edit... UA-cam does not like the hyperlink formatting, but you get the idea 🤨
Great, thank you!
There is also a link in the platform where users can go to manage groups they own: myaccount.microsoft.com/groups/groups-i-own - FYI!
seriously, can you guys see the text on the screen?
It helps if you set the video quality, sometimes it will default to low. I have since started using UI scaling but I can’t retroactively fix without re-recording
@@bi-ome It's set to 1080p but still can't make out a lot of the text