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.

PropertyValue
TitleTermServerConnects
Refresh every x seconds60
Server instance nameSERVERNAME
Initial catalogRDConnectionBroker
Trusted ConnectionEnabled
Table or view nameTermServerConnects
Column name of TimeStampCreateTime
Seconds delay60
Column name of MessageMessage
Include following columns as property

ServerName,TermServer,TermServerFqdn,TermServerNetBios,TermServerIp,

Pool,PoolName,UserId,UserName,UserDomain,SessionId,CreateTime,DisconnectTime,

InitialProgram,ProtocolType,State,ResolutionWidth,ResolutionHeight,ColorDepth,Message

Log application name as propertyTermServerConnects
Event Level2
Valid local time period 

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!

 

Comments

You may also like:

Lurgle.Logging v1.1.14 and Lurgle.Alerting v1.1.9 Released

I've pushed out updates to Lurgle.Logging and Lurgle.Alerting today. The Lurgle.Logging update is minor - I noticed that Log.Add wasn't correctly passing the calling method, source file, and line number. Lurgle.Alerting has received a more substantial update: This helps to make Lurgle.Alerting even more useful and reliable! You can get...

Lurgle.Alerting v1.1.10 and Lurgle.Logging v1.1.15 Released

I've just pushed out an update to Lurgle.Alerting on Nuget. This release adds a Handlebars template option, based on the implementation by Matthew Turner at FluentEmail.Handlebars (github.com). When I came across the FluentEmail.Handlebars package, I was keen to use it, but it was only compiled against .NET Standard 2.1, and...

Lurgle.Alerting - a standardised FluentEmail implementation with extra goodies!

Another Lurgle Around the time that I tackled my original Serilog logging implementation, I also looked at our email alerting. Emails can be used for a variety of reasons, and it's not uncommon that they are sent as a simple string that concatenates or formats variables. In this scenario, the...