Welcome to the new home of PowerWire – PowerWire.uk

Update your bookmarks now!

Informing the IBM Community

Where Are You Listening?

5
(3)

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-know Ports

ServicePortFunction
HTTP80Web traffic
HTTPS443Secure web traffic
FTP20 & 21File transfer
DNS53Name resolution
SMTP25Internet mail
POP3110Post Office Protocol (POP) mailbox
IMAP143Internet message access protocol (IMAP) mailbox
Telnet23Remote login
SSH22Secure remote login


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:

SQL

-- category: System Management
-- description: Query connections and Jobs 
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
    
SQL

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

SQL
 
 select job_name,
   local_port,
   local_address,
   remote_port,
   remote_address,
  from qsys2.netstat_job_info
  where local_port = '992'
   
SQL

When we use some SQL skills we end up with an SQL statement, which allows us to use part of the job name in order to discover which ports are in use:

SQL

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;
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;
SQL


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.

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 3

No votes so far! Be the first to rate this post.


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *