User Last Login

(1) Share

Report

Posted on by Community Member

Hello all. I hope this helps a few of you as I couldn't find anything online for finding out when a user last logged into GP (except in the ACTIVITY table, which is emptied upon user exit).

I work with the auditors in my company to provide compliance documentation for their areas of interest. One of their areas of interest is user password expiration. One of the things I had a hard time convincing them of was if a user's password is more than 90 days old (which would normally be in violation of our policy) and that user hasn't been in GP to receive the notice to change their password, then the 'violation' isn't really a violation. Without having auditable proof of the user's last login date/time, my theory remained unproven.

So, I wrote a SQL statement to give me a list of SQL logins and their statuses based on the sys.server_principals and sys.sql_logins tables and was going to use the syslogins table for the accdate as Last Login date, but that date didn't jive with the GP user actual last login.

To rectify this, I created a table and wrote a trigger to begin tracking user login history:

CREATE TABLE LastLogin (

NAME VARCHAR(50),

LastLogin DATETIME)

-----------------------------------------------------------------------------------------

CREATE TRIGGER trigger_UpdateLastLogin ON ACTIVITY

FOR UPDATE, INSERT AS

-- Step one: insert any new users

INSERT INTO LastLogin (NAME)

SELECT q1.NAME

FROM

(SELECT sp.NAME

FROM sys.server_principals sp

WHERE sp.[type] IN ('S', 'U', 'G')

AND sp.type_desc = 'SQL_LOGIN'

)q1

WHERE q1.NAME NOT IN (SELECT NAME FROM LastLogin)

--Step two: update last login value

UPDATE LastLogin

SET LastLogin = (SELECT CONVERT(VARCHAR(10),LOGINDAT,111)+' '+CONVERT(VARCHAR(5),LOGINTIM,108)

FROM ACTIVITY

WHERE ACTIVITY.USERID = LastLogin.NAME)

WHERE NAME IN (SELECT USERID FROM ACTIVITY)

I then modified my initial select statement to include the new data field:

SELECT sp.name NAME

, sp.type_desc LoginType

, CASE WHEN sp.is_disabled = 1 THEN 'YES' ELSE 'NO' END AS IsDisabled

, sp.create_date CreatedOn

, sp.modify_date LastChanged

, ll.LastLogin

, CASE WHEN sl.is_policy_checked = 1 THEN 'YES' ELSE 'NO' END AS PolicyChecked

, CASE WHEN sl.is_expiration_checked = 1 THEN 'YES' ELSE 'NO' END AS ExpireChecked

, CASE WHEN sp.modify_date < GETDATE()-90 AND sp.is_disabled = 0 THEN 'YES' ELSE '' END AS InViolation

FROM

sys.server_principals sp

LEFT OUTER JOIN

sys.sql_logins sl ON sp.SID = sl.SID

LEFT OUTER JOIN LastLogin ll

ON ll.NAME = sp.name

WHERE

sp.[type] IN ('S', 'U', 'G')

AND sp.type_desc = 'SQL_LOGIN'

ORDER BY sp.[name]

Please feel free to comment on ways to improve this process or give an alternative solution.

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report
RE: User Last Login

Hello Joe,

It seems that you have such a considerable work. Although, before guiding through your script, I would like to shed a light that there is Activity Tracking in Dynamics GP which can just meet your requirements and record all the login/logout details either for specific or group of users.

Activity tracking has many levels to be activated which are;

Tools > Setup > System > Activity Tracking) ,

Login/ Log out tracking

Access Tracking

File Tracking

Process Tracking

Posting Tracking

Once activated, you can view the details from an inquiry widow with proper filtration criteria ()

Please reach further details on System Activity Tracking for Auditing

Your feedback is highly appreciated,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: User Last Login

Excellent! Thanks for that reminder, Mahmound. I remembered seeing something about this at Convergence this year, but I couldn't remember what it was called. Since your reminder, I've played with this a little in our DEV environment and have the following review:

Activity Tracking

Pros:

> The event is not recorded until the all GP modules are fully loaded (maybe a pro?)

> The utility is 'out-of-the-box' so it is already a proven tool for auditing

> The audit can be reviewed from within the GP UI

Cons:

> Must be configured on a per-user basis, so each new user would need an extra step in setup

> Each event is logged separately in the SY05000, which is excellent for granular auditing, but is more than we need and creates the potential for large table growth unless regularly maintained

The Trigger

Pros:

> For our application, only the most recent event is needed and that's all that is captured, maintaining constant table size with minimal growth

> The event is recorded after user successfully logs into a company (but before all GP modules are fully loaded) - which means they made it past the login prompt

> Is a "set it and forget it" resolution that doesn't need special attention when new users are added

Cons:

> Will need to be documented, proven and approved both internally and by the auditors

> Creates a customization that will need to be considered when upgrading

Again, thanks for the direction to the auditing tool as I believe it may be a superb solution for some folks and some of the other tracking is good to have in your back pocket for certain "problem user" scenarios.

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: User Last Login

Impressive !

Please keep us updated for any related inquiries.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: User Last Login

Hi Jody

I learning the SQL skills - and ways to manipulate SQL database.

I understand how to create the table - is that in the DYNAMICS database?

How do I create the trigger? and Which SQL script do I use in the trigger - you mention that you changed the "initial statement" - so I was wondering which script is the one to use.

Thanks

Ray

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: User Last Login

Ray,

Thank you for your interest! These elements are created in the DYNAMICS database. Here is the code refactored for ease of use and better readability (run each section independently):

-----------------------------------------------------------------------------------------

USE DYNAMICS

GO

CREATE TABLE LastLogin (NAME VARCHAR(50), LastLogin DATETIME)

-----------------------------------------------------------------------------------------

USE DYNAMICS

GO

CREATE TRIGGER UpdateLastLogin ON ACTIVITY

FOR UPDATE, INSERT AS

INSERT INTO LastLogin (NAME)

SELECT q1.NAME

FROM (SELECT sp.NAME

FROM sys.server_principals sp

WHERE sp.[type] IN ('S', 'U', 'G')

AND sp.type_desc = 'SQL_LOGIN')q1

WHERE q1.NAME NOT IN (SELECT NAME FROM LastLogin)

UPDATE LastLogin

SET LastLogin = (SELECT CONVERT(VARCHAR(10),LOGINDAT,111)+' '+CONVERT(VARCHAR(5),LOGINTIM,108)

FROM ACTIVITY

WHERE ACTIVITY.USERID = LastLogin.NAME)

WHERE NAME IN (SELECT USERID FROM ACTIVITY)

-----------------------------------------------------------------------------------------

USE DYNAMICS

GO

CREATE VIEW UserLoginAudit AS

SELECT

sp.name NAME

, sp.type_desc LoginType

, CASE WHEN sp.is_disabled = 1 THEN 'YES' ELSE 'NO' END AS IsDisabled

, sp.create_date CreatedOn

, sp.modify_date LastChanged

, ll.LastLogin

, CASE WHEN sl.is_policy_checked = 1 THEN 'YES' ELSE 'NO' END AS PolicyChecked

, CASE WHEN sl.is_expiration_checked = 1 THEN 'YES' ELSE 'NO' END AS ExpireChecked

, CASE WHEN sp.modify_date < GETDATE()-90 AND sp.is_disabled = 0 THEN 'YES' ELSE '' END AS InViolation

FROM sys.server_principals sp

LEFT OUTER JOIN

sys.sql_logins sl ON sp.SID = sl.SID

LEFT OUTER JOIN LastLogin ll ON ll.NAME = sp.name

WHERE sp.[type] IN ('S', 'U', 'G')

AND sp.type_desc = 'SQL_LOGIN'

-----------------------------------------------------------------------------------------

-- Your final query to use the view you created

SELECT * FROM DYNAMICS..UserLoginAudit

ORDER BY NAME

/*

Though Mahmoud's suggestion adds an extra step to new user setup, it is already vetted and audit-compliant, so it may be the preferred method of approach. It's easy enough to create a maintenance plan step to keep the logs cleaned out of the SY05000 on intervals as well.

Hope this helps!

As always, use code at your own risk....

*/

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: User Last Login

Thanks Jody

This is a great help and works a treat.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: User Last Login

Hi Jody

This view and trigger are working well for us - but they created a situation - where a user cannot login into more than 1 database at 1 time. We have several databases running different (related) businesses - but when a user tries to login to a second different database - they get the following message

More info gives the following:

The trigger ‘UpdateLastLogin’ is failing if more than one entry is returned per user id from the Activity table.

Any thoughts on how this might be managed - still getting to grasps with SQL.

Thanks in advance

Ray

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: User Last Login

Good morning, Ray.

Wow, this one pulled on my memory strings a bit, going back to 2014, but I like a challenge.

The error you are getting is because of the WHERE clause in the TRIGGER (WHERE ACTIVITY.USERID = LastLogin.NAME)). In order to get the results you're looking for (I think), you'd also need to account for the Company. If you want to keep your current LastLogin data intact, you'd need to back up the table before proceeding, but here is what I threw together this morning to address the issue you're having. It's kind of a hack, so (again) use it at your own risk.

The specific modifications:

1) Drop and re-create (or modify) the LastLogin table to include CompanyName

2) Alter the UPDATE clause in the UpdateLastLogin trigger to include the CompanyName criteria and data

3) Alter the View to include the CompanyName field

What to expect:

Now the LastLogin table will be updated to reflect the Last Login Per Company.

I'm sure there is a cleaner way of doing the update with a HAVING statement, but this seems to work in my environment.

-----------------------------------------------------------------------------------------

USE DYNAMICS

GO

CREATE TABLE LastLogin (UserName VARCHAR(50), CompanyName VARCHAR(65), LastLogin DATETIME)

-----------------------------------------------------------------------------------------

USE DYNAMICS

GO

CREATE TRIGGER UpdateLastLogin ON ACTIVITY

FOR UPDATE, INSERT AS

INSERT INTO LastLogin (UserName)

SELECT q1.NAME

FROM (SELECT sp.NAME

FROM sys.server_principals sp

WHERE sp.[type] IN ('S', 'U', 'G')

AND sp.type_desc = 'SQL_LOGIN')q1

WHERE q1.NAME NOT IN (SELECT UserName FROM LastLogin)

UPDATE LastLogin

SET LastLogin.CompanyName = ACTIVITY.CMPNYNAM

, LastLogin.LastLogin = CONVERT(VARCHAR(10),ACTIVITY.LOGINDAT,111)+' '+CONVERT(VARCHAR(5),ACTIVITY.LOGINTIM,108)

FROM LastLogin

INNER JOIN ACTIVITY ON LastLogin.UserName = ACTIVITY.USERID

WHERE CONVERT(VARCHAR(10),ACTIVITY.LOGINDAT,111)+' '+CONVERT(VARCHAR(5),ACTIVITY.LOGINTIM,108) = (SELECT MAX(CONVERT(VARCHAR(10),LOGINDAT,111)+' '+CONVERT(VARCHAR(5),LOGINTIM,108))from activity)

-----------------------------------------------------------------------------------------

USE DYNAMICS

GO

CREATE VIEW UserLoginAudit AS

SELECT

sp.name NAME

, sp.type_desc LoginType

, CASE WHEN sp.is_disabled = 1 THEN 'YES' ELSE 'NO' END AS IsDisabled

, sp.create_date CreatedOn

, sp.modify_date LastChanged

, ll.LastLogin

, ll.CompanyName

, CASE WHEN sl.is_policy_checked = 1 THEN 'YES' ELSE 'NO' END AS PolicyChecked

, CASE WHEN sl.is_expiration_checked = 1 THEN 'YES' ELSE 'NO' END AS ExpireChecked

, CASE WHEN sp.modify_date < GETDATE()-90 AND sp.is_disabled = 0 THEN 'YES' ELSE '' END AS InViolation

FROM sys.server_principals sp

LEFT OUTER JOIN

sys.sql_logins sl ON sp.SID = sl.SID

LEFT OUTER JOIN LastLogin ll ON ll.UserName = sp.name

WHERE sp.[type] IN ('S', 'U', 'G')

AND sp.type_desc = 'SQL_LOGIN'

-----------------------------------------------------------------------------------------

-- Your final query to use the view you created

SELECT * FROM DYNAMICS..UserLoginAudit

ORDER BY NAME

-----------------------------------------------------------------------------------------

Was this reply helpful? Yes No