Connectors

Reporting Database Connector

Overview

This article shows how to move your telemetry data from IoTConnect into Azure SQL Database by using a live data telemetry streaming connector.
Reporting database connector moves your telemetry data into creating rows in a database table.

Configure Connector in IoTConnect

Login into IoTConnect and go to the left menu -> Configuration -> live data streaming section and enable the reporting database, which opens below popup for you to configure the connector.

isAutoScriptExecute
Value 0 – if you want to execute database schema, table, and procedure manually and give only Insert record rights to database users
Value 1 – Platform will take care of initial setup and execute the script on your SQL server database

  • If the value is 0 then, This database user must have access of execute procedures & insert records in the table. Also, you must run the script manually which can generate Schema, Table, Procedure. To download SQL script file from the porta by click on icon, get the file from the given link on popup.
  • If value is 1 then, This database user must have following access
    • Create schema. [It will create schema named “IoTConnect”]
    • Create table & insert records. [It will create table named “IoTConnect.AttributeValue”]
    • Create & execute procedure. [It will create procedure named “IoTConnect.TelemetryAttributeValue_Bulkadd”]

Connection String

  • Data source – Domain/Ip Address of your SQL server
  • Port – access open port for SQL Server, 1433 is the default.
  • initialCatalog – the name of the database that you create on your SQL server
  • Userid – Name of database user
  • Password – password for database user
  • Encrypt –0 or 1 (Setting the encrypt property to 1 will cause the transmission of data to and from the SQL server to be encrypted.)

Once you are done with all the above fields which start exporting data from IoTConnect to SQL Server.

Details description of table columns

  • Guid –Table primary Key
  • companyGuid – Company GUID
  • localName – Device attributes name
  • uniqueId – Device uniqueID
  • tag – This column is used when you have gateway type device data
  • attributeValue – Value of attributes
  • createdDate – Timestemp of platform send data to SQL server
  • sdkUpdatedDate – Timestemp of device send data to platform
  • gatewayUpdatedDate – Timestemp of gateway device send data to platform
  • deviceUpdatedDate – Timestemp of device/child Device send data to platform

Not advisable to turn this feature on if per day message is more then 5K. This will increase the data loss and SQL Deadlock on target SQL Server.

Related Articles