Archives of the TeradataForum

Message Posted: Wed, 06 Apr 2005 @ 07:57:20 GMT

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

How about the DBC.SessionInfo view? There's a row for each currently logged on session. Then, there's the DBC.LogOnOff view. There's a row for each "event." The events are logon, logoff, and various other ways a session can end. By matching starting to ending events you can get the beginning and ending time for each session one the session ends (there's no ending row until the session logos off).

And example might help here. This is a macro I install in DBC. I install it in DBC because it selects from DBC tables. If I install it somewhere else, I have to grant some rights to the place I install to.

     replace macro WhoWasOn2
      (D1 date  DEFAULT DATE
      ,T1 float DEFAULT TIME
      ,D2 date  DEFAULT DATE
      ,T2 float DEFAULT TIME)
      AS
     (
     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
       and ((a.LogDate = :D2 and a.LogTime <= :T2)
            or a.LogDate < :D2)
       and ((b.LogDate = :D1 and b.LogTime >= :T1)
            or b.LogDate > :D1)

     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
     where (LogonDate = :D2 and LogonTime <= :T2)
        or  LogonDate < :D2

     order by 5,6,2;
     );

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