Where Are You Listening?

Security is hard to ignore these days and for good reason. So, adding TLS to all IBM i applications is a logical step to take. For sure there are the well know ports, for which it is not hard to guess by which application they are used.

Well Known Ports

When dealing with IBM i, some of the TLS ports are well documented, like the IBM i Access ports but once in a while we just want to know which port is in use by a certain job/server. This is where the Navigator for i can be a help.

We can view the ports by accessing Navigator for i => Network => TCP/IP Configuration => Connections. When selecting a record, the options Jobs, Stop and Properties are available as you can see below:

Select the option Jobs, will bring up this screen:

When dealing with the known ports it is not so difficult to find the ports in use by an application, but if you are running several Web Services and HTTP servers with, or without, a Websphere Application Server behind them, finding the port becomes more complicated.

So, for this purpose we can “glue” the two SQL statements together when using the SQL button, nearly available on each page of Navigator for i.

The SQL button present on the Connections page, results in the following SQL statement:

SELECT A.CONNECTION_TYPE,
       CONNECTION_OPEN_TYPE,
       BIND_USER,
       IDLE_TIME,
       BYTES_RECEIVED_LOCALLY,
       BYTES_SENT_REMOTELY,
       CASE
           WHEN TCP_STATE IS NULL
               AND PROTOCOL = 'UDP' THEN '*UDP'
           ELSE TCP_STATE
       END AS TCP_STATE,
       CASE A.REMOTE_PORT
           WHEN '0' THEN '*'
           ELSE CHAR(A.REMOTE_PORT)
       END AS REMOTE_PORT,
       CASE A.REMOTE_ADDRESS
           WHEN '0.0.0.0' THEN '*'
           WHEN '0' THEN '*'
           WHEN '::' THEN '*'
           ELSE A.REMOTE_ADDRESS
       END AS REMOTE_ADDRESS,
       CASE A.LOCAL_PORT
           WHEN '0' THEN '*'
           ELSE CHAR(a.LOCAL_PORT)
       END AS LOCAL_PORT,
       CASE A.LOCAL_ADDRESS
           WHEN '0.0.0.0' THEN '*'
           WHEN '::' THEN '*'
           ELSE A.LOCAL_ADDRESS
       END AS LOCAL_ADDRESS, Job_name
    FROM QSYS2.NETSTAT_INFO  a inner join QSYS2.NETSTAT_JOB_INFO b on a.Local_port=b.LOCAL_PORT
    where job_name_short like :Enter_Search_string_for_Job_name
    LIMIT 100 OFFSET 0
    ```

When selecting the option “Jobs” as shown before on port 992, when pressing the SQL button again on that result page we get:

SELECT A.CONNECTION_TYPE, CONNECTION_OPEN_TYPE, BIND_USER, IDLE_TIME, BYTES_RECEIVED_LOCALLY,
       BYTES_SENT_REMOTELY,
       CASE
           WHEN TCP_STATE IS NULL AND PROTOCOL = 'UDP' THEN '*UDP'
           ELSE TCP_STATE
       END AS TCP_STATE,
       CASE A.REMOTE_PORT
           WHEN '0' THEN '*'
           ELSE CHAR(A.REMOTE_PORT)
       END AS REMOTE_PORT,
       CASE A.REMOTE_ADDRESS
           WHEN '0.0.0.0' THEN '*'
           WHEN '0' THEN '*'
           WHEN '::' THEN '*'
           ELSE A.REMOTE_ADDRESS
       END AS REMOTE_ADDRESS,
       CASE A.LOCAL_PORT
           WHEN '0' THEN '*'
           ELSE CHAR(a.LOCAL_PORT)
       END AS LOCAL_PORT,
       CASE A.LOCAL_ADDRESS
           WHEN '0.0.0.0' THEN '*'
           WHEN '::' THEN '*'
           ELSE A.LOCAL_ADDRESS
       END AS LOCAL_ADDRESS, Job_name
    FROM QSYS2.NETSTAT_INFO a
         INNER JOIN QSYS2.NETSTAT_JOB_INFO b
             ON a.Local_port = b.LOCAL_PORT
    WHERE job_name_short LIKE :Enter_Search_string_for_Job_name;

When running the statement a prompt is shown to enter the search string, as show below:

Using the LIKE for the prompt field, allows me to include the “%” to the left and right of the search string, in order to use “%ADMIN%’ to list all ports of jobs with “ADMIN” in their name.
If you prefer to use the fully qualified job name, just change the “LIKE” into “=” and you are good to go.

The Navigator for i is a great tool to enhance your SQL skills on systems with several HTTP servers and Web Services. This SQL statement stops you from having to browse the Web Administration for i HTTP server and look for the ports used there. In the end I think we all benefit from working more efficiently.