In the current releases of Deployment Server DS6.9SP6 and Ghost Solution Suite 3.0, caution should be used in the deployment of remote consoles to avoid backend SQL Server exhaustion. I'll show today that the major metric for sizing the CPU of your SQL Server is the number of remote consoles you intend to deploy.
To highlight this point, in our environment where we manage a little over 3000 nodes, we see a typical SQL Server CPU overhead of between 250MHz-400MHz per remote console. As this overhead does not diminish for unattended consoles, we need to be creative in the management of the resulting SQL load.
This article demonstrates one way of achieving this using a Windows Terminal Server to contain the remote consoles and a vbscript to close any that are idle. We've found that this decimates our SQL Server load, and we estimate that this would allow the SQL Server to service at least 5 times more remote consoles.
If you just want the final script without the preamble, just dive down to the bottom of the article!
One of the caveats that Altiris administrators need to understand when deploying remote consoles is the price to be paid on the backend SQL Server. Each console elevates the backend SQL server load, and the more consoles are open, the bigger that load will be. Nothing too surprising there right? But here is the real clincher -the server load is not eased by console inactivity -idle consoles consume nearly as much backend resource as active ones.
It's therefore not hard to see that if console deployment is not managed well, a risk emerges on the backend SQL Server (and therefore any service that relies on it). We found in our environment that even if just a few techs left their DS Consoles open, the SQL Server's dominant activity is one of servicing these consoles rather than the execution of scheduled jobs. A tipping point was eventually reached for us in January 2014 with the arrival of some new starters. So many consoles were left open that we were effectively in a Denial of Service scenario -the SQL Server ran out of resources and deployments started to fail. The Deployment Server was compromised.
To resolve this, our first thought was to look to the product for an internal mechanism to throttle remote consoles. However, Altiris Deployment Server 6.9 (and indeed GSS3.0) did not possess mechanisms to force consoles into a true low-cpu consuming idle state. A couple of options do exist to reduce the refresh, but these drop the overall console experience as well as still leaving a significant SQL load even when maximised. As our deployment service is critical, we had to come up with a solution fast to stop this happening again. What we did was two things,
Once these changes were implemented, we noticed with some surprise that the average number of open consoles plummeted from 9 to 2. It seems that although the techs were just dipping into the consoles perhaps once or twice a day, they had simply adopted a bad habit of never closing them. The SQL Server since implementing the console killer became very, very happy.We also noticed that the odd glitches we sometimes experienced in our deployments all but vanished.
To help demonstrate the SQL loading issue, below is a CPU profile taken from a backend SQL Server where no idle console killing present. The first 60 second CPU profile is demonstrates the CPU load with no console open, and the second CPU profile shows the load with 9 consoles open,
.
This trace is for an environment with a mature DS6.9 installation of 3000 nodes. Whilst the above CPU profile doesn't look bad, it is important here to notice the scale. This SQL server is relatively high-spec with a total of 12.6GHz processing capacity, and remote idle consoles are actually consuming well over 3GHz of CPU on the server. So, each console here is consuming approximately 400MHz each and they are ALL idle.
From looking at many traces like this over the last 18 months, I now assume for initial sizing purposes that each open console will load the backend SQL Server somewhere between 250MHz-400MHz. This hopefully demonstrates the caution that should be applied when deploying remote consoles, and the importance of implementing something akin to a console killer to ease load on the SQL server.
The story should have ended here, and it really should have. But then Microsoft had done something very bad. And we didn't even know it.
When Microsoft releases a new server OS, the general expectation is one of feature improvement muddled with some random increased 'click-complexity' when executing our common day-to-day tasks. Generally, once we've worked this through and twisted our process around the OS redesign, all is well and life goes on.
Sometimes however, we find that the OS mashup cycle had abandoned to the 'trash bin of history' a rather useful feature. And this is what happened when Microsoft silently dropped screensaver support in Terminal Server 2008.
I can't find an official comment from Microsoft on this, so at this point I can only point to rumor. The consensus seems to be Microsoft decided that screensavers simply took too much resource on a terminal server and thus they simply decided not to implement this broken feature from 2008 onwards. The objective here I am assuming was to protect customers from themselves. Many Windows administrators however took the view that as screensavers had a variety of uses, any resulting resource consumption should be their issue to solve should they meet it. If you Google this issue today, you'll still find the internet littered with admins failing to shoe horn back in this functionality.
The final nail in the coffin for Terminal Server screensavers was sadly the release of the 2012 incarnation of Terminal Services, Windows 2012 RDS (Remote Desktop Services). Here the screensaver settings continue to remain unhonoured without official comment from Microsoft.
This screensaver 'console killing' concept had worked brilliantly for us since 2014. However, with Windows 2003 server being EOL in July this year, we had find and alternate means to reduce the the remote console SQL overhead. Now, this wasn't absolutely critical -our new SQL Server was sized to cope. However, some means to manage the load was certainly desirable -a habitually high CPU would certainly flip alarms with the virtual infrastructure administrator and might also herald the return of our old deployment niggles.
So we looked into seeing what we could so to trigger on idle user actions in our Windows 2012 RDS. This turned out to be something that Google said was really hard to do. And when Google says that, a little bit of your IT soul dies.
As I saw it, we had 4 options,
- Keep the 2003 Terminal Server running
- Migrate to Windows 2012 RDS, Kill all sessions after 30 minutes idle.
- Migrate to Windows 2012 RDS and de-virtualize the SQL server
- Find another programatical route to kill idle DS Consoles
And here's the nice table that flashed into my head as I considered them,
The first option is worst-case scenario here in terms of overall work, which is odd when it looks like it's the 'Do Nothing' approach. Problem is the paperwork. The auditing and mitigation required to keep a Windows 2003 terminal server server in production use beyond July 14th will be enormous. I just didn't want to go there.
The second option would be very rough for the techs -reaping entire sessions on idle. Draconianly killing off whole sessions just on the off-chance they might have left a DS6.9/GSS3 Console open is going to be rather unpopular. It's a last resort, "with apologies" tactic.
The third option although expensive is potentially doable, but not quickly. This option upgrades the terminal server and moves the SQL Server to physical hardware. The tricksy part here though is getting sign-off for the act of moving an existing virtualised server into the physical world at a time when the flow is strongly the other way.
The last option therefore looked tempting -just keep looking for a way to program away the issue. So I gave myself a time limit of three days to come up with another route to kill off those idle consoles.
Without a screensaver to identify an idle user, we needed something else. Asking around the office reveals that Windows has a few commands which are useful in providing summaries on logged in user sessions. A couple of these are,
- QUser
Used to display information about the users logged into the system
- Query Session
Used to display information about remote desktop sessions (also aliased to command QWInsta)
I was actually new to these commands, and it turns out the output of these two applications is very similar, but the big difference is that QUser provides an actual idle time for each session. Below is an example of some output,
So here we can see that we've got two active sessions, one of which (the test1 account) has been idle for 43 minutes. Having established that the test1 account has been idle for more than 30 minutes, the next step would be to run up taskmgr and kill any console processes (eXpress.exe) running in this context.
So, all that's left it to put that to a script.
In order to script the whole process of identifying an idle user and terminating any consoles they might have running, the first step is to analyse the output of QUser.
Programatically, the output can by executing QUser and redirecting standard output to a file. This file can then be read, and parsed for the data columns of interest. In my vbscript, running QUser and gathering the output is simple,
Set objShell = WScript.CreateObject("WScript.Shell")
StrTemp=objShell.ExpandEnvironmentStrings( "%TEMP%" )
StrCmd="cmd /c QUSER >" & StrTemp & "\quser.txt"
intReturn = objShell.Run(StrCmd,0)
StrFile=GetFile(StrTemp & "\quser.txt")
function GetFile(FileName)
If FileName<>"" Then
Dim FS, FileStream
Set FS = CreateObject("Scripting.FileSystemObject")
on error resume next
Set FileStream = FS.OpenTextFile(FileName)
GetFile = FileStream.ReadAll
End If
End Function
where the GetFile function is a custom function I used to read in the contents of a file.
Next, the script needs to extract usernames from the first column, and the idle times from the fith column of every line in the output. My favorite way to do this is with regular expressions which are simply the bees knees when it comes to string matching.
With regular expressions, you can examine a string and search for a pattern of content. The pattern declaration you create is formed of shorthand aliases which serve to match on the type of content you are looking for (from arrangements of specific text, to white space, non-white space, special characters and numbers). If you are new to regular expressions, I highly recommend you take a look at this tutorial, http://www.regular-expressions.info/tutorial.html
Creating regular expressions however is an art -the shorthand aliases look daunting and typos are hard to avoid. For this reason I rely heavily on gskinner's online regular expression tool at http://www.regexr.com/. From here you can insert the text you are intending to pattern match on, and craft you regular expression, observing the pattern match results in real time.
So, after a few false starts, this was the result,
Which gave my regular expressions as,
^\s*>?(\S+)\s+\S*\s+[0-9]+\s+\S+\s+(\S+)\s+
Now, what I've done is is create a regular expression using the following pattern aliases,
| Pattern Alias |
Meaning |
| ^ |
This matches on the beginning of a line |
| \s |
matches on whitespace |
| \S |
matches on non-whitespace |
| * |
matches any number of the preceding item (including none) |
| + |
matches on one or more of the preceding item |
| ? |
means the preceding item may or many not exist |
| ( ) |
Any pattern aliases enclosed by round brackets represents a group which we intend to extract later as our data |
| [0-9] |
The square brackets represent a range match. This is any number between 0 and 9 |
In essence, what this regular expression does is extract, as two groups, the first and fith columns from any line that pass the match test. This will gives us our user name, and our idle time.
To use this regular expression in my vbscript, To grab the first and fith columns from a string (represented by the first and second regex group) the following vbscript extract suffices,
sQueryExpression="^\s*>?(\S+)\s+\S*\s+[0-9]+\s+\S+\s+(\S+)\s+"
StrUser=ExpressionExtractGroupOne(StrCmdOutput,sQueryExpression)
StrIdle=ExpressionExtractGroupTwo(StrCmdOutput,sQueryExpression)
Function ExpressionExtractGroupOne(StrIn,StrExpr)
ExpressionExtractGroupOne ="?"
Set RegExp = New RegExp
RegExp.IgnoreCase = True
RegExp.Global = false
RegExp.Multiline = false
RegExp.Pattern=StrExpr
If RegExp.Test(StrIn) Then
Set myMatches = RegExp.Execute(StrIn)
set myMatch = myMatches(0)
ExpressionExtractGroupOne = mymatch.submatches(0)
End if
End Function
Function ExpressionExtractGroupTwo(StrIn,StrExpr)
ExpressionExtractGroupTwo="?"
Set RegExp = New RegExp
RegExp.IgnoreCase = True
RegExp.Global = false
RegExp.Multiline = false
RegExp.Pattern=StrExpr
If RegExp.Test(StrIn) Then
Set myMatches = RegExp.Execute(StrIn)
set myMatch = myMatches(0)
ExpressionExtractGroupTwo = mymatch.submatches(1)
End if
End Function
From here we just need to do some magic to convert the horribly formatted idle string into an integer number of minutes,
Function ConvertIdle(StrIn)
'The Microsoft QUser output has a horribly formatted idle time string
'which is <days>+<hours>:<mins>
'
'This function converts this string into an integer number of minutes.
'
ConvertIdle=0
If lcase(StrIn)="none" then exit function
If lcase(StrIn)="." then exit function
if instr(StrIn,"+") then
ConvertIdle=ConvertIdle + 24*60*Cint(left(StrIn,instr(StrIn,"+")-1))
StrIn=mid(StrIn,instr(StrIn,"+")+1,len(StrIn))
end if
if instr(StrIn,":") then
ConvertIdle=ConvertIdle + 60*Cint(left(StrIn,instr(StrIn,":")-1))
StrIn=mid(StrIn,instr(StrIn,":")+1,len(StrIn))
end if
ConvertIdle=ConvertIdle +Cint(StrIn)
End Function
After which, we can finally, terminate the process if our max idle minutes has been exceeded,
strProcess = "eXpress.exe"
strCommand = "cmd /c taskkill /f /fi ""USERNAME eq " & StrUser & """" & " /im " & StrProcess
intReturn = objShell.Run(StrCommand,0)
And anyone who's messed with quotes in command strings will know exactly how long it took to get that second line right.
Below is the full script I now use to kill idle console sessions. This is fired off every 10 minutes using a Windows scheduled task and helps us massively to keep on top of this idle console problem. For tracking, it will log all kill attempts to a log file in the same folder as the script, as well as providing the details of the idle session.
'#####################################################################################
'# RDS_Idle_Process_Killer.vbs
'# Author: Ian.Atkin@it.ox.ac.uk
'# Version: 1.0
'#
'# Purpose: Kills process defined by StrProcess for terminal services sessions
'# which have been recorded as idel for longer that IntIdleLimit
'#
'# Designed to kill off Altiris DS6.9/GSS3 Console Windows which consume significant
'# backend CPU even when user is idle.
'#####################################################################################
'_____________________________________________________
' Declarations
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Dim IntIdleLimit,StrProcess
Dim StrUser, StrIdle, IntIdle
Dim objShell,ofso,sLogFile,sQueryExpression,StrCmdOutput
StrProcess="express.exe"
IntIdleLimit=30
'_____________________________________________________
' Set name of output file
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Set objShell = WScript.CreateObject("WScript.Shell")
set ofso=CreateObject("Scripting.FileSystemObject")
sLogFile=ofso.GetParentFolderName(Wscript.ScriptFullName) & "\RDS_Idle_Process_Killer.log"
'_____________________________________________________
' Get output from qUser
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
sQueryExpression="^\s*>?(\S+)\s+\S*\s+[0-9]+\s+\S+\s+(\S+)\s+"
StrCmdOutput=""
StrTemp=objShell.ExpandEnvironmentStrings( "%TEMP%" )
StrCmd="cmd /c QUSER >" & StrTemp & "\quser.txt"
intReturn = objShell.Run(StrCmd,0)
StrFile=GetFile(StrTemp & "\quser.txt")
'Call AppendTxtToFile(StrFile,SLogFile)
m=1
Do While m<Len(StrFile)
StrCmdOutput = GrabLine(m,StrFile)
'Call AppendTxtToFile("-->" & StrCmdOutput,sLogFile)
StrUser=ExpressionExtractGroupOne(StrCmdOutput,sQueryExpression)
StrIdle=ExpressionExtractGroupTwo(StrCmdOutput,sQueryExpression)
'Call AppendTxtToFile("--[" & StrUser & "," & StrIdle & "]",sLogFile)
if StrUser<>"?" and StrIdle<>"?" then
IntIdle=ConvertIdle(StrIdle)
If IntIdle>IntIdleLimit then
if IsUserProcessRunning(StrProcess,StrUser) then
Call AppendTxtToFile(StrCmdOutput,sLogFile)
Call AppendTxtToFile(StrUser & " idle time of " & IntIdle & "mins exceeds limit of " & intIdleLimit & "mins. Killing active process " & StrProcess,sLogFile)
strCommand = "cmd /c taskkill /f /fi ""USERNAME eq " & StrUser & """" & " /im " & StrProcess
intReturn = objShell.Run(StrCommand,0)
end if
end if
end if
m=instr(m,StrFile,vbcrlf) + 2
Loop
'#############################################################
Function IsUserProcessRunning(sProcess, sUsername)
IsUserProcessRunning = 0
strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery("select * from win32_process where name='" & sProcess & "'")
For Each objprocess In colProcessList
colProperties = objprocess.GetOwner(strNameOfUser, strUserDomain)
If strNameOfUser = sUsername Then
IsUserProcessRunning = 1
End If
Next
End Function
Function ConvertIdle(StrIn)
'The Microsoft QUser output has a horribly formatted idle time string
'which is <days>+<hours>:<mins>
'
'This function converts this string into an integer number of minutes.
'
ConvertIdle=0
If lcase(StrIn)="none" then exit function
If lcase(StrIn)="." then exit function
if instr(StrIn,"+") then
ConvertIdle=ConvertIdle + 24*60*Cint(left(StrIn,instr(StrIn,"+")-1))
StrIn=mid(StrIn,instr(StrIn,"+")+1,len(StrIn))
end if
if instr(StrIn,":") then
ConvertIdle=ConvertIdle + 60*Cint(left(StrIn,instr(StrIn,":")-1))
StrIn=mid(StrIn,instr(StrIn,":")+1,len(StrIn))
end if
ConvertIdle=ConvertIdle +Cint(StrIn)
End Function
Function ExpressionExtractGroupOne(StrIn,StrExpr)
ExpressionExtractGroupOne ="?"
Set RegExp = New RegExp
RegExp.IgnoreCase = True
RegExp.Global = false
RegExp.Multiline = false
RegExp.Pattern=StrExpr
If RegExp.Test(StrIn) Then
Set myMatches = RegExp.Execute(StrIn)
set myMatch = myMatches(0)
ExpressionExtractGroupOne = mymatch.submatches(0)
End if
End Function
Function ExpressionExtractGroupTwo(StrIn,StrExpr)
ExpressionExtractGroupTwo="?"
Set RegExp = New RegExp
RegExp.IgnoreCase = True
RegExp.Global = false
RegExp.Multiline = false
RegExp.Pattern=StrExpr
If RegExp.Test(StrIn) Then
Set myMatches = RegExp.Execute(StrIn)
set myMatch = myMatches(0)
ExpressionExtractGroupTwo = mymatch.submatches(1)
End if
End Function
Sub AppendTxtToFile(sTxt,sFilename)
Dim sFile, fso, ts
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(sFilename, 8, True)
ts.WriteLine NOW() & " " & sTxt
ts.close
Set ts = Nothing
Set fso = Nothing
End Sub
function GetFile(FileName)
If FileName<>"" Then
Dim FS, FileStream
Set FS = CreateObject("Scripting.FileSystemObject")
on error resume next
Set FileStream = FS.OpenTextFile(FileName)
GetFile = FileStream.ReadAll
if Err.Number<>0 then
'msgbox "Can't read File"
wscript.quit ERR_FILEREAD
End If
End If
End Function
Function GrabLine(index,InputStr)
'This function returns as a string all characters leading up to the line break
'from the string index provided
myIndex=Instr(index,InputStr,vbcrlf)
GrabLine=Mid(InputStr,index,myIndex-index)
End Function
The ideal solution to this problem is for DS6.9/GSS3 to have an globally honoured idle console setting. Something which makes it clear that consoles will be forced into an low-cpu state when unused,
And after a console has been decided it's inactive, it could present a screen like this to make that clear,
If something like this were implemented, administrators need save capital when it comes to their SQL server hardware and deploy remote consoles with much more flexibiltiy.