Creating the query template

As the first step, the query template needs to be created and the custom endpoint configured.

Proceed as follows

1. Open the Explore > Data Explorer tab.

2. Click the Add Query Template button to create a new query template.
   → The Template Designer is displayed.

3. In the Name field, enter a name for the query template.

4. Click the Access rights button to define the access rights for the desired user roles. Refer to the Managing query template access rights chapter.

5. In the Parameters pane, click the Add parameter button to add the first parameter.
   → The Edit Parameter dialog is displayed.

   1. In the Technical Name field, enter engine_id.

   2. Activate the Provide Choices checkbox to add the engine IDs and their labels.

   3. Click the Add choice button to add a row for each engine.

   4. Enter a Label and the engine IDs.

   5. In the engine_id field below the Define Default Value checkbox, select the engine that should be set as default engine.

   6. Click the Save button.

6. Click the Add parameter button to add the second parameter.
   → The Edit Parameter dialog is displayed.

   1. In the Technical Name field, enter limit.

   2. In the Data type drop-down list, select INT.

   3. Optionally, activate the Define Default Value checkbox and enter any number for the limit.

   4. Click the Save button.

7. In the Custom Endpoint pane, activate the Enable custom endpoint switch to execute the query template by calling the customized URL.

   1. In the Path field, enter any unique string and the technical names of the parameters in curly brackets separated by slashes, e.g.
      https://bosch-iot-insights.com/r/<projectName>/latest-data/{engine_id}/{limit}

8. In the Template Definition pane, enter the following query.

   [

   {

   "$match": {

   "payload.id" : ${engine_id}

   }

   },

   {

   $sort: {

   "payload.measurement_date": -1

   }

   },

   { "$limit" : ${limit}

   },

   {

   $sort: {

   "payload.measurement_date": 1

   }

   }

   ]

9. Click the Create New Query Template button.
   → The template has been created.

10. Test-run the query template with any engine by clicking the Run button to check whether the query template is working correctly.

The query template for this tutorial has been configured as follows:

Accessing the custom endpoint

To test the custom endpoint, the endpoint URL can be called with any web browser.

Proceed as follows

1. Copy the Insights Endpoint URL and paste it into the address bar.

2. Copy the Path and paste it into the address bar behind the Insights endpoint URL.

3. Replace the parameters {engine_id} and {limit} with actual values, e.g.
   https://bosch-iot-insights.com/r/<projectName>/latest-data/123/12
   → 12 entries for the engine with the engine ID 123 should be displayed.

Displaying the engine data in Microsoft Excel

In this step, we will load the engine data into Microsoft Excel and create a line chart that displays the temperature values of both sensors.

Proceed as follows

1. Open Microsoft Excel.

2. Open the Data tab.

3. Click the From Web button.
   → The From Web dialog is displayed.

4. Select the Advanced radio button.

5. In the URL parts field, enter the URL you just pasted into the address bar of your web browser.

6. In the HTTP request header parameters (optional) pane, add the following headers:

   • Accept → text/csv

   • Authorization → Basic <your base64-encoded API user credentials>

     For the creation and role adjustment of an API user, refer to Creating an API user.

7. Click the OK button to save your input and load the data.
   → A dialog is displayed that shows the data to be loaded into the sheet.

8. Click the Load button.
   → The data is loaded into the sheet.

To create a chart from the data we loaded from Bosch IoT Insights, refer to the Microsoft Excel documentation.

The From Web dialog in Microsoft Excel is provided with the following input:

Refreshing the engine data in Microsoft Excel

If new data is constantly coming in, you can refresh the data in Microsoft Excel.

Proceed as follows

1. Open the Data tab.

2. Click the Refresh All button.
   → The data is updated.