 |
 |
Archives of the TeradataForum
Message Posted: Wed, 03 Aug 2005 @ 19:09:45 GMT
| 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
| |
