Dynamics GP - Remove inactive users

Remove Idle users
Log out idle users


Original Link
http://mohdaoud.blogspot.com/2010/02/script-to-get-users-logged-in-to-gp_2325.html

Use this to find all idle users, then delete from the activity table. Will post a complete script soon.

SELECT
CASE WHEN S.session_id IS NULL THEN 'Missing DEX_SESSION' ELSE '' END MISSING_SESSION,
CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1 THEN 'Idle for ' + LTRIM(RTRIM(STR(DATEDIFF(mi, P.last_batch, GETDATE())))) + ' minutes.' ELSE '' END AS IDLE_TIME_DESC,
CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1 THEN DATEDIFF(mi, P.last_batch, GETDATE()) ELSE 0 END AS IDLE_TIME,
A.USERID,
A.CMPNYNAM COMPANY_NAME,
INTERID COMPANY_ID,
LOGINDAT + LOGINTIM LOGIN_DATE_TIME,
SQLSESID SQL_SESSIONID,
P.login_time SQL_LOGINTIME,
P.last_batch SQL_LAST_BATCH,
DATEDIFF(mi, P.last_batch, GETDATE()) TIME_SINCE_LAST_ACTION,
S.session_id SQLSERVER_SESSIONID,
S.sqlsvr_spid SQLSERVER_PROCESSID,
P.spid PROCESSID,
P.status PROCESS_STATUS,
P.net_address NET_ADDRESS,
P.dbid DATABASE_ID,
P.hostname HOSTNAME
FROM DYNAMICS..ACTIVITY A
LEFT JOIN DYNAMICS..SY01400 U ON A.USERID = U.USERID
LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM
LEFT JOIN tempdb..DEX_SESSION S ON A.SQLSESID = S.session_id
LEFT JOIN master..sysprocesses P ON S.sqlsvr_spid = P.spid AND ecid = 0
LEFT JOIN master..sysdatabases D ON P.dbid = D.dbid