IBM i Global

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Thomas, I'm not sure about the audit services, but one could easily write a CL that looped every 30 seconds or so and did an SQL query to NETSTAT_INFO view  something like

SELECT CONNECTION_TYPE, REMOTE_ADDRESS, PROTOCOL, TCP_STATE FROM QSYS2.NETSTAT_INFO  WHERE LOCAL_PORT = 446;

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Thomas, I'm not sure about the audit services, but one could easily write a CL that looped every 30 seconds or so and did an SQL query to NETSTAT_INFO view  something like

SELECT CONNECTION_TYPE, REMOTE_ADDRESS, PROTOCOL, TCP_STATE FROM QSYS2.NETSTAT_INFO  WHERE LOCAL_PORT = 446;

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Thomas, I'm not sure about the audit services, but one could easily write a CL that looped every 30 seconds or so and did an SQL query to NETSTAT_INFO view  something like

SELECT CONNECTION_TYPE, REMOTE_ADDRESS, PROTOCOL, TCP_STATE FROM QSYS2.NETSTAT_INFO  WHERE LOCAL_PORT = 446;

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

If you don't just want a current snapshot but would rather take over a certain time period then you will need to run a comm trace.

WRKLIND                                              
STRCMNTRC CFGOBJ(LANLINSYS) CFGTYPE(*LIN) MAXSTG(32M)

wait

ENDCMNTRC CFGOBJ(LANLINSYS) CFGTYPE(*LIN)

PRTCMNTRC CFGOBJ(LANLINSYS) CFGTYPE(*LIN) OUTPUT(*PRINT)  
          OUTFILE(ROB/COMMTRACE) FMTTCP(*YES) SLTPORT(446)

Change the port

There's an *OUTFILE option but the SCDATA field looks encoded and I didn't want to take the time to figure that out. 

When done

DLTCMNTRC CFGOBJ(LANLINSYS) CFGTYPE(*LIN)

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

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:

1) exit program

2) look thru history log :  DSPLOG msgid(CPI3E34) job(QRWT*)

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

That's a good catch Bryan.  Based on that, this is another way of capturing that.

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;

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:

1) exit program

2) look thru history log :  DSPLOG msgid(CPI3E34) job(QRWT*)

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

thanks Rob,

to carry this one more step  I use the following to find DRDA and ODBC like connections.

-- category: bryandietz
-- 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

;

at one point, I sub stringed(or maybe LOCATE_in_STRING) out the IP address, but I cannot locate the example

That's a good catch Bryan.  Based on that, this is another way of capturing that.

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;

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:

1) exit program

2) look thru history log :  DSPLOG msgid(CPI3E34) job(QRWT*)

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Bryan,

The IP address would be easier to find if you search MESSAGE_TOKENS instead of MESSAGE_TEXT as it would be in a fixed position in tokens. (as would YOUR_USER).  Then again, this may only apply if you're looking for a single message id, or two with identical tokens.  Adding the CASE would probably work...

Clean this up and you're in

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;

thanks Rob,

to carry this one more step  I use the following to find DRDA and ODBC like connections.

-- category: bryandietz
-- 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

;

at one point, I sub stringed(or maybe LOCATE_in_STRING) out the IP address, but I cannot locate the example

That's a good catch Bryan.  Based on that, this is another way of capturing that.

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;

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:

1) exit program

2) look thru history log :  DSPLOG msgid(CPI3E34) job(QRWT*)

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Bryan,

The IP address would be easier to find if you search MESSAGE_TOKENS instead of MESSAGE_TEXT as it would be in a fixed position in tokens. (as would YOUR_USER).  Then again, this may only apply if you're looking for a single message id, or two with identical tokens.  Adding the CASE would probably work...

Clean this up and you're in

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;

thanks Rob,

to carry this one more step  I use the following to find DRDA and ODBC like connections.

-- category: bryandietz
-- 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

;

at one point, I sub stringed(or maybe LOCATE_in_STRING) out the IP address, but I cannot locate the example

That's a good catch Bryan.  Based on that, this is another way of capturing that.

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;

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:

1) exit program

2) look thru history log :  DSPLOG msgid(CPI3E34) job(QRWT*)

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

------------------------------
Thomas Varkey
------------------------------

Hello team,

Is there any way to generate an audit report of IPs accessing IBMi via port 446. Your views would be appreciated.

Thank you

------------------------------
Thomas Varkey
------------------------------