cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Datalyzer
Frequent Visitor

Action 'HTTP' failed while trying to query the Dataverse API

Hello,

 

I'm trying to retrieve all the users and their roles across some environments in my Office 365 tenant using a Power Automate flow, but when I'm trying to query the Dataverse Web API either with OData or fetchXml, I always get the same error:

 

2boIj

 

d4iVI

 

At this point I don't have any clue on how to fix it, if anyone could point me in the right direction I would really appreciate it. I also tried this in postman and it works without problem.

 

Thanks for your help.

 

I'm adding some extra information below.

 

URI

https://someorg.crm2.dynamics.com/api/data/v9.2/systemusers

 

Headers

{
  "Accept": "application/json",
  "OData-MaxVersion": "4.0",
  "OData-Version": "4.0",
  "Prefer": "odata.include-annotations=\"*\"",
  "If-None-Match": "null",
  "Content-Type": "application/json; charset=utf-8"
}
 
Query
<fetch top="1000">
  <entity name="systemuser">
    <attribute name="fullname" />
    <attribute name="domainname" />
    <link-entity name="systemuserroles" from="systemuserid" to="systemuserid" intersect="true">
      <link-entity name="role" from="roleid" to="roleid" intersect="true">
        <attribute name="name" />
        <attribute name="roleid" />
      </link-entity>
    </link-entity>
  </entity>
</fetch>
 
Note: the query was encoded in URL acceptable format
 
%3Cfetch%20top%3D%221000%22%3E%0A%20%20%3Centity%20name%3D%22systemuser%22%3E%0A%20%20%20%20%3Cattribute%20name%3D%22fullname%22%20%2F%3E%0A%20%20%20%20%3Cattribute%20name%3D%22domainname%22%20%2F%3E%0A%20%20%20%20%3Clink-entity%20name%3D%22systemuserroles%22%20from%3D%22systemuserid%22%20to%3D%22systemuserid%22%20intersect%3D%22true%22%3E%0A%20%20%20%20%20%20%3Clink-entity%20name%3D%22role%22%20from%3D%22roleid%22%20to%3D%22roleid%22%20intersect%3D%22true%22%3E%0A%20%20%20%20%20%20%20%20%3Cattribute%20name%3D%22name%22%20%2F%3E%0A%20%20%20%20%20%20%20%20%3Cattribute%20name%3D%22roleid%22%20%2F%3E%0A%20%20%20%20%20%20%3C%2Flink-entity%3E%0A%20%20%20%20%3C%2Flink-entity%3E%0A%20%20%3C%2Fentity%3E%0A%3C%2Ffetch%3E
 
 
 
6 REPLIES 6
Expiscornovus
Super User
Super User

Hi @Datalyzer,


Have you tried url encoding the fetch xml query and using it with the ?fetchXml=parameter in your uri?

 

Below is an article about that approach:

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/use-fetchxml-web-api

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Hey @Expiscornovus Yes, I already encoded it (just writing it that way here so it's readable), I forgot to mention it. I tried the request in the browser and postman and it works as expected, but for some odd reason in Power Automate I get that error.

Expiscornovus
Super User
Super User

Hi @Datalyzer,

 

Just to double check. You did try to use the fetchxml query directly in the URI and not in the Queries section of the HTTP action?

 

Below is an example.

 

encodeUriComponent(variables('FetchXml'))

 

fetchxmlinuri.png

 

I tested it with a simple fetch xml query (from the Microsoft doc shared earlier):

<fetch mapping='logical'>
   <entity name='account'>
      <attribute name='accountid'/>
      <attribute name='name'/>
      <attribute name='accountnumber'/>      
</entity>
</fetch>

 

Below is the result of my test.

 

example_fetchxmluri.png

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Hey @Expiscornovus 

 

Thank you for your response. I haven't actually tried that yet, so I did it that way and I still get the same error.

 

Datalyzer_0-1668060123057.png

 

This is what I have in the action:

 

Datalyzer_1-1668060218052.png

 

And if use the resulting URI in the browser, it works alright

 

Datalyzer_2-1668060450024.png

 

It's really confusing. I even exported the solution and imported in another environment.

 

Thanks a lot of your help. I think I might submit a ticket or just try another approach with Power BI instead of Power Automate.

Expiscornovus
Super User
Super User

Hi @Datalyzer,

 

I have just tried to reproduce the issue with your fetchxml.

 

Works fine over here as well. I am afraid I am unable to reproduce your issue at the moment.

 

Only thing I can think of is that it might be an authentication setup kind of thing. For example I am using a different Authority setup. I would also check things like your client id, secret and application user registration/role in your environment.

 

And to rule stuff out I would also suggest to try a simple fetchxml query on a different table. For example the one I used in my first test with accounts.

 

Below are the details of my test. That might be useful as well (so you can compare).

 

1. The setup I used with your fetch xml query

 

authoritysetup.png

 

2. The test results

fetchxml_systemusers.png

 

Just to double check. You did create an application user for your Azure AD app (client id) and assigned a role in the environment/org url? 

https://docs.microsoft.com/en-us/power-platform/admin/manage-application-users#create-an-application...

 

I have also written a blog on how to use this in a HTTP action:

https://www.expiscornovus.com/2021/03/14/how-to-use-the-dataverse-web-api/

 

applicationuser_environment.png



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Hey @Expiscornovus 

 

Thank you very much for your help. I was missing to add the application user. After I did that, I could use https://login.microsoftonline.com as Authority, that's why I was having to use https://login.microsoftonline.com/2c15f21f-ae9b-4fde-a5ea-e0f93949ac6a/oauth2/authorize?resource=[En... URL] in the past to authenticate, but I was getting that error.

 

Datalyzer_0-1668085555644.png

 

 

Now I have a question, do you know if there's a way to add the application user for every single environment at once? I'm looping this through all the environments in the tenant (filtering out the ones without Dataverse), because I need to get all the roles and user per environment.

 

Thanks a lot.

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,605)