Last Activity Time For A User

This question is not answered

Is there any column that will show the last activity time for a user?  I would like to be able to send email alerts to users who are logged in but haven't been active for 1 hour.

Ron Wilson Real Life Dynamics User http://rldu.wordpress.com
All Replies
  • Hi Ron

    Have a look at the attached script.

    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

    This script has a column called IDLE_TIME in minutes. That should help you track how long a specific user is idle.

    Let me know if you found this query useful.

    Let us know if you need further assistance.

    Thanks & Regards

    Siva - Dynamics GP MVP (2010 - 2012)

  • Sivakumar,

    This appears to work perfectly!  How exactly is it doing it?  I see that it is looking at the master db as well as temp and dynamics. 

    Ron

     

    Frank,

    What do you think about this?  Does it make sense to you?

    Ron

    Ron Wilson Real Life Dynamics User http://rldu.wordpress.com
  • Hi Ron

     Yes. This looks at the SQL Session ID's in the tables in the tempdb, DYNAMICS and the master databases to identify which GP's processes are being run by a specific user and note the inactivity for each user.

    Let me know if you need further assistance.

    Let us know if you need further assistance.

    Thanks & Regards

    Siva - Dynamics GP MVP (2010 - 2012)

  •  Sivakumar,

     Thanks...this is exactly what I needed.

     Ron

    Ron Wilson Real Life Dynamics User http://rldu.wordpress.com
  • You are welcome. :-)

    Feel free to post if you have further questions.

    Let us know if you need further assistance.

    Thanks & Regards

    Siva - Dynamics GP MVP (2010 - 2012)

  • Yes sir, Ron, it makes sense to me.  I didn't think about using the SQL session timing to do this.  Great idea Siva!!  Would you mind if I post this on my blog?

    Frank E. Hamelly, MCP, MCITP, MCT, MVP

    http://gp2themax.blogspot.com/

  • No problem Frank..

    You can... This should be a helpful script to all... :) Once you do it, I can cross link it on my blog... (if you don't mind). :)

    Let us know if you need further assistance.

    Thanks & Regards

    Siva - Dynamics GP MVP (2010 - 2012)

  • Man if I had a blog I would post it on it too :)...

    Ron Wilson Real Life Dynamics User http://rldu.wordpress.com
  • Sounds good Siva - thanks!!

     Well Ron, get busy! :)

    Frank E. Hamelly, MCP, MCITP, MCT, MVP

    http://gp2themax.blogspot.com/

  • You are welcome Frank. :)

    Have a nice day.

    Let us know if you need further assistance.

    Thanks & Regards

    Siva - Dynamics GP MVP (2010 - 2012)

  • Sivakumar,

    This post got me to thinking that if I had a blog I would post this...

    I now have a blog.  http://rldu.wordpress.com

    I have given you credit for this script!  Thanks so much...it is an AWESOME script...

    Ron Wilson Real Life Dynamics User http://rldu.wordpress.com
  • HI Sivakumar

    Could you please help me how can i create job to delete user that idle time for example more than 7 minutes