Get IP address for QZDASOINIT/QZDASSINIT jobs?

[Hal]

All -

I have a database exit program running on V5R4M5 that checks a table with names in it to ensure that only authorized people can make an ODBC connection to our box.

That program has worked well for about 2 years, but now I have need to track the date, time and IP address of each database connection. Previously I did not log any information.

So I created a table, with 5 columns. I have an identity column, timestamp, username, IP address, and success/fail indicator.

I have everything logging as of this writing except the IP address, and I don't know how to get to it. I thought I could call the QDCRDEVD API from within my CL program, but it does not work because the device (&DEVICE) returns QZDASSINIT, and the API apparently doesn't know how to get the IP address from that. I am using the DEVD0600 format.

The interesting part is, if you look at the log at the start of the database connection, the IP address is in there:

Job 342108/QUSER/QZDASSINIT started on 10/07/13 at 15:49:46 in subsystem
QSERVER in QSYS. Job entered system on 10/07/13 at 15:49:46.
User BLAHBLAH from client 1.2.3.4 connected to server.
SAVE DATA operation completed successfully.
Member OUTTABLE file OUTTABLE opened with no sharing allowed.
Member OUTTABLE file OUTTABLE in DBCONNS opened.
Member OUTTABLE file OUTTABLE in DBCONNS closed.

So the question I have is, from my exit program registered against QIBM_QZDA_INIT, how can I get the IP address of the user who is connecting into a character variable so I can put it into my logging table?

Any advice is appreciated.

Thanks,
Hal

[CRPence]

On 07 Oct 2013 15:58, Hal wrote:
>
> I have a database exit program running on V5R4M5 that checks a table
> with names in it to ensure that only authorized people can make an
> ODBC connection to our box.
>
> That program has worked well for about 2 years, but now I have need
> to track the date, time and IP address of each database connection.
> Previously I did not log any information.
>
> So I created a table, with 5 columns. I have an identity column,
> timestamp, username, IP address, and success/fail indicator.
>
> I have everything logging as of this writing except the IP address,
> and I don't know how to get to it. I thought I could call the
> QDCRDEVD API from within my CL program, but it does not work because
> the device (&DEVICE) returns QZDASSINIT, and the API apparently
> doesn't know how to get the IP address from that. I am using the
> DEVD0600 format.
>
> The interesting part is, if you look at the log at the start of the
> database connection, the IP address is in there:
>
> Job 342108/QUSER/QZDASSINIT started on 10/07/13 at 15:49:46 in subsystem
> QSERVER in QSYS. Job entered system on 10/07/13 at 15:49:46.
> User BLAHBLAH from client 1.2.3.4 connected to server.
> SAVE DATA operation completed successfully.

> <<SNIP>>

>
> So the question I have is, from my exit program registered against
> QIBM_QZDA_INIT, how can I get the IP address of the user who is
> connecting into a character variable so I can put it into my logging
> table?
>
> Any advice is appreciated.

Has the Retrieve Thread Attribute (QWTRTVTA) API been investigated
for its key "326 CHAR(45) Client IP address - IPv4 or IPv6"? Seems
likely to be the best given the following link has the following quote
in its text under the title and major headings [shown delimited with
underscores]:
_i Can … Identify Your Server Jobs_
http://ibmsystemsmag.blogs.com/i_can/2009/12/
"...
_Miscellaneous stuff_
...
The operating system stores the IP address implicitly for all server
jobs. The Retrieve Thread Attributes API returns the IP address for the
thread (if applicable). ...
..."

A less desirable means [excessive work, indirect means] is discussed
here; found searching the web for the exit-point name and "IP address":
http://www.itjungle.com/fhg/fhg022311-printer01.html
_i Secure DB2 for i Database Server Access by IP Address i_
"Published: February 23, 2011
... RPG Exit Program Sample DBSRVEXTR ..."

Give the message is in the joblog, there are also [also IMO
undesirable] joblog API(s) and message-receiving techniques that could
be utilized in order to obtain the message data from the message that is
logged to identify the user and the IP address; e.g. the CPIAD02 "User
&1 from client &2 connected to server." shown in the included joblog
snippet. As well there is likely the same information in the history
[an API provides access to QHST message data] and\or auditing though
likely not as easily tied to the job and a /job start/ given they are
prestart jobs that can be reused.

--
Regards, Chuck

[CRPence]

On 07 Oct 2013 16:58, CRPence wrote:
><<SNIP>>
> Given the message is in the joblog, there are also [also IMO

> undesirable] joblog API(s) and message-receiving techniques that
> could be utilized in order to obtain the message data from the
> message that is logged to identify the user and the IP address; e.g.
> the CPIAD02 "User &1 from client &2 connected to server." shown in
> the included joblog snippet. As well there is likely the same
> information in the history [an API provides access to QHST message
> data] and\or auditing though likely not as easily tied to the job and
> a /job start/ given they are prestart jobs that can be reused.
>

Oh! Also, the DSPJOBLOG OUTPUT(*OUTFILE) allowed for the current job
[as requested by the exit-program for the QIBM_QZDA_INIT exit point] is
probably a less negative option than some others, with regard to
utilizing the message data of the CPIAD02 logged to the joblog. This
could even be limited [like an API] to only the first several messages
[using the SIZE() settings on CHGPF]; be aware that some messages
effected by things like CHGJOB and STRSRVJOB [or perhaps some internal
processing for the server job] could appear earlier than the
desired\searched message.

--
Regards, Chuck

[dinob...@gmail.com]

Hi Hal,
have you tried the API QUSRJOBI - Retrieve Job Information ???

This API will return the client IP address for single-threaded server jobs. This was available beginning with V5R1.

Regards DinoB