1) exit program
2) look thru history log : DSPLOG msgid(CPI3E34) job(QRWT*)
CPI3E34 DDM job xxxx servicing user yyy on mm/dd/yy at hh:mm:ss (This can be suppressed with QRWOPTIONS)
- 3) History of connections to IBM i
https://www.ibm.com/support/pages/node/6212238 - https://community.ibm.com/community/user/power/discussion/auditting-ips-accessing-ibmi-via-port-446
Port 446 is the DRDA port, QRWTLSTN is the job that is listening on that port, so a couple of ways I can think of:
-- category:Robert Berendt
select *
FROM TABLE (QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT DATE - 2 days
)) AS X
Where message_id='CPI3E34'
and from_job_name like 'QRWT%'
ORDER BY ORDINAL_POSITION desc;
-- category: bryandietz
-- find DRDA and ODBC like connections
-- description: history log-find user from QZDASOINIT-QRWTSRVR
SELECT Message_Timestamp
,From_User
,From_Job
,Message_Id
,MESSAGE_TEXT
FROM TABLE(Qsys2.History_Log_Info(
Start_Time => current_timestamp - 1 day, -- pick your time frame
End_Time => current_timestamp
)) i
WHERE Message_Id in ('CPIAD09','CPI3E34')
-- AND MESSAGE_TEXT LIKE '%YOUR_USER%' -- if needing to "audit" for a single user
;
-- find ip from message_tokens
-- category: Robert Berendt
select trim(substring(message_tokens, 75, 15)) as IP_address, x.*
FROM TABLE (QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT DATE - 2 days
)) AS X
Where message_id='CPI3E34'
and from_job_name like 'QRWT%'
ORDER BY ORDINAL_POSITION desc;
-- find IP
-- category: bryandietz
-- find DRDA and ODBC like connections
-- description: history log-find user from QZDASOINIT-QRWTSRVR
SELECT Message_Timestamp
,From_User
,From_Job
,Message_Id
,MESSAGE_TEXT
,TRIM(SUBSTR(Message_Text,(LOCATE_IN_STRING(Message_Text, 'client', 1)+7), -- start of IP
(LOCATE_IN_STRING(Message_Text, ' connected', 1) -
(LOCATE_IN_STRING(Message_Text, 'client ', 1)+7) -- end of IP address
))) AS IP_addr
FROM TABLE(Qsys2.History_Log_Info(
Start_Time => current_timestamp - 1 day, -- pick your time frame
End_Time => current_timestamp
)) i
WHERE Message_Id in ('CPIAD09','CPI3E34')
-- AND MESSAGE_TEXT LIKE '%YOUR_USER%' -- if needing to "audit" for a single user
;
