Use query data to create a JSON web URL?

[PeterShankland]

Hi, I am using the following URL to capture information from Microsoft Defender 365 which includes the DeviceID of all the devices within the specified RBAC Group ID as a list:

https://api.securitycenter.microsoft.com/api/machines?$filter=rbacGroupId+eq+7529  

I then need to be able to use the value.id file from this table to populate the following URL to then retrieve vulnerability details for all the devices that were brought back in that initial query:

https://api.securitycenter.microsoft.com/api/vulnerabilities/machinesVulnerabilities?$filter=machine... } 

Firstly, is this even possible with Power BI and if so, could someone please point me in the right direction?

Thanks.

[v-yueyunzh-msft]

Hi , @PeterShankland 

According to your description, you want to send a http request to get the DeviceID and then put the DeviceID in another http request url.

If this , i my thought, you can try to get the all DeviceID in a table as a list . And then  you can create a custom funtion in Power Query Editor and the parameter is the DeviceID .You can refer to :
Understanding Power Query M functions - PowerQuery M | Microsoft Learn
Web.Contents - PowerQuery M | Microsoft Learn

And then you can use "Custom Column" to add a column and run the custom function and put the current DeviceID to the function.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PeterShankland]

Hi, here is a screenshot of the first query showing the Device ID (value.id) that is required for the second query:

Then, looking at the following post about using Power BI to report on Defender 365 API data, I have started creating a new query using the advanced editor:

Microsoft Defender for Endpoint APIs connection to Power BI | Microsoft Learn

The query needs to include 'machines[value.id]' but I am not sure of the syntax.

Thanks.

[v-yueyunzh-msft · Accepted solution]

Hi , @PeterShankland 

As a sample , you can try to update the odata to a function, like this:

(ID)=>
let
    t=ID+1
in
    t

Then we can add a custom column in the Device ID table:

Then we can use the function each row:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[grandtotal]

Can you post the result of the first URL?