Archives of the TeradataForum

Message Posted: Sat, 08 Mar 2014 @ 15:18:18 GMT

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

I wrote some SQL for this purpose a while ago, check out if it is of any help. It may need some adjustments:

     /*** Logons + IP ADDRESS + ClientUserID of a USER ***/ select  sub.YM
                 ,sub.UserName
                 ,sub.IPAddress
                 ,sub.TDPID
                 ,sub.ClientUserID
                 ,sub.ClientPRGRM
                 , count(*) as NumOfLogons
     from
       (
          SELECT
             TRIM (EXTRACT (YEAR FROM logdate)) || '-' ||
                       CASE WHEN EXTRACT (MONTH FROM logdate) <10
                       THEN '0' ELSE '' END || TRIM (EXTRACT (MONTH FROM logdate)) AS YM
             , UserName
             , substring (LogonSource FROM 15 FOR
                   INDEX (substring (LogonSource FROM 15),' ')-1)  as IPAddress
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource, IPAddress) + char_length ( IPAddress ) ) )  as TDPIDcont
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource,TDPIDCont) FOR INDEX (TDPIDcont, ' ')  ) )  as TDPID
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource, TDPID) + char_length ( TDPID ) ) )  as ClientProcessIDcont
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource,ClientProcessIDcont) FOR
                   INDEX (ClientProcessIDcont, ' ')  ) ) as ClientProcessID
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource, ClientProcessID) + char_length ( ClientProcessID ) ) )  as ClientUserIDcont
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource,ClientUserIDcont) FOR INDEX (ClientUserIDcont, ' ')  ) )  as ClientUserID
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource, ClientUserID) + char_length ( ClientUserID ) ) )  as ClientPRGRMcont
             , trim (substring (LogonSource FROM
                   INDEX (LogonSource,ClientPRGRMcont) FOR INDEX (ClientPRGRMcont, ' ')  ) )  as ClientPRGRM
             from dbc.logonoff
             where 1=1
               AND event = 'logon'
             --AND UserName IN ('username')
             --AND logdate BETWEEN '2013-01-01' AND '2013-04-01'
          ) sub
     group by 1,2,3,4,5,6
     order by 1 desc, 2,3,4,5,6
     ;

You'll find the complete list of elements logged in LogonSource in Data Dictionary doc: Chapter2: Data Dictionary Views: System Views Columns Reference

Regards,

Tomas

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