Auto logout of idle RTC sessions

Comments

• Options

  agentz Member Posts: 14

  2012-10-25

  the core functionality is below.
  i use this as part of an automated webservice which fires every 60 seconds but has a maxIdle param and an exempt param.
  
  i've checked with my vendor before running this in a live environment to ensure it would not orphan any records or corrupt any indexes and they assured me it is fine.
  
  you will need an sa account to execute kill and the other sql statement to get current users.
  
  this is how you find current sql nav users (sorry, remove the 'usersExempt...' junk):
  
  SqlCommand cmd=new SqlCommand("SELECT ISNULL(usersExemptFromKill.editedBy, '') as editedBy, s.[Connection ID], s.[User ID], s.[My Session], CONVERT(VARCHAR(12), s.[Login Date], 107) AS [Login Date], CONVERT(VARCHAR(12), s.[Login Time], 114) AS [Login Time], s.[Database Name], s.[Application Name], s.[Login Type], s.[Host Name], s.[CPU Time (ms)], s.[Memory Usage (KB)], s.[Physical I_O], s.Blocked, s.[Wait Time (ms)], s.[Blocking Connection ID], s.[Blocking User ID], s.[Blocking Host Name], s.[Blocking Object], s.[Idle Time] / 60000 AS [Idle Time] FROM usersExemptFromKill RIGHT OUTER JOIN Session AS s ON usersExemptFromKill.userID = s.[User ID] WHERE (s.[Application Name] LIKE '%Dynamics%') and usersExemptFromKill.editedBy is null and ((" + idleLimitMinutes.ToString() + " - s.[Idle Time] / 60000) <=0 ) ORDER BY CAST(s.[Idle Time] AS bigint) DESC");
  
  SqlDataAdapter da = new SqlDataAdapter(cmd);
  SqlConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings["NavConnectionString"].ConnectionString);
  System.Data.DataTable dt = new System.Data.DataTable("hitList");
  System.Data.DataView dv = new System.Data.DataView(dt);
  
  cmd.Connection = cn;
  cmd.CommandType = System.Data.CommandType.Text;
  
  cn.Open();
  da.Fill(dt);
  cn.Close();
  
  this is how you kill the user by the id
  Private Sub killMe(ByVal connectionID As String)
  
  Dim cn As New SqlConnection
  Dim cmd As New SqlCommand
  Dim cmdText As String = "kill " & connectionID
  Dim connectionString As String = WebConfigurationManager.ConnectionStrings.Item("NavConnectionString").ToString
  
  cn.ConnectionString = connectionString
  
  With cmd
  
  .Connection = cn
  .CommandText = cmdText
  .CommandType = CommandType.Text
  
  End With
  
  cn.Open()
  cmd.ExecuteNonQuery()
  cn.Close()
  
  
  End Sub

  0
• Options

  gycsiak Member Posts: 19

  2019-03-20

  Hi,
  
  Maybe this was working for 2009 R2, but do you also have a solution for 2017 as well?
  
  FYKI, Session table with Idle time field doesn't exist any more, from SQL master..sysprocesses I can only see Service sessions, but not client connections running through the service, so I cannot register nor query Idle time for client connections.
  
  Any suggestions where I could query the idle time for a client connection or do you have any information, where the service is storing and registering the idle time for a client, where it can be queried?
  
  Thanks in advance,
  
  BR,
  
  Gy.

  0
• Options

  wynner0 Member Posts: 8

  2019-03-21

  Why not use idle client timeout:
  https://blogs.msdn.microsoft.com/nav/2016/06/24/configuring-client-timeout/

  0