Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 03 Aug 2005 @ 19:09:45 GMT


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


Subj:   Re: Teradata connection from PERL
 
From:   Stubbs, Donald

You can look at the Data Dictionary Manual, Chapter 2 System Views, System Views Column Reference for the column Logonsource. Below is the SQL I use for V2R5. The version for Network Attached logons is clumsy, but it works. The MVS version was easier. Change the WHERE clause(s) to suit your needs.

     ------    MVS Attached Logons  ------------------------

      select op_sys             as op_sys
            ,jes_name           as jes_name
            ,mvs_job_name       as mvs_job_name
            ,sec_userid         as sec_userid
            ,tdp_name           as tdp_name
            ,util_name          as util_name
            ,min(logon_dtm)     as min_logon_dtm
            ,max(logoff_dtm)    as max_logoff_dtm
            ,count(*)           as nbr_sess
        from (
              SELECT username
                    ,cast(cast(logondate as date format 'yyyy-mm-dd') || ' ' ||
                          cast(logontime as format '99:99:99.99') as timestamp(2))  as logon_dtm
                    ,cast(cast(logdate   as date format 'yyyy-mm-dd') || ' ' ||
                          cast(logtime   as format '99:99:99.99') as timestamp(2))  as logoff_dtm
                    ,sessionno
                    ,trim(substring(logonsource from 01 for 8)) as op_sys
                    ,trim(substring(logonsource from 09 for 8)) as tdp_name
                    ,trim(substring(logonsource from 17 for 8)) as mvs_job_name
                    ,trim(substring(logonsource from 25 for 8)) as env_name
                    ,trim(substring(logonsource from 33 for 8)) as sec_userid
                    ,trim(substring(logonsource from 41 for 8)) as sec_group
                    ,trim(substring(logonsource from 49 for 8)) as util_name
                    ,trim(substring(logonsource from 57 for 8)) as coord_name
                    ,trim(substring(logonsource from 65 for 8)) as trans_id
                    ,trim(substring(logonsource from 73 for 8)) as term_id
                    ,trim(substring(logonsource from 81 for 8)) as operator_id
                    ,trim(substring(logonsource from 89 for 8)) as jes_name
                    ,logonsource as logonsource
                FROM dbc.dbclogonoff
               where substring(logonsource from 1 for 3) = 'MVS'
                 and trim(substring(logonsource from 97 for 8)) = '1101 LSS'
                 and logondate = date - 1
                 and event = 'logoff'
             )logonoff
       group by 1,2,3,4,5,6
       order by min_logon_dtm,util_name
          ;




     ---------  Network Attached Logons   ---------


     CREATE SET VOLATILE TABLE LOGONOFF_WORK_01, NO FALLBACK, NO LOG
     (
           UserName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           LogonDate DATE FORMAT 'YYYY-MM-DD' NOT NULL,
           LogonTime INTEGER FORMAT '99:99:99' NOT NULL,
           LogoffDate DATE FORMAT 'YYYY-MM-DD' NOT NULL,
           LogoffTime INTEGER FORMAT '99:99:99' NOT NULL,
           AccountName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           Event CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           SessionNo INTEGER FORMAT '--,---,---,--9' NOT NULL,
           ls_lgth INTEGER,
           ipaddr  VARCHAR(50),
           ipaddrend INTEGER,
           lgsrc02 VARCHAR(128),
           ls_lgth02 INTEGER,
           datasrcend INTEGER,
           datasrc VARCHAR(50),
           lgsrc03 VARCHAR(128),
           ls_lgth03 INTEGER,
           LogonSource VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX (LOGONDATE, LOGONTIME, LOGOFFDATE, LOGOFFTIME)
     ON COMMIT PRESERVE ROWS
     ;

      INSERT LOGONOFF_WORK_01
           (
             UserName
            ,LogonDate
            ,LogonTime
            ,LogoffDate
            ,LogoffTime
            ,AccountName
            ,Event
            ,SessionNo
            ,ls_lgth
            ,ipaddr
            ,ipaddrend
            ,lgsrc02
            ,ls_lgth02
            ,datasrcend
            ,datasrc
            ,lgsrc03
            ,ls_lgth03
            ,LogonSource
           )
      SELECT username
            ,logondate
            ,logontime (integer, format '99:99:99')
            ,logdate
            ,logtime   (integer, format '99:99:99')
            ,accountname
            ,event
            ,sessionno
            ,character_length(trim(logonsource))
     as ls_lgth
            ,case when 31 <= ls_lgth
                  then trim(substring(logonsource from 15 for
                       (position(' ' in substring(logonsource from 15 for 16) ) ) ))
                  when 15 <= ls_lgth
                  then (trim(substring(logonsource from 15 for ls_lgth - 14) ))
             end
     as ipaddr
            ,case when 31 <= ls_lgth
                  then (position(' ' in substring(logonsource from 15 for 16) ) ) + 14
                  when 15 <= ls_lgth
                  then ls_lgth
             end
     as ipaddrend
            ,case when ipaddrend < ls_lgth
                  then trim(substring(logonsource from ipaddrend
                            for ls_lgth - ipaddrend + 1) )
             end
     as lgsrc02
            ,case when ipaddrend < ls_lgth
                  then character_length(trim(lgsrc02))
             end
     as ls_lgth02
            ,case when ls_lgth02 is not null
                  then position(' ' in lgsrc02)
             end
     as datasrcend
            ,case when ls_lgth02 is not null
                  then trim(substring(lgsrc02 from 1 for datasrcend - 1))
             end
     as datasrc
            ,case when datasrcend < ls_lgth02
                  then trim(substring(lgsrc02 from datasrcend
                            for ls_lgth02 - datasrcend + 1) )
             end
     as lgsrc03
            ,case when datasrcend < ls_lgth02
                  then character_length(trim(lgsrc03))
             end
     as ls_lgth03
            ,logonsource
     as logonsource
        FROM dbc.logonoff
       where substring(logonsource from 1 for 8) = '(TCP/IP)'
         and event = 'logoff'
         and logondate between date - 14 and date
         and logontime between 070000 and 160000
        ;


     CREATE SET VOLATILE TABLE LOGONOFF_WORK_02, NO FALLBACK, NO LOG
     (
           UserName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           LogonDate DATE FORMAT 'YYYY-MM-DD' NOT NULL,
           LogonTime INTEGER FORMAT '99:99:99' NOT NULL,
           LogoffDate DATE FORMAT 'YYYY-MM-DD' NOT NULL,
           LogoffTime INTEGER FORMAT '99:99:99' NOT NULL,
           AccountName CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           Event CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           SessionNo INTEGER FORMAT '--,---,---,--9' NOT NULL,
           ls_lgth INTEGER,
           ipaddr  VARCHAR(50),
           ipaddrend INTEGER,
           lgsrc02 VARCHAR(128),
           ls_lgth02 INTEGER,
           datasrcend INTEGER,
           datasrc VARCHAR(50),
           lgsrc03 VARCHAR(128),
           ls_lgth03 INTEGER,
           netpidend INTEGER,
           netpid VARCHAR(50),
           lgsrc04 VARCHAR(128),
           ls_lgth04 INTEGER,
           cluserend INTEGER,
           cluser VARCHAR(50),
           lgsrc05 VARCHAR(128),
           ls_lgth05 INTEGER,
           utilend INTEGER,
           util VARCHAR(50),
           LogonSource VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX (LOGONDATE, LOGONTIME, LOGOFFDATE, LOGOFFTIME)
     ON COMMIT PRESERVE ROWS
     ;

      INSERT LOGONOFF_WORK_02
           (
             UserName
            ,LogonDate
            ,LogonTime
            ,LogoffDate
            ,LogoffTime
            ,AccountName
            ,Event
            ,SessionNo
            ,ls_lgth
            ,ipaddr
            ,ipaddrend
            ,lgsrc02
            ,ls_lgth02
            ,datasrcend
            ,datasrc
            ,lgsrc03
            ,ls_lgth03
            ,netpidend
            ,netpid
            ,lgsrc04
            ,ls_lgth04
            ,cluserend
            ,cluser
            ,lgsrc05
            ,ls_lgth05
            ,utilend
            ,util
            ,logonsource
           )
      SELECT username
            ,logondate
            ,logontime (integer, format '99:99:99')
            ,logoffdate
            ,logofftime   (integer, format '99:99:99')
            ,accountname
            ,event
            ,sessionno
            ,ls_lgth
            ,ipaddr
            ,ipaddrend
            ,lgsrc02
            ,ls_lgth02
            ,datasrcend
            ,case when datasrc is null
                  then null
                  when substring(datasrc from 1 for 1) between '0' and '9'
                  then ' '
                  else datasrc
             end
     as datasrc
            ,lgsrc03
            ,ls_lgth03

            ,case when ls_lgth < 47
                  then null
                  when datasrc is null
                  then null
                  when substring(datasrc from 1 for 1) between '0' and '9'
                  then datasrcend
                  when ls_lgth03 is not null
                  then position(' ' in lgsrc03)
             end
     as netpidend
            ,case when ls_lgth < 30
                  then null
                  when ls_lgth between 30 and 47
                  then datasrc
                  when datasrc is null
                  then null
                  when substring(datasrc from 1 for 1) between '0' and '9'
                  then datasrc
                  when ls_lgth03 is not null
                  then trim(substring(lgsrc03 from 1 for netpidend - 1))
                  else null
             end
     as netpid
            ,case when ls_lgth < 47
                  then null
                  when datasrc is null
                  then null
                  when substring(datasrc from 1 for 1) between '0' and '9'
                  then lgsrc03
                  when netpidend < ls_lgth03
                  then trim(substring(lgsrc03 from netpidend
                            for ls_lgth03 - netpidend + 1) )
             end
     as lgsrc04
            ,case when ls_lgth < 47
                  then null
                  when datasrc is null
                  then null
                  when substring(datasrc from 1 for 1) between '0' and '9'
                  then ls_lgth03
                  when netpidend < ls_lgth03
                  then character_length(trim(lgsrc04))
             end
     as ls_lgth04

            ,case when ls_lgth < 47
                  then null
                  when ls_lgth04 is null
                  then null
                  when ls_lgth04 is not null
                  then position(' ' in lgsrc04)
             end
     as cluserend
            ,case when ls_lgth < 47
                  then null
                  when ls_lgth04 is null
                  then null
                  when ls_lgth04 is not null
                  then trim(substring(lgsrc04 from 1 for cluserend - 1))
             end
     as cluser
            ,case when ls_lgth < 47
                  then null
                  when ls_lgth04 is null
                  then null
                  when cluserend < ls_lgth04
                  then trim(substring(lgsrc04 from cluserend
                            for ls_lgth04 - cluserend + 1) )
             end
     as lgsrc05
            ,case when ls_lgth < 47
                  then null
                  when ls_lgth04 is null
                  then null
                  when cluserend < ls_lgth04
                  then character_length(trim(lgsrc05))
             end
     as ls_lgth05

            ,case when ls_lgth < 47
                  then null
                  when ls_lgth05 is null
                  then null
                  when ls_lgth05 is not null
                  then position(' ' in lgsrc05)
             end
     as utilend
            ,case when ls_lgth < 47
                  then null
                  when ls_lgth05 is null
                  then null
                  when ls_lgth05 is not null
                  then trim(substring(lgsrc05 from 1 for utilend - 1))
             end
     as util

            ,LogonSource
     as logonsource
        from LOGONOFF_WORK_01
        ;

      select UserName
            ,LogonDate
            ,LogonTime
            ,LogoffDate
            ,LogoffTime
            ,AccountName
            ,Event
            ,SessionNo
            ,ipaddr
            ,datasrc
            ,netpid
            ,cluser
            ,util
            ,LogonSource
        from LOGONOFF_WORK_02
       order by logondate, logontime
       ;

Don Stubbs
Teradata Certified Master



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019