Logging Remote Desktop Session Host logins and disconnects with Seq.Input.MSSQL!

I've already written up a couple of posts about using Seq.Input.MSSQL, but I wanted to share one more. If you use termservers (Remote Desktop Session Host) in your environment, integrated with a Remote Desktop Connection Broker, you can do something pretty funky - logging new logins and disconnects to Seq!

This, of course, needs a view for connections, including calculations to turn database fields into valid timestamps ...

USE [RDConnectionBroker]
GO

/****** Object: View [dbo].[TermServerConnects] Script Date: 21/10/2021 8:48:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[TermServerConnects] AS SELECT @@servername as ServerName
,target.Name AS TermServer
,target.Fqdn AS TermServerFqdn
,target.Netbios AS TermServerNetBios
,targetip.IpAddress AS TermServerIp
,pool.Alias AS Pool
,pool.DisplayName AS PoolDisplayName
,[UserId]
,[UserName]
,[UserDomain]
,[SessionId]
,dateadd(hh, datediff(hh, getutcdate(), getdate()), DATEADD(nanosecond,CreateTime % 600000000,
DATEADD(minute,CreateTime / 600000000, cast('16010101' as datetime2(7))))) AS CreateTime
,CASE WHEN [DisconnectTime] = 0 THEN NULL Else dateadd(hh, datediff(hh, getutcdate(), getdate()), DATEADD(nanosecond,DisconnectTime % 600000000,
DATEADD(minute,DisconnectTime / 600000000, cast('16010101' as datetime2(7))))) END AS DisconnectTime
,[InitialProgram]
,[ProtocolType]
,session.State
,[ResolutionWidth]
,[ResolutionHeight]
,[ColorDepth]
,'{UserDomain}\{UserName} connected to {TermServer}' AS Message
FROM [RDConnectionBroker].[rds].[Session] session
LEFT JOIN [RDConnectionBroker].[rds].[Target] target ON session.TargetId = target.Id
LEFT JOIN [RDConnectionBroker].[rds].[TargetIp] targetip ON session.TargetId = targetip.TargetId
LEFT JOIN [RDConnectionBroker].[rds].[Pool] pool ON target.PoolId = pool.Id
WHERE Session.State <> 4
ORDER BY CreateTime DESC OFFSET 0 ROWS

GO

and a view for disconnections.

USE [RDConnectionBroker]
GO

/****** Object:  View [dbo].[TermServerDisconnects]    Script Date: 21/10/2021 9:14:05 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[TermServerDisconnects] AS SELECT @@servername as ServerName
  ,target.Name AS TermServer
  ,target.Fqdn AS TermServerFqdn
  ,target.Netbios AS TermServerNetBios
  ,targetip.IpAddress AS TermServerIp
  ,pool.Alias AS Pool
  ,pool.DisplayName AS PoolDisplayName
      ,[UserId]
      ,[UserName]
      ,[UserDomain]
      ,[SessionId]
      ,dateadd(hh, datediff(hh, getutcdate(), getdate()), DATEADD(nanosecond,CreateTime % 600000000,
 DATEADD(minute,CreateTime / 600000000, cast('16010101' as datetime2(7))))) AS CreateTime
  ,dateadd(hh, datediff(hh, getutcdate(), getdate()), DATEADD(nanosecond,DisconnectTime % 600000000,
 DATEADD(minute,DisconnectTime / 600000000, cast('16010101' as datetime2(7))))) AS DisconnectTime
       ,[InitialProgram]
      ,[ProtocolType]
      ,session.State
      ,[ResolutionWidth]
      ,[ResolutionHeight]
      ,[ColorDepth]
  ,'{UserDomain}\{UserName} disconnected from {TermServer}' AS Message
  FROM [RDConnectionBroker].[rds].[Session] session
  LEFT JOIN [RDConnectionBroker].[rds].[Target] target ON session.TargetId = target.Id
  LEFT JOIN [RDConnectionBroker].[rds].[TargetIp] targetip ON session.TargetId = targetip.TargetId
  LEFT JOIN [RDConnectionBroker].[rds].[Pool] pool ON target.PoolId = pool.Id
  WHERE session.State = 4
  ORDER BY DisconnectTime DESC OFFSET 0 ROWS

GO

And then all that's required is a Seq.Input.MSSQL instance to read in each view, eg.

And an equivalent app for TermServerDisconnects. We need 2 instances, because we want different messages for connects and disconnects. This will give you essentially all the useful structured properties related to each connection - sadly, not the client IP which I'd have liked, but enough to be able to build fancy dashboards and alerts. We actually use this to detect a mass user disconnection, which would indicate a networking issue.

All you need to expose your new logs is a signal or two, which could be as simple as a signal looking for "Application = 'TermServerConnects' OR Application = 'TermServerDisconnects'". 

It's worth noting here that the Remote Desktop Connection Broker has a quirk - it only logs sessions to the database once per minute, so this is where the "Seconds Delay" setting comes in for Seq.Input.MSSQL. By setting both "Refresh every X seconds" and "Seconds Delay" to 60, we allow time for new connections and disconnections to be logged in their respective input. 

Seconds Delay started as an inbuilt 1 second delay to address an issue with timestamps that didn't measure in milliseconds (the Job Agent logs from my last post), but I made it configurable for usages like this. I recently updated Seq.Input.MSSQL to reflect a longer maximum delay (up to 24 hours!) because, as it turns out, there's scenarios where a 1 hour delay or more might be needed to ensure that new logs are picked up - apps "lying" about the timestamp they're inserting to a database well after that time. 

The kind of power that we've built into Seq.Input.MSSQL is awesome, and this is just one simple example of usage. I've only barely scraped the surface of its capabilities - mapping properties to database values that can feed into alerting apps is something that's usage dependent, but amazing when you can do it!

We recently added the ability to specify SQL connect timeout, query timeout, and even set encrypted connections. Check it out in your own Seq instance!