Troubleshooting: How to get a log of Stream Host connectivity

A number of network or infrastructure configuration can impact Stream HostIs a client application which hosts/executes the Use Cases designed by the user in the IoT Portal. connectivity. In order to analyze the connectivity, one may require to log StreamConsists of a combination of Stream Objects allowing real-time data to flow through. It is created to address a particular use case. Host connections. This is possible using the following SQL Queries on Data Stream DesignerIs a model-driven approach to connect, transform and action real-time “streaming” data in a visual, drag and drop manner. database.

Please do note that this can only be run by the administrator or a user with sufficient infrastructure level access.

CREATE TABLE [dbo].[DeviceConnectionLog](
       [ConnectionId] [nvarchar](64) NULL,
       [Id] [uniqueidentifier] NULL,
       [Name] [nvarchar](512) NULL,
       [EdgeContainerId] [uniqueidentifier] NULL,
       [Timestamp] [bigint] NULL,
       [Rank] [int] NULL,
       [Connected] [datetime] NULL,
       [Disconnected] [datetime] NULL
) ON [PRIMARY]

CREATE TRIGGER [dbo].[LogDeviceConnections]
   ON  [dbo].[Device]
   AFTER INSERT, DELETE
AS
BEGIN
       SET NOCOUNT ON;
       INSERT INTO [DeviceConnectionLog]([ConnectionId]
           ,[Id]
           ,[Name]
           ,[EdgeContainerId]
           ,[Timestamp]
           ,[Rank]
           ,[Connected])
    SELECT *, getdate()
    FROM inserted

       Update [DeviceConnectionLog]
       set [Disconnected] = getdate()
       WHERE [ConnectionId] in (SELECT [ConnectionId] from deleted)
END

SELECT TOP 5 [ConnectionId]
      ,[Id]
      ,[Name]
      ,[Connected]
      ,[Disconnected]
         ,DATEDIFF(hour, [Connected], [Disconnected]) as [Online time (h)]
  FROM [XMIoT].[dbo].[DeviceConnectionLog]
 ORDER BY [Connected] desc