Troubleshooting
Problem
This note documents how to track an IP address for invalid sign-on attempts for current releases of the IBM i Operating System.
Resolving The Problem
How do I track IP address of invalid sign-on attempts?
Following is an example: If a user signed on with invalid attempts and you want to find IP address of the PC.
Following is an example: If a user signed on with invalid attempts and you want to find IP address of the PC.
Method 1
Using the SQL Service QSYS2.DISPLAY_JOURNAL() to create a report that contains invalid sign-on attempts for users.
Authorization:
- You must have *USE authority to the journal and to all requested journal receivers.
- *OBJEXIST authority is required to the journal if object-name is omitted or if object-name specifies an object that no longer exists.
- If object-name is *ALL, you must be authorized to every object associated with a journal entry.
Note: This method is only available on R720 and later versions of the Operating System. For older releases, follow Method 2.
Using the ACS "Run SQL Scripts" tool, run the following SQL Statement:
SELECT JOB_NAME,
job_user,
DATE(entry_timestamp) AS entry_date,
TIME(entry_timestamp) AS entry_time,
SUBSTR(CAST(entry_data AS CHAR(2048)), 12, 22) AS device_name,
SUBSTR(CAST(entry_data AS CHAR(2048)), 1, 1) AS entry_type,
SUBSTR(CAST(entry_data AS CHAR(2048)), 2, 10) AS user_ID,
remote_address
FROM TABLE (
QSYS2.DISPLAY_JOURNAL(
JOURNAL_LIBRARY => 'QSYS',
JOURNAL_NAME => 'QAUDJRN',
STARTING_RECEIVER_NAME => '*CURCHAIN', JOURNAL_ENTRY_TYPES => 'PW', STARTING_TIMESTAMP => '2020-11-20 00:00:01',
ENDING_TIMESTAMP => '2020-11-25 06:30:00')
)
Note: This SQL Statement might not work on the STRSQL command-line interface if your job has a different CCSID than the code page as specified on the 5250 Emulation Session.
Note 2: Modify the time and date on the SQL to the wanted one.
Note 3: If Hexadecimal data is displayed in the device_name, entry_type, user_ID fields, you need to change the JDBC connection properties to translate the CCSID to 65535. In the Run SQL Scripts tool, select Edit > JDBC Configurations... > Edit > "Translation" tab and click Translate CCSID 65535. Save the connection and reconnect to run the SQL.
The command creates a report with user profiles that attempted to Signon with an invalid password for the current chain of journal receivers attached to the QAUDJRN and going back to the time and date specified on the SQL.
Note: The remote IP address may not always be available to the Operating System at the time the Invalid Signon Attempt occurred, if that is the case, the remote address field is blank.
Method 2:
Tracking IP address at R540 or later releases.
Auditing Standard Field (QASYPWJ5) Record format Type 5). Refer to the Security Reference manual Appendix F for information on the data that is captured for the PW audit type.
The Security Reference Manual can be obtained on the following link:
Security Reference Manual
Step 1: On the operating system command line, type the following commands and press the Enter key.
CPYAUDJRNE ENTTYP(PW) OUTFILE(yourlib/QAUDIT)
Note: The command above searches for PW entries in the current receiver attached to your auditing journal. If you are not finding entries you need, the journal receiver may have been recently detached. To obtain the needed information, you may want to query the whole receiver chain instead, specifying a time period for the search:
CPYAUDJRNE ENTTYP(PW) JRNRCV(*CURCHAIN) FROMTIME('date' 'time') TOTIME('date' 'time') OUTFILE(yourlib/QAUDIT)
Note: Where yourlib is the name of the library where the file is to be created
Special Note: Run Step 2 or Step 3:
Step 2: On the operating system command line, type the following commands and press the Enter key.
STRSQL to get to the interactive SQL command line. Then type the following:
Step 1: On the operating system command line, type the following commands and press the Enter key.
CPYAUDJRNE ENTTYP(PW) OUTFILE(yourlib/QAUDIT)
Note: The command above searches for PW entries in the current receiver attached to your auditing journal. If you are not finding entries you need, the journal receiver may have been recently detached. To obtain the needed information, you may want to query the whole receiver chain instead, specifying a time period for the search:
CPYAUDJRNE ENTTYP(PW) JRNRCV(*CURCHAIN) FROMTIME('date' 'time') TOTIME('date' 'time') OUTFILE(yourlib/QAUDIT)
Note: Where yourlib is the name of the library where the file is to be created
Special Note: Run Step 2 or Step 3:
Step 2: On the operating system command line, type the following commands and press the Enter key.
STRSQL to get to the interactive SQL command line. Then type the following:
SELECT PWJOB,
PWUSER,
PWNBR,
PWPGM,
PWUSPF,
PWRPORT,
PWRADR,
PWESDL,
PWTYPE,
PWUSRN,
PWTSTP
FROM yourlib/qauditpw
Note: Where yourlib is the name of the library where the file was created
Step 3: Do the following:
| 1) | On the operating system command line, type the STRQRY command and press the Enter key. |
| 2) | Select Option 1, Work with queries from the Query Utilities display, and press the Enter key. |
| 3) | Select Option 1, Create from the Work with Queries display, and press the Enter key. |
| 4) | Type a query and library. |
| 5) | Select Option 1, Specify file selections from the Define the Query display, and press the Enter key. |
| 6) | Type the file name of QAUDITPW and library of YOURLIB on the Specify File Selections display. |
| 7) | Select additional options, if required, or press F3 to save or run the query from the Define the Query display. |
| 8) |
Select Save definition = Y and choose option 1 to run interactively or 2 to run in batch from the Exit this Query display. The Remote address and port can now be shown.
Note: If you are going to run it in batch, you need to specify a library other than QTEMP on the cpyaudjrne command in Step 1. Also use that same library in Step 3f. |
Tracking IP address at R520 and R530
Auditing Standard Field (QASYPWJ5) Record format Type 5 (at R520 and R530). Refer to the Security Reference manual for V5R2 Appendix F.
Step 1: On the operating system command line, type the following commands and press the Enter key.
CRTLIB TESTLIB
CRTDUPOBJ OBJ(QADSPJR5) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(TESTLIB) NEWOBJ(IP)
DSPJRN JRN(QAUDJRN) ENTTYP(PW) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE5) OUTFILE(TESTLIB/IP)
Special Note: Run Step 2 or Step 3:
Step 2: On the operating system command line, type the following commands and press the Enter key.
STRSQL to get to the interactive SQL command line. Then type the following:
SELECT PWJOB,
PWUSER,
PWNBR,
PWPGM,
PWUSPF,
PWRPORT,
PWRADR,
PWESDL,
PWTYPE,
PWUSRN,
PWTSTP
FROM TESTLIB/IP
Step 3: Do the following:
| 1) | On the operating system command line, type the STRQRY command and press the Enter key. |
| 2) | Select Option 1, Work with queries from the Query Utilities display, and press the Enter key. |
| 3) | Select Option 1, Create form the Work with Queries display, and press the Enter key. |
| 4) | Type a query and library. |
| 5) | Select Option 1, Specify file selections from the Define the Query display, and press the Enter key. |
| 6) | Type the file of IP and library of TESTLIB on the Specify File Selections display. |
| 7) | Select additional options, if required, or press F3 to save or run the query from the Define the Query display. |
| 8) |
Select Save definition = Y and choose option 1 to run interactively or 2 to run in batch from the Exit this Query display. The Remote address and port can now be shown.
|
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]
Historical Number
329133089
Was this topic helpful?
Document Information
Modified date:
12 April 2022
UID
nas8N1016247