samc technologies blogs

SAMC Technologies staff feel it's important to share our experiences with others as part of our open communication approach.  Please feel free to send us any comments you have regarding our blogs. 

Dynamics SQL: Obtain list of all GP users

Here is a useful script for obtaining a list of users.
I will also demo how to join this table to the activity table in order to create some useful report data.

USE DYNAMICS
SELECT
 USERID
,USERNAME
FROM SY01400
ORDER BY USERID

Rather than setting each user as I create them in GP for log in and out tracking, I have the following script as an SQL Agent job to run during my night processes.

UPDATE SY60100
SET TRKUSER = 1
WHERE TRKUSER = 0

Now when you want to see the activity log, execute the following from Management Studio:

use DYNAMICS
select usr.USERID
,usr.USERNAME
,(
SELECT MAX (DATE1 + TIME1)
from SY05000
where INQYTYPE = 2
and USERID = usr.USERID
) as LastLogin
FROM SY01400 usr

Note: if you are getting null vlaues in your last login make sure you have turned on Activity Logging for Log ins