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

[Datalyzer]

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:

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

[Expiscornovus]

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

[Datalyzer]

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]

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'))

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.

[Datalyzer]

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.

This is what I have in the action:

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

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]

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

2. The test results

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/

[Datalyzer]

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.

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.