What is it all about?
There were lots of inquiries recently following a forum question about how to deal with the user’s ACTIVITY table in Dynamics GP. Some company are adding user licenses when they feel being short or having too much users complaining about the inability to login into GP, because all the licenses are used up in the system.
Now there is always a question that comes up in this situation: how many of my GP licenses are really in use and how much are currently stranded users that have been disconnected by the system or closed on the ‘hard’ way (i.e. killed their GP session) the client because it was not responding or they were stuck in a loop not being able to logoff properly.
The Way Dynamics GP tracks the user licenses is through the table DYNAMICS..ACTIVITY in the system database of the SQL server. From the registration key, Dynamics know how much user licenses are granted to this server and thus tracks the count in that table. Now when user proceed with normal login / logout, the table gets updated every time and puts a time stamp in the fields LOGINDAT & LOGINTIM in the form of a long Date string. This is a way to know since how long a user has been logged in GP, but doesn’t tell you if the user is actually working or not in GP actively. This can only be achieved by monitoring closely the processes running inside of GP itself. Some 3rd party tools like the Rockton GP Toolbox and Willoware GP Powerpack offer that kind of functions.
Dynamics GP offers a way to track the login/logout activity in the system itself, but that doesn’t help more if looking for ‘inactive’ users or stranded logins. This would only serve as a kind of statistics log. To enable the user login/logout tracking, go to “Microsoft Dynamics GP menu >> Tools >> Utilities >> System >> Activity Detail” and select the appropriate range of users or company to enable / disable the login/logout activity. The table that is tracking the activity is SY05000 from the DYNAMICS system DB.
Now what to do? How to get rid of stranded users?
A simple way to get rid of stranded user’s login in the ACTIVITY table is to delete the entries from that table, after you make sure the user is really no longer working in GP.
In a usual 9-to-5 office environment, this is pretty easy to achieve, because most of the GP activity will take place during those office hours, thus making it easy to determine who is working or not and I doubt that you’ll have a lots of users working off-hours let’s say around 11PM or even 1AM in the morning.
But you don’t want to setup your clock and get up in the midst of the night to flush your ACTIVITY table, so your SQL Agent is your best friend in this sense. All you have to do is create a scheduled job that would run a script and clear out the table.
What I do is filter the DELETE action and leave out user logins that I know might be used (i.e. the ‘sa’ user account or my own one, because I might doing some nightly maintenance on GP). The script for the job would look like this:
Delete from ACTIVITY
LOGINDAT < GETDATE()-1 and –> optional, cleans out only logins older then 24hrs
(USERID <> ‘sa‘ or USERID <> ‘xxxxx‘)
Your Step 1 for the job would look like this below. Schedule it to be run every night before you do your SQL backups routine (or later if this happens to be earlier in the evening). Mine runs at midnight. Make sure you make it run under the DYNAMICS Database.
To complete this, I use also a stored procedure that I run on schedule one or two hours before the cleanup task and that sends me the list of the stranded users in the ACTIVITY table. The code for this makes usage of a send-mail feature in SQL that needs to be enabled in order to work (see comment from next chapter).
The code goes like follow:
/****** Object: StoredProcedure [dbo].[usp_GPLoggedUsers_overtimed] Script Date: 2013-04-04 08:01:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
— Author: < Beat BUCHER >
— Create date: 2009-09-08 / 10:22 /
— Description: The script validates which user account is logged in Dynamics GP
— since more then 750 min (12,5 hrs) and sent the report by e-mail
CREATE PROCEDURE [dbo].[usp_GPLoggedUsers_overtimed]
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
select datediff(mi,logindat+logintim, getdate()) as DURATION, –convert(datetime, convert(varchar(15), GetDate(), 114), 114) – LOGINTIM as DURATION,
where datediff(mi,logindat+logintim, getdate()) > 750
DECLARE @SQL varchar(8000)
SET @SQL = ‘select datediff(mi,logindat+logintim, getdate()) as DURATION, USERID,
from DYNAMICS.dbo.ACTIVITY where datediff(mi,logindat+logintim,getdate()) > 750′
EXEC msdb.dbo.sp_send_dbmail@recipients = ‘firstname.lastname@example.org’,
@subject = ‘Users Logged in beyond limit’,
@body = ‘Attached is a list of users that have been logged in beyond the limit’, @query = @SQL,
@attach_query_result_as_file = ‘TRUE’,
@query_result_width = 250
Change the @recipients variable content to fit your needs accordingly.
I’m constantly hitting the max user license limit. Is there a way to monitor this?
Yes there is. This actually implies some setup on the SQL server side, since by default the function to send out e-mails is disabled from SQL 2005 and up (SQL 2000 still had it enabled by default, but not much systems are running on this old SQL version today). This goes behind the topic of this article, but you have to make sure with your DBA or SQL admin that the sp_sendmail stored procedure is enabled and proper notification and SQL-mail is configured in the server. There are plenty of SQL forums and sites that discuss this in detail.
Since this blog environment doesn’t allow uploading attachments, I have to explode the entire code here in the text.
declare @limit int, @current int
declare @msg varchar(80)
declare @threshold decimal(5,2)
set @limit = 16 /* current Great Plains limit – unable to extract from reg keys of table */
set @threshold = 0.85
select @current = count(*) from ACTIVITY
print ‘Current = ‘ + str( @current)
>= @limit begin
set @msg = ‘Great Plains user limit of ‘ + str( @limit) + ‘ has been hit.’
@recipients = ‘email@example.com;’
, @subject = ‘Great Plains User limit hit’
— @message = @msg
if @current >=( @limit * @threshold ) begin
set @msg = ‘Great Plains user connections (‘ + str( @current ) + ‘) exceeds ‘ + ltrim(str( @threshold * 100)) + ‘ % limit of the available ‘ + str( @limit) + ‘.’
@recipients = ‘firstname.lastname@example.org;’
, @subject = ‘Great Plains User count exceeds threshold’
, @body = @msg
The best way to make this work is to put this in a SQL Job that will be scheduled to run every 5 minutes or so (adjust to your needs) during the business hours. In my setup below, there is another step to collect stats information about the login activity in GP, which allows me to draw some nice charts in Excel. This too goes behind the topic of this article, but I can cover that in a later blog post if there is some interest for it. Just let me know by leaving a comment at the bottom.
I hope this will help some users to get better in control with their Dynamics GP user licenses. As mentioned at the beginning, 3rd party tools have way more to offer and can be very handy to handle such situation, but they come with a price tag $$$$ attached, whereas those proposed solutions are free to implement.
I had worked for a company that didn’t wanted to understand the benefit of buying a product like Rockton’s Toolbox to kick out inactive users from GP and rather spend a lot of their precious dollars by adding new GP user licenses… When I left the company, the user license count was up to 48 users and we didn’t had much more active GP users then my current company, and we run with only 16 concurrent licenses, barely hitting the maximum (it happened maybe 3-4 times since I joined the company).
My total user count in GP is 137 users from which about 40 are login into GP on a regular basis (more than a dozen times within the last 6 months), and about 20 heavy users, almost every day in GP. I never run out of licenses with the help of the Rockton GP Toolbox. I mention this product because it is the one our company bought several years ago when it was still called OmniTools, but other products in the market is doing a similar job.
From what I have discussed with Microsoft during my last Convergence participation, we may soon see a built-in function in GP to manage that kind of issues (i.e. inactivity timeout and maintenance lockout from the system in order to carry on upgrades), so cross your fingers.
Enjoy and start to work on a better control of your GP environment.