Quick Tip: Making sure Activity Tracking is enabled for all users


David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

This Quick Tip comes to you courtesy of an idea from my friend, Robert Cavill.

On a Microsoft Dynamics GP system where Activity Tracking is being used, it needs to be activated for each User ID/Company ID that you want it enabled for. In most cases, you would want the Activity Tracking enabled for all valid User ID and Company ID combinations.

What we have seen many times is that when new users or new companies are created in the system or user company access settings are changed, the settings in Activity Tracking Setup are overlooked and not updated to activate the tracking. Meaning that Activity Tracking is not capturing all the desired events for all users in all companies.

If you want to ensure Activity Tracking is enabled for all Users and Companies automatically, you can create a SQL Agent job to run the following code on a regular basis:

update SY60100
set TRKUSER = 1 
where TRKUSER = 0

print 'Changed : ' + str ( @@ROWCOUNT )

OR

You could also have a trigger on DYNAMICS.dbo.SY60100 table which will update the Activity Tracking flag automatically when the record in the table is created or updated. The code for the trigger is below and also attached to this post.
SQL Trigger Script

-- Trigger to make sure Activity Tracking is enabled
USE DYNAMICS
GO

IF EXISTS (
		SELECT *
		FROM dbo.sysobjects
		WHERE id = object_id(N'[dbo].[SY60100_INSERTUPDATE_ActivityTracking]')
			AND OBJECTPROPERTY(id, N'IsTrigger') = 1
		)
	DROP TRIGGER [dbo].[SY60100_INSERTUPDATE_ActivityTracking]
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS OFF
GO

CREATE TRIGGER [SY60100_INSERTUPDATE_ActivityTracking] ON [dbo].[SY60100]
FOR INSERT
	,UPDATE NOT
FOR REPLICATION AS

DECLARE @RowCount INT

SELECT @RowCount = count(*)
FROM inserted
WHERE EXISTS (
		SELECT *
		FROM SY60100 A
		WHERE A.USERID = inserted.USERID
			AND A.CMPANYID = inserted.CMPANYID
			AND A.TRKUSER <> 1
		)

SET NOCOUNT ON

BEGIN
	UPDATE A
	SET A.TRKUSER = 1
	FROM SY60100 A
	JOIN inserted I ON A.USERID = I.USERID
		AND A.CMPANYID = I.CMPANYID
	WHERE I.TRKUSER <> 1

	DECLARE @logmsg VARCHAR(500)

	IF @@ROwCount <> @RowCount
	BEGIN
		SELECT TOP 1 @logmsg = USERID + '(' + STR(CMPANYID) + ')'
		FROM inserted

		SELECT @logmsg = 'Trigger ' + db_name() + '.SY60100_INSERTUPDATE_ActivityTracking (' + SYSTEM_USER + '): ' + @logmsg + ' may not have updated SY60100 table correctly'

		EXEC master.dbo.xp_logevent 60011
			,@logmsg
			,Error
	END
	ELSE IF @RowCount > 0
	BEGIN
		SELECT TOP 1 @logmsg = USERID + '(' + STR(CMPANYID) + ')'
		FROM inserted

		SELECT @logmsg = 'Trigger ' + db_name() + '.SY60100_INSERTUPDATE_ActivityTracking (' + SYSTEM_USER + '): ' + @logmsg + ' updated Activity Tracking'

		EXEC master.dbo.xp_logevent 60012
			,@logmsg
			,Informational
	END
END
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO



Hope this is handy.

David

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

SQL Trigger SY60100_INSERTUPDATE_ActivityTracking.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

3 thoughts on “Quick Tip: Making sure Activity Tracking is enabled for all users

  1. Hi David,
    You cloned my mind 🙂 … I used the same trick since many years to update the Activity tracking table for new users… much quicker than using the form in GP. But your script for the trigger is a big winner !! no longer to worry about forgetting some new user.
    I also use the table SY05000 to run some stats about the login/logout activities to report my top 10 users of the month and see who's the most active in GP. The activity record of type 2 and 15 are the login and logout of GP. For some strange reason, GP logs two records of type 15 when the user quits GP… Any idea why ?
    Have a great time,
    Beat

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.