Storage Access with Logic Apps and SQL Server

These instructions show how to use Logic Apps to call the IoT Ensemble warm query every 12 hours and insert the data into a SQL Server database. Before we get started, here's an overview of the entire Logic App with the steps collapsed.

Step 1: HTTP

For the first step of the Logic App, search for 'HTTP'.

Configure the HTTP trigger with the following settings from your IoT Ensemble account. This example is using the Warm Query.

• Method: GET
• URI: https://fathym-cloud-prd.azure-api.net/fcp-iotensemble/WarmQuery?includeEmulated=true
• Header: lcu-subscription-key: f24047c3d5b14f****
• How often do you want to check for items?: 12 Hour

note

Use your primaryKey or secondaryKey from the IoT Ensemble API Keys for the lcu-subscription-key value. For example:

lcu-subscription-key: f24047c3d5b14f****

info

In the HTTP trigger above, use the 'How often do you want to check for items' fields to control how often the HTTP trigger calls the IoT Ensemble Warm Query for new data.

Step 2: Compose

For the next step of the Logic App, search for 'Compose'.

For the Compose step we need to list an example JSON payload the Logic App will receive from the HTTP trigger. For the IoT Ensemble Warm Query, you can use the following:

{
  "Payloads": [
    {
      "DeviceData": {
        "Floor": 1,
        "Latitude": 40.269,
        "Longitude": -104.5808,
        "Room": 4
      },
      "DeviceID": "Emulated-3",
      "DeviceType": "Generic",
      "EnterpriseLookup": "EMULATED",
      "EventEnqueuedUtcTime": "2021-02-11T23:55:29.995Z",
      "EventProcessedUtcTime": "2021-02-11T23:55:30.227905Z",
      "PartitionId": 29,
      "SensorMetadata": {
        "Temperature": {
          "Battery": 0.3
        },
        "_": {
          "SignalStrength": 3
        }
      },
      "SensorReadings": {
        "Humidity": 9,
        "Occupancy": 2,
        "Occupied": 1,
        "Temperature": 117
      },
      "Timestamp": "2021-02-11T23:55:29.9963929+00:00",
      "Version": "0.0.2",
      "_attachments": "attachments/",
      "_etag": "\"09009632-0000-0700-0000-6025c3f50000\"",
      "_rid": "8qpRAK49o3m60BAAAAAAAA==",
      "_self": "dbs/8qpRAA==/colls/8qpRAK49o3k=/docs/8qpRAK49o3m60BAAAAAAAA==/",
      "_ts": 1613087733,
      "id": "1475d2e2-de67-6c8f-53c1-47699a3c40f2"
    },
    {
      "DeviceData": {
        "Floor": 2,
        "Latitude": 40.8559,
        "Longitude": -104.905,
        "Room": 5
      },
      "DeviceID": "Emulated-4",
      "DeviceType": "Generic",
      "EnterpriseLookup": "EMULATED",
      "EventEnqueuedUtcTime": "2021-02-11T23:55:29.995Z",
      "EventProcessedUtcTime": "2021-02-11T23:55:30.227905Z",
      "PartitionId": 29,
      "SensorMetadata": {
        "Temperature": {
          "Battery": 0.4
        },
        "_": {
          "SignalStrength": 2
        }
      },
      "SensorReadings": {
        "Humidity": 88,
        "Occupancy": 12,
        "Occupied": 1,
        "Temperature": 11
      },
      "Timestamp": "2021-02-11T23:55:29.9964182+00:00",
      "Version": "0.0.2",
      "_attachments": "attachments/",
      "_etag": "\"09009432-0000-0700-0000-6025c3f50000\"",
      "_rid": "8qpRAK49o3m50BAAAAAAAA==",
      "_self": "dbs/8qpRAA==/colls/8qpRAK49o3k=/docs/8qpRAK49o3m50BAAAAAAAA==/",
      "_ts": 1613087733,
      "id": "f583575c-3c40-9297-7c69-3add2a8b1c18"
    }
  ],
  "Status": {
    "Code": 0,
    "Message": "Success"
  },
  "TotalPayloads": 43065
}

Copy/paste the JSON payload above into the Compose input:

Step 3: Parse JSON

For the next step of the Logic App, search for 'Parse JSON'.

For the Parse JSON content, use the dynamic Outputs object from the previous Compose step. For the Schema, click the 'Use sample payload to generate schema' link and it will automatically generate the schema based on the Outputs object. Refer to the screenshot below:

Step 4: For Each

For the next step of the Logic App, search for 'For Each'. Select the 'Control' option.

Within the Controls, select the 'For each' action.

For the output, select the dynamic Payloads object from the previous Parse JSON step.

Step 5: Insert into SQL Server

Click the 'Add an Action' button to add an action to the For Each. Search for 'sql server' and select the 'SQL Server' option.

Within the SQL Server option, select 'Import row'.

After you authorize with the SQL Server, the form will allow you to select your server, database, and table. After those are selected, you can add Parameters for each of your data properties. The screenshot below is based on the IoT Ensemble emulated data.

Save and run the logic app to see it in action.

Overview of Steps

Here's an overview of the entire Logic App with the steps collapsed. The Logic App calls the IoT Ensemble warm query every 12 hours and inserts the data into a SQL Server database.