I want to point out that if your organization is LARGE, like many thousands of users, you will need to use a more scalable tool to extract this data like Logic Apps or PowerShell. Power Automate is mean to be a user or team-level tool, when you move to the enterprise scale you need something beefier.
You simply CANNOT begin to imagine how much time and frustration you've saved me. Unfortunately, my organisation has assigned VMs with "Person" accounts. The motricity accounts to a sum of 80k + "users" which are mapped to line managers that do not work for the org anymore... *Now it is my amateur understanding that PowerShell needs to be manually triggered? Do you have an automated recommendation?
@@testktrkrt I'm not sure I am following the question - but you can schedule PowerShell scripts to run with Windows Task Scheduler. Usually you'd have this on a VM or server that was always on, because it has to be on when the task is supposed to start. There are a TON of third-party tools for cleaning up orphaned users, if you have budget for it I would recommend that to save your sanity. But I'm not sure if your issue is with user references or out-of-date user profiles? Many orgs will sync profile data like manager and department from their HR system via integrations. It helps to have that HR data go to an intermediary SQL server - it's very useful for all kinds of things to have in an accessible database (reporting, syncing to other systems, etc).
@@bi-ome Hiya. The problem as you correctly pointed out is with out-of-date user profiles. I've tried using the "PATH" function on PowerBI but the amount of orphan users (for AD managers that do not exist anymore) is way out of proportion. Can you recommend a few of these apps you mentioned above for cleanup? *They have tried to map "line managers" through Workday once upon a time but that didn't go out well... **I have accepted the fact the the Active Directory and/or the Office365 connector is a hot mess...
Hi Christine, Is there a way I can specify the type of users we want to search for? If I leave it blank it searches for EVERYONE but I'm looking for example just the "Department":"Outside Sales"
Hmm, good question. It looks like it's just searching variants of the name from the tooltip (I tried searching Sales in mine too, and it came back blank). You could either: 1. Search to get everything, then use Filter Array to filter on department; or 2. Use the "get direct reports" action instead, and feed it a manager's login name (assuming everyone in the department reports to them)
@@bi-omeI ended up using the first option with a Filter Array and it's working properly. Wanting to give you a video idea since I just did this with my team not too long ago - creating a dynamic approver process through sharepoint and power automate.
I think we have over 20,000 employees. If I get all and then filter, I am risking a performance problem? Or since it’s all in the cloud, am I ok? I can’t seem to get the filter array to work right. Where would it be positioned in the order?
@@Romenet310 Worst case MS will throttle the flow - I think your allowed usage depends on the licensing. Pagination supposedly maxes out at 100k but I don’t have a large number of users so I couldn’t test it. If you’re willing, please try it and let me know how it does! If it doesn’t work, the official way to be doing this is to use a scheduled PowerShell script (would require an admin account and local admin access on the machine).
Thank you Christine, this was really Awesome! 🤝 I have created this flow without 3rd step Search user instead I used Excel table with specified set of members as this search user is not seems to be working with our org which has huge members.
Yeah, what you'd probably need to do here is use a "parse json" action on your users variable, then for-each loop on the items in it to create an item for each. You'd need to decide how you handle updates to it, otherwise it'll just add all the users every time it runs and you probably don't want duplicates, so it's more complicated than it seems at the outset, particularly if you need to remove individuals that leave on top of that. I'll put "get data from somewhere and put it in a SP list" on my queue. 😀
I get the following error: Flow save failed with code 'InvalidPaginationPolicy' and message 'The pagination policy of workflow run action 'Search_for_users_(V2)' of type 'OpenApiConnection' at line '1' and column '1308' is not valid. The value specified for property 'minimumItemsCount' exceeds the maximum allowed. Actual: '40000'. Maximum: '5000'.'.. I am assuming that means we are limited to returning 5000 values...with a large company, is there a way around this? A different way to do it? This is exactly what I need, but I have to create a table of users to be able to use Path in PowerBI for hierarchy purposes. Any suggestions?
Yeah, Power Automate isn’t great for looping through large numbers like that. Usually an org of that size has user data in a database synced from somewhere like the HR system with a higher-powered tool. PowerShell may be able to get it too, but I’m not as familiar with that.
Their name is in the outputs already, it's the display name. The image... there is an action to get the profile image, but the output is base64 and I don't think Teams likes base64 images, so you might just want to skip that and try linking directly to the profile photo, since it follows a specific URL pattern and already lives in the root of SharePoint. I think Teams will probably embed image links automatically when they're added as a link, but haven't tried it. Here's an article: vitalyzhukov.com/en/sharepoint-online-getting-user-picture-url Filtering is harder, the search users action won't let you filter on department - so you'd have to call the Graph API instead (you can use the HTTP action for that). Link: learn.microsoft.com/en-us/graph/api/user-list?view=graph-rest-1.0&tabs=http#optional-query-parameters OR if you want to greatly simplify your life, in Teams you can create tabs for SharePoint pages in the associated SharePoint site. SharePoint has a web part that will display an org chart starting from a particular user, which has their profile photo and profile link in there automatically. This will take about 30 seconds to set up, so if that fits what you need I would do that instead. :)
Hi Christine. good video from you but as I see the apply for each action can not exceed more than 999 values from the search for user v2 action. is there a way to process all users. In my organisatiin there are maybe more than 500k users. it would be fine to get all of them no matter the column orders etc.
PA won’t be handling anything with 500k I don’t think - people normally get around size issues with pagination settings or querying in parts, but 500k is too big.😅 PowerShell will probably be your best bet - it’ll let you set ResultSetSize to increase the max size. learn.microsoft.com/en-us/powershell/module/activedirectory/get-adobject?view=windowsserver2022-ps
Hi Christine, great video and really clear. Do you know if its possible, or how I would, specify say a list of 100 specific names and run this flow? I have a huge user base and just need this for around 100 names. I thought maybe an apply to each but I wasn't sure? Thanks!
Yeah, if you get all the emails in an array variable, or even Excel would probably work, you can “apply to each” on those and use “get user profile” instead of the search action and append the outputs to a variable (or add rows to Excel).
Thanks! Licensing info isn't in the user profile. You can get it with Graph API with an HTTP action, but people typically use PowerShell for that sort of thing--
Have you tried dropping an example of the array output into Chat GPT and asking it for a formula for Power Automate Cloud? I tried that and it says to stick this in a Compose action: json(concat('{', join(InsertOutputHere, ','), '}')) Otherwise you could parse the array first and use that in the concat instead if you need more control--
It depends on which level you're looking at - if it's at the tenant level, you need to create an app registration in Azure for the permissions and then call the Power BI Admin API. It's not super common to use Power Automate for this because it's more in the ETL category and involves moving larger amounts of data around, but you probably could use it if you wanted to. Logic apps would probably work better. The alternative is to use the workspace usage reports that you can get to through the UI - much easier but smaller scale. Rui Romano has a Github project called PBIMonitor that is popular for pulling this data for the tenant if you search on his name. I haven't tried it yet but it's on my list - it would be easier than starting from scratch.
Awesome tutorial, great job; appreciate your work. I used dataverse table to save the output. I want to compare it with the D365HR user profile and automate update EntraID profile based on D365HR.
You would need to do a Graph API call for that, here's the documentation: learn.microsoft.com/en-us/graph/api/user-assignlicense?view=graph-rest-1.0&tabs=http If you want to use Power Automate, it'd be the HTTP connector, but most people get licensing data with PowerShell - there's a slew of PowerShell scripts available for it out there online because that's how most administrators manage licensing at scale.
Firstly, thanks for the great content! I work for a global company and even if I set the maximum value in pagination for Search for Users, it gives me not all the users. Actually I want to filter the users with their domain name on emails (the text after @) before searching all the users. Although, there is a ‘Search Term’ opportunity on Search for Users, I couldn’t set it with the email option. Is there a way to filter all users, before Search for Users? I think, I explained what I want to achieve 😀 Greetings from Türkiye 🇹🇷
Thanks! Yes - there is a limit, but I could not seem to find any record of what the return limit actually is in the documentation, what was the number it stopped at for you? The search does not support wildcards and seems to only search from the beginning of the username (for example I searched for "christine" and got results, but nothing with using my domain). Your best bet is probably to try the HTTP Request action in Power Automate and query the Graph API: learn.microsoft.com/en-us/graph/api/user-list?view=graph-rest-1.0&tabs=http
The number, which stopped me was around 100.000. I tried the suggestion 'HTTP Request Action' you made (with UserPrincipialName), but it gives me the 'Error Code: 500', and I think it's because of the administrative permission, which I don't have. But I solved my problem in a different way: I got the UserIDs (about 150) from our internal website and copy them to Excel programmatically. Then I use 'List rows present in a table' in order to read them, appended to array, and then search them in 'Search for Users (V2)' with 'Apply to Each'. Finally I got them with their departments, which I later use in my Power BI Dashboard. Thanks again! Your video about Search for Users saved me and showed me the Power of 'Power Automate' !!!
If you have a list of users, you don’t need the profile search. You can just put the “get user profile” action in a loop on your list and put their username in the dynamic content for the “get”. If you need to put the data somewhere, you can put an “append to array variable” step in the loop to get it all in one variable.
I want to point out that if your organization is LARGE, like many thousands of users, you will need to use a more scalable tool to extract this data like Logic Apps or PowerShell. Power Automate is mean to be a user or team-level tool, when you move to the enterprise scale you need something beefier.
You simply CANNOT begin to imagine how much time and frustration you've saved me.
Unfortunately, my organisation has assigned VMs with "Person" accounts.
The motricity accounts to a sum of 80k + "users" which are mapped to line managers that do not work for the org anymore...
*Now it is my amateur understanding that PowerShell needs to be manually triggered?
Do you have an automated recommendation?
@@testktrkrt I'm not sure I am following the question - but you can schedule PowerShell scripts to run with Windows Task Scheduler. Usually you'd have this on a VM or server that was always on, because it has to be on when the task is supposed to start. There are a TON of third-party tools for cleaning up orphaned users, if you have budget for it I would recommend that to save your sanity. But I'm not sure if your issue is with user references or out-of-date user profiles? Many orgs will sync profile data like manager and department from their HR system via integrations. It helps to have that HR data go to an intermediary SQL server - it's very useful for all kinds of things to have in an accessible database (reporting, syncing to other systems, etc).
@@bi-ome Hiya. The problem as you correctly pointed out is with out-of-date user profiles. I've tried using the "PATH" function on PowerBI but the amount of orphan users (for AD managers that do not exist anymore) is way out of proportion.
Can you recommend a few of these apps you mentioned above for cleanup?
*They have tried to map "line managers" through Workday once upon a time but that didn't go out well...
**I have accepted the fact the the Active Directory and/or the Office365 connector is a hot mess...
Once again, it is absolutly clear and understandable even for a french user. Thanks :)
Please would you let me know how you can use this data to overwrite a Sharepoint list. Thank you.
I thought this was really good. Not exactly what I was looking for but very close and helpful for my newbie self. Thank you!
Hi Christine, Is there a way I can specify the type of users we want to search for? If I leave it blank it searches for EVERYONE but I'm looking for example just the "Department":"Outside Sales"
Hmm, good question. It looks like it's just searching variants of the name from the tooltip (I tried searching Sales in mine too, and it came back blank). You could either:
1. Search to get everything, then use Filter Array to filter on department; or
2. Use the "get direct reports" action instead, and feed it a manager's login name (assuming everyone in the department reports to them)
@@bi-omeI ended up using the first option with a Filter Array and it's working properly. Wanting to give you a video idea since I just did this with my team not too long ago - creating a dynamic approver process through sharepoint and power automate.
@@luigir2459 Awesome! I have been meaning to make a video on approvals, it's on my list for sure. 😄
I think we have over 20,000 employees. If I get all and then filter, I am risking a performance problem? Or since it’s all in the cloud, am I ok? I can’t seem to get the filter array to work right. Where would it be positioned in the order?
@@Romenet310 Worst case MS will throttle the flow - I think your allowed usage depends on the licensing. Pagination supposedly maxes out at 100k but I don’t have a large number of users so I couldn’t test it. If you’re willing, please try it and let me know how it does!
If it doesn’t work, the official way to be doing this is to use a scheduled PowerShell script (would require an admin account and local admin access on the machine).
Thank you Christine, this was really Awesome! 🤝 I have created this flow without 3rd step Search user instead I used Excel table with specified set of members as this search user is not seems to be working with our org which has huge members.
Can you please show how to get all m365 users and add them to sharepoint list. eg name, email address, title, department and Office location.
Yeah, what you'd probably need to do here is use a "parse json" action on your users variable, then for-each loop on the items in it to create an item for each. You'd need to decide how you handle updates to it, otherwise it'll just add all the users every time it runs and you probably don't want duplicates, so it's more complicated than it seems at the outset, particularly if you need to remove individuals that leave on top of that. I'll put "get data from somewhere and put it in a SP list" on my queue. 😀
I get the following error: Flow save failed with code 'InvalidPaginationPolicy' and message 'The pagination policy of workflow run action 'Search_for_users_(V2)' of type 'OpenApiConnection' at line '1' and column '1308' is not valid. The value specified for property 'minimumItemsCount' exceeds the maximum allowed. Actual: '40000'. Maximum: '5000'.'..
I am assuming that means we are limited to returning 5000 values...with a large company, is there a way around this? A different way to do it? This is exactly what I need, but I have to create a table of users to be able to use Path in PowerBI for hierarchy purposes. Any suggestions?
Yeah, Power Automate isn’t great for looping through large numbers like that. Usually an org of that size has user data in a database synced from somewhere like the HR system with a higher-powered tool. PowerShell may be able to get it too, but I’m not as familiar with that.
How do I extract the user's name and the link to their profile image in Teams? Is it possible to extract from a specific department?
Their name is in the outputs already, it's the display name. The image... there is an action to get the profile image, but the output is base64 and I don't think Teams likes base64 images, so you might just want to skip that and try linking directly to the profile photo, since it follows a specific URL pattern and already lives in the root of SharePoint. I think Teams will probably embed image links automatically when they're added as a link, but haven't tried it. Here's an article: vitalyzhukov.com/en/sharepoint-online-getting-user-picture-url
Filtering is harder, the search users action won't let you filter on department - so you'd have to call the Graph API instead (you can use the HTTP action for that). Link: learn.microsoft.com/en-us/graph/api/user-list?view=graph-rest-1.0&tabs=http#optional-query-parameters
OR if you want to greatly simplify your life, in Teams you can create tabs for SharePoint pages in the associated SharePoint site. SharePoint has a web part that will display an org chart starting from a particular user, which has their profile photo and profile link in there automatically. This will take about 30 seconds to set up, so if that fits what you need I would do that instead. :)
actual information, well described thank you.most appreciated.
Hi Christine. good video from you but as I see the apply for each action can not exceed more than 999 values from the search for user v2 action. is there a way to process all users. In my organisatiin there are maybe more than 500k users. it would be fine to get all of them no matter the column orders etc.
PA won’t be handling anything with 500k I don’t think - people normally get around size issues with pagination settings or querying in parts, but 500k is too big.😅 PowerShell will probably be your best bet - it’ll let you set ResultSetSize to increase the max size. learn.microsoft.com/en-us/powershell/module/activedirectory/get-adobject?view=windowsserver2022-ps
Hi Christine, great video and really clear. Do you know if its possible, or how I would, specify say a list of 100 specific names and run this flow? I have a huge user base and just need this for around 100 names. I thought maybe an apply to each but I wasn't sure?
Thanks!
Yeah, if you get all the emails in an array variable, or even Excel would probably work, you can “apply to each” on those and use “get user profile” instead of the search action and append the outputs to a variable (or add rows to Excel).
@@bi-ome thanks so much!
Great explanation! Thank you!
Is it possible to get licenses assigned to users through this?
Thanks! Licensing info isn't in the user profile. You can get it with Graph API with an HTTP action, but people typically use PowerShell for that sort of thing--
Thank you for this tutorial. However, I’d like to turn the array to an Object to use in an adaptive card. I’m having a hard time converting the array.
Have you tried dropping an example of the array output into Chat GPT and asking it for a formula for Power Automate Cloud? I tried that and it says to stick this in a Compose action: json(concat('{', join(InsertOutputHere, ','), '}'))
Otherwise you could parse the array first and use that in the concat instead if you need more control--
Hi how we do user activity for power bi service using power automate
Thanks in advance dear
It depends on which level you're looking at - if it's at the tenant level, you need to create an app registration in Azure for the permissions and then call the Power BI Admin API. It's not super common to use Power Automate for this because it's more in the ETL category and involves moving larger amounts of data around, but you probably could use it if you wanted to. Logic apps would probably work better. The alternative is to use the workspace usage reports that you can get to through the UI - much easier but smaller scale.
Rui Romano has a Github project called PBIMonitor that is popular for pulling this data for the tenant if you search on his name. I haven't tried it yet but it's on my list - it would be easier than starting from scratch.
Thank you so much! That was so well communicated 👏❤
Awesome tutorial, great job; appreciate your work. I used dataverse table to save the output. I want to compare it with the D365HR user profile and automate update EntraID profile based on D365HR.
ooh that sounds like a great idea!
How can I retrieve the license data of a user (so that I can see which user has which license)?
You would need to do a Graph API call for that, here's the documentation: learn.microsoft.com/en-us/graph/api/user-assignlicense?view=graph-rest-1.0&tabs=http
If you want to use Power Automate, it'd be the HTTP connector, but most people get licensing data with PowerShell - there's a slew of PowerShell scripts available for it out there online because that's how most administrators manage licensing at scale.
Perfect tutorial.
Big thanx
Firstly, thanks for the great content!
I work for a global company and even if I set the maximum value in pagination for Search for Users, it gives me not all the users. Actually I want to filter the users with their domain name on emails (the text after @) before searching all the users.
Although, there is a ‘Search Term’ opportunity on Search for Users, I couldn’t set it with the email option.
Is there a way to filter all users, before Search for Users?
I think, I explained what I want to achieve 😀
Greetings from Türkiye 🇹🇷
Thanks! Yes - there is a limit, but I could not seem to find any record of what the return limit actually is in the documentation, what was the number it stopped at for you? The search does not support wildcards and seems to only search from the beginning of the username (for example I searched for "christine" and got results, but nothing with using my domain). Your best bet is probably to try the HTTP Request action in Power Automate and query the Graph API: learn.microsoft.com/en-us/graph/api/user-list?view=graph-rest-1.0&tabs=http
The number, which stopped me was around 100.000.
I tried the suggestion 'HTTP Request Action' you made (with UserPrincipialName), but it gives me the 'Error Code: 500', and I think it's because of the administrative permission, which I don't have.
But I solved my problem in a different way: I got the UserIDs (about 150) from our internal website and copy them to Excel programmatically. Then I use 'List rows present in a table' in order to read them, appended to array, and then search them in 'Search for Users (V2)' with 'Apply to Each'. Finally I got them with their departments, which I later use in my Power BI Dashboard.
Thanks again! Your video about Search for Users saved me and showed me the Power of 'Power Automate' !!!
How can I apply this to a list of users?
If you have a list of users, you don’t need the profile search. You can just put the “get user profile” action in a loop on your list and put their username in the dynamic content for the “get”. If you need to put the data somewhere, you can put an “append to array variable” step in the loop to get it all in one variable.
great content - as always
You're the best! Thank you.
great stuff.. appreciated.
Thank you!!
Thank you Christine!!!!!!
Excellent
Simple, clear, wonderful. Marry Me.