GP User Activity with Status and IP

If you've ever needed to clear out old GP users, you know it can sometimes be difficult to figure out which ones are still connected and which ones are not. Yes, the ones that logged in two days ago may seem the obvious choices to kick out, but that's not always the case. We've seen users leave GP open for days on end to not lose their seat and their SQL session is hot, and we've seen users open GP in the morning but bombed out an hour later but didn't need it anymore so they didn't bother to log in to clear their seat.

Either way, this script below combines several GP and SQL system tables and views to give you a broad view of the status of your GP users. It will show whether users in User Activity are in fact still connected to GP, how long they've been logged into GP, when their last SQL activity occurred and how long ago it was, their computer's IP address, if they are working on batches, and what table they are currently working in.

I'm sure there is still some room for improvement, and I will be adding to it from time to time, so check back later for any updates.

USE DYNAMICS
SELECT 
    Activity.USERID UserID, 
    Users.USERNAME Username, 
    Activity.CMPNYNAM Company,
    CASE
        WHEN AbandonedSessions.SQLSESID IS NULL
            THEN 'Connected'
        ELSE 'Removable'
    END Status,
    CONVERT(varchar(20),ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) LoggedInAt,
    ISNULL(CONVERT(varchar(20),ConnectedSessions.last_batch),'') LastGPUsage,
    CASE WHEN GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))-1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 's '
    END    TimeSinceLogin,
    CASE 
        WHEN ConnectedSessions.last_batch IS NULL 
            THEN ''
        WHEN GETDATE()-ConnectedSessions.last_batch < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-ConnectedSessions.last_batch)- 1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-ConnectedSessions.last_batch)) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-ConnectedSessions.last_batch)) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-ConnectedSessions.last_batch)) + 's '
    END GPIdleTime,
    ISNULL(lock.table_path_name,'') OpenTable,
    ISNULL(batch.BatchCount,0) BatchesInProgress,
    Activity.SQLSESID SessionID,
    ISNULL(CONVERT(varchar(10),ConnectedSessions.SPID),'') SPID,
    ISNULL(ConnectedSessions.client_net_address,'') IPAddress
FROM 
    DYNAMICS..ACTIVITY Activity
    INNER JOIN DYNAMICS..SY01400 Users 
    ON Activity.USERID = Users.USERID
LEFT JOIN
    (
        SELECT *
        FROM DYNAMICS..Activity 
        WHERE SQLSESID NOT IN (
            SELECT SQLSESID
            FROM DYNAMICS..Activity A
            INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
            INNER JOIN master..sysprocesses P
            on S.sqlsvr_spid = P.spid
            AND A.USERID = P.loginame
        )
        AND NOT EXISTS
            (SELECT spid, c.CMPNYNAM DB, loginame,*
            FROM master..sysprocesses p
            inner join master..sysdatabases d
                on p.dbid = d.dbid
            inner join DYNAMICS..SY01500 c
                on d.name = RTRIM(c.INTERID)
            where c.CMPNYNAM = Activity.CMPNYNAM
                and p.loginame = Activity.USERID
                and p.program_name = ''
            )
    ) AbandonedSessions
    ON Activity.SQLSESID = AbandonedSessions.SQLSESID
    AND Activity.USERID = AbandonedSessions.USERID
LEFT JOIN
    (
        SELECT 
            A.SQLSESID, 
            P.spid, 
            P.last_batch, 
            ec.client_net_address, 
            CASE 
                WHEN P.login_time < A.LOGINDAT + A.LOGINTIM 
                    THEN p.login_time 
                ELSE A.LOGINDAT + A.LOGINTIM 
            END last_login
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
            ON S.sqlsvr_spid = P.spid
        AND A.USERID = P.loginame
        INNER JOIN sys.dm_exec_connections ec
            ON S.sqlsvr_spid = ec.session_id
    ) ConnectedSessions
    ON Activity.SQLSESID = ConnectedSessions.SQLSESID
LEFT JOIN
    (
        SELECT session_id, max(table_path_name) table_path_name
        FROM tempdb..DEX_LOCK
        GROUP BY session_id
    ) lock
    ON Activity.SQLSESID = lock.session_id
LEFT JOIN
    (
        SELECT
            USERID,
            CMPNYNAM,
            COUNT(*) BatchCount
        FROM
            Dynamics..SY00800
        GROUP BY
            USERID,
            CMPNYNAM
    ) batch
    ON Activity.USERID = batch.USERID
    and Activity.CMPNYNAM = batch.CMPNYNAM
ORDER BY 
    Activity.LOGINDAT,
    Activity.LOGINTIM