Inactivity Auto Logout for Dynamics GP
Updated 18th May 2014 – Amended to improve stability
October 2015 - New post Inactivity Auto Logout for Dynamics GP Part II GP2013R2 onwards adds extra considerations
Concurrent licences
Dynamics GP is licenced on a concurrent user model. Hitting max user count limit, yet having some of the user sessions idle for hours at the same time as the user is AFK (Away From Keyboard) is a problem for many installations. Asking politely for users to logout is not enough as users cannot always anticipate when they will be unexpectedly pulled away from their work, sometimes for extended times leaving GP logged in hogging a valuable licence.
A colleague thought one approach to address the issues may be to allow the users to click a button, see on the above screen his idea, in order to see the users that have been idle too long. Thus the users themselves get to do the leg work of phoning up those licence hogs to get them to logout. I have written this version, by hooking into the form that pops up for maximum user count. A better way to tackle this in my view is with an inactivity auto logout approach.
User licences are so expensive, so having them laying idle is not acceptable so I tried looking for an auto logout function in GP. Alas it is not there. Rockton Software have a GP Toolbox, which has a feature rich auto logout, go check it out. This looked to be part of a larger toolkit that I didn’t require. Thus I rolled up my sleeves and fired up Visual Studio and Google.
Using SQL
I was really only targeting the occasional users, not so much core users and didn’t want to force people out during dangerous activities like posting or editing records. I toyed with using a SQL script and SQL Job to kill off users from the activity table. I checked first to see if they had any locks on resources that would concern us though the locks tables, thus not affecting users editing or posting. Although this was OK, I wasn’t happy enough with the way it worked. I could end up clearing up the data corruption if it didn’t work! I then thought I could try to use a Visual Studio Addin to log these users out, rather than rudely killing them off behind the scenes. This could be done based on the same script to decide when it should happen.
See idle times from the second SQL script on this page:http://dynamicsgpblogster.blogspot.co.uk/2009/04/retrieving-dynamics-gp-user-idle-time.html
Using Visual Studio Addin
I set up a system.Timer and fired it regularly to check the SQL script and then used a method I found to log the users out. http://mohdaoud.blogspot.co.uk/2008/12/programmatically-closing-for-dynamics_3512.html
I had found a way to do the logout and it worked like a charm. It works by simulating the user going to file>>exit. It does this by writing out a GP macro to a temp file that is subsequently ran. It meant that GP retained control in exactly the same manner as if a user had attempted to exit, thus would say “do you want to save changes?” and prevent logout, should the user be in the middle of anything risky. This soft logout is quite adequate for what I was trying to achieve, shaving a few more users out the system with low risk of causing any issues.
The problem was then that when I tried testing I found that normal users can’t access the system tables I needed (SYSPROCESSES) without granting extra permissions (VIEW SERVER STATE). I didn’t want to grant this. So it had been fine running as a SQL job under an elevated permission set, but not as end users.
Hooking into Global Procedures
Thus I started looking at how I could detect a user actively doing it in GP. I went looking at putting an event handler in the client GP application. I hit upon the global security procedure that is checked when you do most anything in GP. By using this procedure to update a last touched date I could pretty much check for user activity in the GP client though the add-in, much better as self contained functionality.
Googling that security global procedure, I then stumbled on a post by David Musgrave about how they had hooked onto that and some other procedures too when making the Rockton tool for inactivity monitoring. http://msgroups.net/microsoft.public.greatplains/auto-logout-of-dynamics-gp/590960 If only I’d seen this at the start of my hunt. I shamelessly added those extra procedures on top of the security procedure and it improved the behaviour, now virtually never get a auto logout when it shouldn’t have happened.
I prompt the user for fifty seconds with a countdown windows form providing an opportunity to cancel the auto logout. I also call out to a SQL stored procedure before initiating the logout (not shown here for simplicity). That stored procedure checks if we are within x% of the max user limit, out of hours or one of a group of users who are excluded from the functionality due to the nature of the work they do (think checklinks maintenance user etc). Then decides if the auto logout should be initiated. There is no point logging people out unless we are running out of user licences, and everyone should logout at the end of day.
Source Code Snippets
I have been running this rough version now on my copy of GP for testing. It just needs the rough edges smoothing, extra error checking and naming variables better. Also need to pull the time out settings from a SQL table where they can be maintained.
Following code shows the registration of the global procedure events we are interested in (see the dexterity programmers guide for more explanation on what they do). We then have a class that simply holds an instance of the timer inactivity class and prods it when we get activity to update the time of last activity. In that timer inactivity class we have a system timer that checks regularly to see if the last time the class was prodded for activity was over the threshold number of mins. If so it then first off the countdown form. This form allows users to cancel the auto logout.
Public Class UserInactivityForm
Dim SecondsToLogout As Short = 50
Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
lblCount.Text = SecondsToLogout.ToString
SecondsToLogout = SecondsToLogout - CShort(1)
If SecondsToLogout = 0 Then Me.DialogResult = Windows.Forms.DialogResult.OK
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
DialogResult = Windows.Forms.DialogResult.Cancel
End Sub
Private Sub UserInactivityForm_Load(sender As Object, e As System.EventArgs) Handles Me.Load
Timer1.Interval = 1000
Timer1.Enabled = True
FlashWindow.Flash(Me)
End Sub
End Class
``` vbnet
Imports System.Windows.Forms
Public Class InactivityTimer
Public WithEvents oApplicaitonExitTimer As Timers.Timer
Private m_LastActivityTime As DateTime
Public Property LastActivityTime() As DateTime
Get
Return m_LastActivityTime
End Get
Set(ByVal value As DateTime)
m_LastActivityTime = value
End Set
End Property
Private m_TimeOutTimeMins As Short = 1
Public Property TimeOutTimeMins() As Short
Get
Return m_TimeOutTimeMins
End Get
Set(ByVal value As Short)
m_TimeOutTimeMins = value
End Set
End Property
Private m_Enabled As Boolean
Public Property Enabled() As Boolean
Get
Return m_Enabled
End Get
Set(ByVal value As Boolean)
m_Enabled = value
If Not IsNothing(oApplicaitonExitTimer) Then
oApplicaitonExitTimer.Enabled = value
oApplicaitonExitTimer.Start()
End If
End Set
End Property
Public Sub Tick()
Me.LastActivityTime = Now()
End Sub
Private o As UserInactivityForm
Private Sub oApplicaitonExitTimer_Elapsed(sender As Object, e As System.Timers.ElapsedEventArgs) Handles oApplicaitonExitTimer.Elapsed
'Checks to see if the current user has been idle too long and logs them out if so
CheckUserTimeOut()
'After first timeout check now check every 5mins
'If oApplicaitonExitTimer.Interval > 300000 Then
' oApplicaitonExitTimer.Interval = 300000
'End If
'CurrentUser.CheckUserTimeOut()
End Sub
Public Sub TryToLogout()
Try
If IsNothing(o) Then o = New UserInactivityForm
If o.ShowDialog() = Windows.Forms.DialogResult.OK Then
'Creates a temp macro and executes it
Dim CompilerApp As New Dynamics.Application
Dim CompilerMessage As String = Nothing
Dim CompilerError As Integer
Dim Commands As String
Commands = ""
Commands = Commands & "local integer l_file_id; " & vbCrLf
Commands = Commands & "local string pathname; " & vbCrLf
Commands = Commands & "pathname = Path_GetForApp(1) + ""TEMP_LOGOUT.MAC""; " &
vbCrLf
Commands = Commands & "l_file_id = TextFile_Open(pathname, 0, 0); " &
vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, ""CommandExec form BuiLtin command cmdQuitApplication""); " & vbCrLf
Commands = Commands & "TextFile_Close(l_file_id); " & vbCrLf
Commands = Commands & "if File_Probe(pathname) then " & vbCrLf
Commands = Commands & " run macro pathname; " & vbCrLf
Commands = Commands & "end if; " & vbCrLf
' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox(CompilerMessage)
End If
Else
o.Dispose()
o = Nothing
End If
Catch ex As Exception
Windows.Forms.MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub CheckUserTimeOut()
'If the last activity was longer than the timeout then try logout
If DateDiff(DateInterval.Minute, Me.LastActivityTime, Now()) > TimeOutTimeMins Then
TryToLogout()
End If
End Sub
Public Sub New()
oApplicaitonExitTimer = New Timers.Timer(60000)
oApplicaitonExitTimer.AutoReset = True
Me.oApplicaitonExitTimer.Enabled = False
End Sub
End Class
'Handlers looking for activity for the inactivity monitor
AddHandler DynamicsGP.Procedures.AddSuccessfulLoginRecord.InvokeAfterOriginal, AddressOf oGlobalProcedures.AfterSucessfulLogin
AddHandler DynamicsGP.Procedures.Pathname.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresPathnameInvokeAferOriginal
AddHandler DynamicsGP.Procedures.SqlScriptPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlScriptPathInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.SqlPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlPathInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.Security.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSecurityInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForNoteInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForRecordNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal
Imports System.Threading
Imports System.Security
Imports Microsoft.Dexterity.Bridge
Imports Microsoft.Dexterity.Applications
Imports System.Windows.Forms
Public Class GlobalProcedures
Private oInactivityTimer As New InactivityTimer
Public Sub DynamicsProceduresPathnameInvokeAferOriginal(sender As Object, e As DynamicsDictionary.PathnameProcedure.InvokeEventArgs)
oInactivityTimer.Tick()
End Sub
Public Sub DynamicsProceduresSqlScriptPathInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.SqlScriptPathProcedure.InvokeEventArgs)
oInactivityTimer.Tick()
End Sub
Public Sub DynamicsProceduresSqlPathInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.SqlPathProcedure.InvokeEventArgs)
oInactivityTimer.Tick()
End Sub
Public Sub DynamicsProceduresSecurityInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.SecurityProcedure.InvokeEventArgs)
oInactivityTimer.Tick()
End Sub
Public Sub DynamicsProceduresCheckForNoteInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.CheckForNoteProcedure.InvokeEventArgs)
oInactivityTimer.Tick()
End Sub
Public Sub DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal(sender As Object, e As DynamicsDictionary.CheckForRecordNoteProcedure.InvokeEventArgs)
oInactivityTimer.Tick()
End Sub
Public Sub AfterSucessfulLogin(ByVal sender As Object, ByVal e As EventArgs)
'Only allow auto logout after login...
Try
oInactivityTimer.Enabled = True
Catch ex As Exception
Windows.Forms.MessageBox.Show("Error on after open" & vbCrLf & ex.ToString, "Toolbar AfterSucessfulLogin", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Try
End Sub
End Class
Enhanced Version
I got around to adding a SQL check that the user count is high and excludes and key users such as sa or DYNSA. The following stored procedure checks for “permission” to log the user out. You could group users in this SQL into groups each with their own thresholds for example.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tim Wappat
-- Create date: 3rd Sept 2013
-- Description: When a user session inactivity times out
-- determines if the user should be logged off
-- =============================================
CREATE PROCEDURE gpmods.SY_UserInactivityTimeOutCheck
@UserID CHAR (15)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserCountThreshold AS INT;
SET @UserCountThreshold = 72;
IF ((SELECT COUNT(*)
FROM dynamics..ACTIVITY) > @UserCountThreshold
AND (@USERID NOT IN ('sa', 'DYNSA')))
BEGIN
RETURN 1;
END
ELSE
BEGIN
RETURN 0;
END
END
GO
GRANT EXECUTE ON gpmods.SY_UserInactivityTimeOutCheck TO DYNGRP
GO
Global Procedures Class handles GP events and contains the inactivity timer
Imports System.Threading
Imports System.Security
Imports Microsoft.Dexterity.Bridge
Imports Microsoft.Dexterity.Applications
Imports System.Windows.Forms
Public Class GlobalProcedures
Private oInactivityTimer As New InactivityTimer(60000)
'60000 1 min
Public Sub DynamicsProceduresPathnameInvokeAferOriginal(sender As Object, _
e As DynamicsDictionary.PathnameProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Public Sub DynamicsProceduresSqlScriptPathInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.SqlScriptPathProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Public Sub DynamicsProceduresSqlPathInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.SqlPathProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Public Sub DynamicsProceduresSecurityInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.SecurityProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Public Sub DynamicsProceduresCheckForNoteInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.CheckForNoteProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Public Sub DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal(sender As Object, _
e As DynamicsDictionary.CheckForRecordNoteProcedure.InvokeEventArgs)
Try
oInactivityTimer.Tick()
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Public Sub New()
oInactivityTimer.Enabled = True
End Sub
End Class
InactivityTimer class fires (60 mins in this example) to check for inactivity and logs the user out if no activity since last check and after checking with the sql stored proc that we actually need to free some users up.
Imports System.Windows.Forms
Public Class InactivityTimer
Public WithEvents oApplicaitonExitTimer As Timers.Timer
Private m_LastActivityTime As DateTime
Public Property LastActivityTime() As DateTime
Get
SyncLock (oLockLastActivityTime)
Return m_LastActivityTime
End SyncLock
End Get
Set(ByVal value As DateTime)
SyncLock (oLockLastActivityTime)
m_LastActivityTime = value
End SyncLock
End Set
End Property
Private m_TimeOutTimeMins As Short = 60
Public Property TimeOutTimeMins() As Short
Get
Return m_TimeOutTimeMins
End Get
Set(ByVal value As Short)
m_TimeOutTimeMins = value
End Set
End Property
Private m_Enabled As Boolean
Public Property Enabled() As Boolean
Get
Return m_Enabled
End Get
Set(ByVal value As Boolean)
m_Enabled = value
If Not IsNothing(oApplicaitonExitTimer) Then
oApplicaitonExitTimer.Enabled = value
oApplicaitonExitTimer.Start()
End If
End Set
End Property
Private oLockLastActivityTime As New Object
Private oLockLogout As New Object
Public Sub Tick()
Me.LastActivityTime = Now()
End Sub
Private oUserInactivityForm As UserInactivityForm
Private Sub oApplicaitonExitTimer_Elapsed(sender As Object, _
e As System.Timers.ElapsedEventArgs) _
Handles oApplicaitonExitTimer.Elapsed
'Checks to see if the current user has been idle too long and logs them out if so
CheckUserTimeOut()
End Sub
Private isLoggingOut As Boolean = False
Public Sub TryToLogout()
Try
'Test and set pattern to ensure another thread is not updating this flag
If Not isLoggingOut Then
SyncLock (oLockLogout)
If Not isLoggingOut Then
isLoggingOut = True
End If
End SyncLock
'Whatever the case, this is now a clean read
If isLoggingOut Then
If IsNothing(oUserInactivityForm) Then oUserInactivityForm = _
New UserInactivityForm
If oUserInactivityForm.ShowDialog() = Windows.Forms.DialogResult.OK Then
'Creates a temp macro and executes it
Dim CompilerApp As New Dynamics.Application
Dim CompilerMessage As String = Nothing
Dim CompilerError As Integer
Dim Commands As String
Commands = ""
Commands = Commands & "local integer l_file_id; " & vbCrLf
Commands = Commands & "local string pathname; " & vbCrLf
Commands = Commands & "pathname = Path_GetForApp(1) + ""TEMP_LOGOUT.MAC""; " &
vbCrLf
Commands = Commands & "l_file_id = TextFile_Open(pathname, 0, 0); " &
vbCrLf
Commands = Commands & "TextFile_WriteLine(l_file_id, ""CommandExec form BuiLtin command cmdQuitApplication""); " & vbCrLf
Commands = Commands & "TextFile_Close(l_file_id); " & vbCrLf
Commands = Commands & "if File_Probe(pathname) then " & vbCrLf
Commands = Commands & " run macro pathname; " & vbCrLf
Commands = Commands & "end if; " & vbCrLf
' Execute SanScript
CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
If CompilerError <> 0 Then
MsgBox(CompilerMessage)
End If
oUserInactivityForm.Dispose()
oUserInactivityForm = Nothing
Else
Tick()
oUserInactivityForm.Dispose()
oUserInactivityForm = Nothing
SyncLock (oLockLogout)
isLoggingOut = False
End SyncLock
End If
End If
End If
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Private Sub CheckUserTimeOut()
'If the last activity was longer than the timeout then try logout
If DateDiff(DateInterval.Minute, Me.LastActivityTime, Now()) > TimeOutTimeMins And Not isLoggingOut Then
If CheckDBForPermissionToLogout() Then
TryToLogout()
End If
End If
End Sub
Private Function CheckDBForPermissionToLogout() As Boolean
'Allows a stored to procedure to look at the activity table and decide if the user needs to be logged out,
' i.e. only log user out when activity table is nearing the user limit
' also prevents sa and some other users from getting logged out
' if connection is down or something to the database, error means we return true and the client logs out
Try
Using sql As New SqlClient.SqlConnection(CurrentUser.ConnectionStringSql)
sql.Open()
Using cmd As New SqlClient.SqlCommand("gpmods.SY_UserInactivityTimeOutCheck", sql)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@USERID", CurrentUser.UserID)
Dim paramReturnValue As New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(paramReturnValue)
cmd.ExecuteNonQuery()
Dim myReturnValue As Integer = CInt(paramReturnValue.Value)
If myReturnValue = 0 Then
Return False
Else
Return True
End If
End Using
End Using
Catch ex1 As SqlClient.SqlException
Return True
Catch ex As Exception
Throw
End Try
End Function
Public Sub New(TimeOutCheckFrequencySeconds As Integer)
oApplicaitonExitTimer = New Timers.Timer(TimeOutCheckFrequencySeconds)
'60000 1 min
oApplicaitonExitTimer.AutoReset = True
Me.oApplicaitonExitTimer.Enabled = False
End Sub
End Class
The add-in when initialised creates an instance of the GlobalsProcedures Class and registers the events of interest to keep the application alive when activity takes place.
Imports System.Windows.Forms
Imports DynamicsGP = Microsoft.Dexterity.Applications.Dynamics
Imports Microsoft.Dexterity.Applications
Imports Microsoft.Dexterity.Applications.MenusForVisualStudioToolsDictionary
Imports Microsoft.Dexterity.Bridge
Public Class GPAddIn
Implements IDexterityAddIn
Private HasInitalisedEventHandlers As Boolean
'GlobalProcedures essentially handles the inactivity time out functionality
Public oGlobalProcedures As New GlobalProcedures
Sub Initialize() Implements IDexterityAddIn.Initialize
Try
If Not HasInitalisedEventHandlers Then
RegisterEventHandlers()
HasInitalisedEventHandlers = True
End If
Catch ex As Exception
exceptions.ExceptionHandler.ShowException(ex)
End Try
End Sub
Public Sub RegisterEventHandlers()
'=========================================================================================================================================
'----------------------- Hooks used for inactivity monitoring -----------------------------
'=========================================================================================================================================
AddHandler DynamicsGP.Procedures.Pathname.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresPathnameInvokeAferOriginal
AddHandler DynamicsGP.Procedures.SqlScriptPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlScriptPathInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.SqlPath.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSqlPathInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.Security.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresSecurityInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForNoteInvokeAfterOriginal
AddHandler DynamicsGP.Procedures.CheckForRecordNote.InvokeAfterOriginal, AddressOf oGlobalProcedures.DynamicsProceduresCheckForRecordNoteInvokeAfterOriginal
End Sub
End Class
Note
18th May 2014: We had some problems in production with instability in GP with the first version of this code. When the timer fires it creates a new thread, so a little attention to detail is required to handle multi-threaded variable access, hence in the more recent version it uses sync locks. Now those problems should be eradicated.
Credits
Thanks to Mohammad R. Daou and David Musgrave for their contributions to this project via forums and blogs.