Query users who have not logged in for x days, months, etc.

[mk553]

I'm using Microsoft Dynamics (Great Plains) version 10.

I seem to be having some difficulty finding the answer to my question, and hope you can help.

For auditing purposes, I need to query the GP Database in SQL to find users who have not logged into the system for x number of days, months, years, etc. I thought maybe there would be a built-in way in Microsoft SQL to do this, but there surprisingly isn't.

I read in some places about an Activity table, but I don't even have an activity table. I found a table called SY01401, but I don't think that's what I'm looking for.

On a side note, I have an ERD for version 9, but not for version 10. Does anyone know if there's a big difference?

Appreciate any guidance,

Thanks!

 

[martink1]

I don't think you'll find that in the database, but I'm not positive.
With-in GP you can run an activity report on log-ons and log-offs. But you have to have set it up to record those events.
setup up is in Tools > Setup > system > Activity tracking
reports are Tools > Utilities > system > Activity Tracking
or Inquiry > System > Activity tracking

 

[klewis10367]

First you have to turn on Activity Logging for Log ins.

Then You can use sy01400( User master ) and sy05000 (Activity tracking). Link by user id. Both tables are located in the Dynamics database. That should get you started.

 

[jazgeek]

you could probably create a trigger as well that would populate a table with user login times.

we use a similar trigger to track changes on SOP docs.





-----------
and they wonder why they call it Great Pains!

jaz

 

[RobertC22]

Rather than manually marking each user as I add them to the system for log in and out tracking, I have the following script as an SQL Agent job to run at least daily.

update SY60100
set TRKUSER = 1 
where TRKUSER = 0

This replaces the need to go to
Tools > Setup > system > Activity tracking to each company for each user in the lower part of the window.

It does assume that you have selected the tracking on User login in the top half of that window once.

Then when you want to know the most recent access by a user, execute the following within Query Analyzer

use DYNAMICS

select USERID, USERNAME, 
( select max (DATE1 + TIME1)
from SY05000 A
where INQYTYPE  = 2
and A.USERID = U.USERID
) as LastLogin
from SY01400 U

So taking that code a bit further, here is how you can check for users whose last login was more than a month ago.

select USERID, USERNAME, LastLogin
from 
(
	select USERID, USERNAME, 
	( select max (DATE1 + TIME1)
	from SY05000 A
	where INQYTYPE  = 2
	and A.USERID = U.USERID
	) as LastLogin
	from SY01400 U
) UserAccess
where datediff ( mm, LastLogin, Getdate() ) > 1
order by USERID

Use the SQL BOL (books online) to get more details on using the DATEDIFF function to check for days or weeks or years)


------
Robert