Archives of the TeradataForum

Message Posted: Wed, 23 Mar 2016 @ 20:39:13 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You can modify the following query to return a count.

-dave.clark

--------------------------------------------

The following select formats information from the system view DBC.LogOnOff and DBC.EventLog into a single line output for each session.

The select returns sessions logged on at any time during the specified interval. If the beginning and the end of the interval are equal, only sessions logged on at that instant will be returned. Note that some of the sessions returned may have logged on earlier than the beginning of the interval and may have logged off after the end of the interval.

This select does not find session that have not yet logged off. This is because it looks for sessions with both a logon and logoff event. Sessions still logged on will not have a logoff event in DBC.EventLog.

Here is some sample output (NOTE: this tends to generate a lot of output)

     User     Session Host    IFP    Logon                 Logoff
     -------- ------- ---- ------    --------              --------
     STV2        1260   52  16383   98/10/05 13:26:06.94   99/12/31 23:58:15.44
     DBC         1500   52  16383   98/10/05 15:05:49.57   99/12/31 23:58:15.52
     STV2        1740   52  16383   98/10/05 15:09:36.41   99/12/31 23:58:15.44


     -------------------------------------------------------------
     sel a.Username(title'User')(format'x(12)')
        ,a.SessionNo(title'Session')(format'zzzzzz9')
        ,a.LogicalHostID(title'Host')
        ,a.IFPNo(title'IFP')
        ,a.LogDate(title'Logon')
        ,a.LogTime(title' ')
        ,cast(b.LogDate as char(8))(title'Logoff')
        ,cast(b.LogTime as char(12))(title' ')
      from DBC.LogOnOff a, DBC.LogOnOff b
      where a.Event='Logon'
       and  b.Event<>'Logon'
       and  a.SessionNo=b.SessionNo
       and  a.IFPNo=b.IFPNo
       and  a.LogonDate=b.LogonDate
       and  a.LogonTime=b.LogonTime
     /* >>>>>>>>>>>>>>>>>> ending date and time */
       and ((a.LogDate= DATE and a.LogTime<= TIME)
            or a.LogDate< DATE)
     /* >>>>>>>>>>>>>>>>>> beginning date and time */
       and ((b.LogDate= DATE-1 and b.LogTime>= 120000)
            or b.LogDate> DATE-1)
     union
     sel UserName(title'User')(format'x(12)')
        ,SessionNo(title'Session')(format'zzzzzz9')
        ,LogicalHostID mod 1024 (title'Host')
        ,IFPNo(title'IFP')
        ,LogonDate(title'Logon')
        ,LogonTime(title' ')
        ,'  Logged'(title'Logoff')
        ,'On          '(title' ')
     from DBC.SessionInfo
     /* >>>>>>>>>>>>>>>>>>  ending date and time */
     where (LogonDate = DATE and LogonTime <= TIME)
        or  LogonDate < DATE
     order by 5,6,2;

	<Prev	Next>		<<First	<Prev	Next>	Last>>