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 - a standardised Serilog implementation with extra goodies!

Logging is important Logging is a really important, oft-neglected, aspect of business applications. I can't state that enough. If you don't have good logging, you can't troubleshoot and debug problems, and you have little chance of seeing what's actually going on in your enterprise. In Structured Logging with Seq and Serilog,...

Performing OpsGenie Heartbeats with Seq

When we investigated OpsGenie, one feature I was attracted to was Heartbeat Monitoring. This is a feature that can help to answer a fundamental problem - "How do you know if you have a major site or infrastructure outage?" There are plenty of ways that you could go about this,...

Structured Logging with Seq and Serilog

A few years back, I picked up an old "unloved" business application for document handling, and brought it into the modern era. I completed some work on adding automated OCR, running as a service, and then started to enhance it well beyond its original capabilities, such as moving a manual...