none
report to get the end session date and time on SQL

    Question

  • I created the query to get the logs details while someone login to SQL using instance name. Login session provides me the details but there are no end session details. When the user logs out from SQL those details were not able to capture. 

    Can you please help with this query?

    SELECT es.[session_id]
    --,sd.name
          ,[login_time]
    	   ,[login_name]
          ,[host_name] as RemoteUser
    	   ,ec.[client_net_address] as RemoteClientPC
          ,ec.[client_tcp_port] as RemoteClientPCPort
          ,ec.[local_net_address] as ServerIP
          ,ec.[local_tcp_port] as ServerIPPort
          ,[program_name]
               ,[status]
          ,[original_login_name]
      -- ,ec.[most_recent_session_id]
          ,ec.[connect_time]
          ,ec.[net_transport]
             ,ec.[last_read]
               
      FROM [master].[sys].[dm_exec_sessions] es
        left join [master].[sys].[dm_exec_connections] ec on ec.[session_id]=es.[session_id] 
    	--inner join sys.databases sd on sd.owner_sid=es.security_id
     -- where [program_name]='Microsoft SQL Server Management Studio - Query'
      order by es.[session_id] desc


    Afzalkhan


    • Edited by AfzalKhanBH Wednesday, May 09, 2018 6:44 AM
    Wednesday, May 09, 2018 6:43 AM

All replies

  • See an example of logon trigger

    https://dba.stackexchange.com/questions/50620/how-to-create-login-and-logout-trigger-for-client-audit

    create table
    stats$user_log
    (
    user_id varchar2(30),
    session_id number(8),
    host varchar2(30),
    last_program varchar2(48),
    last_action varchar2(32),
    last_module varchar2(32),
    logon_day date,
    logon_time varchar2(10),
    logoff_day date,
    logoff_time varchar2(10),
    elapsed_minutes number(8)
    )
    ;
    create or replace trigger
    logon_audit_trigger
    AFTER LOGON ON DATABASE
    BEGIN
    insert into stats$user_log values(
    user,
    sys_context('USERENV','SESSIONID'),
    sys_context('USERENV','HOST'),
    null,
    null,
    null,
    sysdate,
    to_char(sysdate, 'hh24:mi:ss'),
    null,
    null,
    null
    );
    END;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, May 09, 2018 7:26 AM
  • you can also get this information from SQLServer Audit Login Logout event classes

    But its not always correct as due to connection pooling the value may be reported higher sometimes


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, May 09, 2018 7:51 AM
  • this query does not work on SQL 2012 Standard edition as well as SQL 2017 develop edition.

    Any other solution?


    Afzalkhan

    Wednesday, May 09, 2018 8:06 AM
  • SQL 2012 STD edition does not have audit features.

    Do you have some other solution?

    Thanks in advance.


    Afzalkhan

    Wednesday, May 09, 2018 8:07 AM
  • SQL 2012 STD edition does not have audit features.

    I guess that Trace is what is left for you. That is you need to have server-side trace running that captures login and logout events.

    Wednesday, May 09, 2018 9:46 PM
  • Cannot you use Logon triggers on STD edition?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 10, 2018 5:16 AM
  • Cannot you use Logon triggers on STD edition?

    Uri, there were two issues with your post:

    1) You had incorrect syntax in your post. :-)
    2) Login triggers does not address Afzal's problem - to get when the session ended.

    Thursday, May 10, 2018 8:42 AM
  • what will be the solution if I want to trace the user logged to SQL and end the session.?

    Afzalkhan

    Thursday, May 10, 2018 8:48 AM
  • what will be the solution if I want to trace the user logged to SQL and end the session.?

    Afzalkhan

    As far as I know using extended events is the other option

    Its available in Standard Edition


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, May 10, 2018 9:34 AM
  • Thank you for the information. Its really help.

    But I want to create the report in SSRS to retrieve the information from SQL that user connect to SQL and do something and then user disconnect the SQL connection.

    I want to capture in the report user login time and date and logout out with user details.


    Afzalkhan

    Thursday, May 10, 2018 9:53 AM
  • Thank you for the information. Its really help.

    But I want to create the report in SSRS to retrieve the information from SQL that user connect to SQL and do something and then user disconnect the SQL connection.

    I want to capture in the report user login time and date and logout out with user details.


    Afzalkhan

    For that you need to extract the required information from your extended event target xml and use it as source for your SSRS report

    see

    https://www.brentozar.com/archive/2015/01/query-extended-events-target-xml/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, May 10, 2018 10:10 AM
  • As far as I know using extended events is the other option

    I don't think so. Extended Events is quite meagre on auditing events, in favour of SQL Audit. (They do use the same mechanism under the hood.)

    Thursday, May 10, 2018 11:39 AM
  • But I want to create the report in SSRS to retrieve the information from SQL that user connect to SQL and do something and then user disconnect the SQL connection.

    You can query the trace data with sys.fn_trace_gettable.

    Thursday, May 10, 2018 11:40 AM
  • Thanks but <g class="gr_ gr_33 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="33" id="33">i</g> tried to execute sys.fn_trace_gettable but show no parameter found <g class="gr_ gr_50 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="50" id="50">error</g>.

    Afzalkhan

    Monday, May 14, 2018 8:39 AM
  • 2012 STD Ed *does* have server audit and server audit specifications. It is just Database audit specifications that is is lacking.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, May 15, 2018 8:54 AM