Create Wallboard from API REST calls

[Darwood]

How can I get the output of a Web Activity into a Lakehouse in Microsoft Fabric please?

A bit of background:

I am trying to create a wallboard showing the status of our IT Infrastructure. One part of this is pulling a list of devices from NinjaOne using their Web/REST API. This is documented here:

• Authorization
• Core Resources

This use OAuth authentication which doesn't seem to be supported by the various Web and REST connections in Microsoft Fabric. However I have managed to create a Data Pipeline which sends a PUT request for an access token and then uses this in a GET request which returns an array of Devices in JSON format. This appears to be working as it runs without error but I'm not sure how to make the returned JSON data available for reporting on or even how to preview the API response in Fabric.

I have tried adding a Copy Data activity to my Data Pipeline but the Source tab just has a Connection field which doesn't list the output of the previous step as an option. I can select "Use dynamic content" and then select the the output of the previous activity but this just seems to fill in the name of the Connection and I still need to select a Connection type from the list of Lakehouse/KQL Database/Warehouse of which mine is neither.

I can select REST as a data source for the Copy Data activity but then I won't be able to authenticate as I need the Pipeline to send the POST request for a token first.

Could someone guide on the correct/best approach for this please. I presume I need to get the output from the REST/API call into a Lakehouse but I can't see a way to do it.

[Darwood]

I've managed to get this working by using a ForEach step and putting a Copy Data activity inside of there so it copies one record at a time. It's a very kludgy solution but it's working. I have to say MS Fabric functionality for getting data from an API with OAuth is very poor. It's taken me days just to do what took me about 30m with Grafana.

[nilendraFabric]

Apols @Darwood I was busy , so didn't got time to look into this.
Happy it worked out.

Could you please accept the solution if this was helpful 🙂

[nilendraFabric · Accepted solution]

hello @Darwood 

I think you have done most of the hard work to get the authentication and connection done

Now for Web Activity Output Handling
• Use a Set Variable activity to capture the Web Activity response
• Configure the variable to store the complete JSON output

Create Copy Data Activity Setup
• Create a dummy source query using a Fabric Datawarehouse connection
• Use the “Additional Columns” section in the source to define your output schema
• Configure the destination as the Lakehouse Files section
• Map the fields from your variable to the destination columns

check this discussion it will be helpful 

https://community.fabric.microsoft.com/t5/Data-Pipeline/Writing-Pipeline-Output-to-JSON-file-in-Lake...

If this helps. Please accept the solution 

Thanks

[Darwood]

ok so I'm almost there....

I have created a dummy query with additional columns and dynamic data and I can get the id value for the first record using a value of

@variables('devicesJson')[0].id

However I want to bring across the values for every single record in the output. I can obviously change the 0 to a different number to get a particular record but I want them all. How would I do this please?

[Darwood]

Ignore that last message. I didn't realise the output wrapped the result in a JSON object with a "response" property. I have updated the variable expression to 

@json(activity('Devices').output.response) and the array variable seems to be correctly populated now. Still struggling to get the Additional Columns working in the Copy Data activity though...

[Darwood]

Thanks for the suggestion @nilendraFabric 

I am getting a data type error on the Set variable activity though when I try and run the Data Pipeline. I have tried using a string or array data type and I get the same sort of problem.

The settings for the Set variable activity are:

• Variable type: Pipeline variable
• Name: devicesJson
• Value: @activity('Devices').output (this is the name of the previous Web activity API call)

"The variable 'devicesJson' of type 'Array' cannot be initialized or updated with value of type 'Object'. The variable 'devicesJson' only supports values of types 'Array'."