Question Status

Unanswered
Ron Wilson asked a question on 16 Feb 2010 6:13 AM

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
Reply
Sivakumar Venkataraman responded on 16 Feb 2010 6:28 AM

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)

Reply
Ron Wilson responded on 16 Feb 2010 6:39 AM

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
Reply
Sivakumar Venkataraman responded on 16 Feb 2010 7:01 AM

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)

Reply
Ron Wilson responded on 16 Feb 2010 7:06 AM

 Sivakumar,

 Thanks...this is exactly what I needed.

 Ron

Ron Wilson Real Life Dynamics User http://rldu.wordpress.com
Reply
Sivakumar Venkataraman responded on 16 Feb 2010 7:09 AM

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)

Reply
Frank Hamelly responded on 16 Feb 2010 7:44 AM

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?

** Please, if this answers your question, mark it as 'Answered' so others experiencing the same will know it resolved your issue. **

Frank E. Hamelly, MCP-GP, MCP-AX, MCITP, MCT, MVP

http://gp2themax.blogspot.com/

Reply
Sivakumar Venkataraman responded on 16 Feb 2010 7:50 AM

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)

Reply
Ron Wilson responded on 16 Feb 2010 7:55 AM

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

Ron Wilson Real Life Dynamics User http://rldu.wordpress.com
Reply
Frank Hamelly responded on 16 Feb 2010 8:47 AM

Sounds good Siva - thanks!!

 Well Ron, get busy! :)

** Please, if this answers your question, mark it as 'Answered' so others experiencing the same will know it resolved your issue. **

Frank E. Hamelly, MCP-GP, MCP-AX, MCITP, MCT, MVP

http://gp2themax.blogspot.com/

Reply
Sivakumar Venkataraman responded on 16 Feb 2010 9:34 AM

You are welcome Frank. :)

Have a nice day.

Let us know if you need further assistance.

Thanks & Regards

Siva - Dynamics GP MVP (2010 - 2012)

Reply
Ron Wilson responded on 24 Mar 2010 5:02 PM

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
Reply
Atef abdel monem responded on 15 Oct 2012 4:53 AM

HI Sivakumar

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

Reply