Troubleshooting: How to get a log of Stream Host connectivity

How Can We Help?

< Back

A number of network or infrastructure configuration can impact Stream Host connectivity. In order to analyze the connectivity, one may require to log Stream Host connections. This is possible using the following SQL Queries on Data Stream Designer 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

 

Comments are closed.

This is the legacy version of the XMPro Documentation site. For the latest XMPro documentation, please visit documentation.xmpro.com

X