AS400 Database Journaling

Panchux
Panchux used Ask the Experts™
on
We have an AS400 with an OS400 V5R3 OS.
 
I want to implement database journaling to log and match every access from a console to it's ip address.

Hope AS400 gurus here would point me in the right direction,

Pancho
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Pancho,

This IBM document is interesting. You should understand the basic of database journaling:
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/books/c415302515.htm

Another link to help:
http://systeminetwork.com/article/extracting-information-qaudjrn

This last one is under V5R4 but could be 'usable' under V5R3:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/cl/cpyaudjrne.htm

Read them and let us know if you have more specific questions...

Lewis

Author

Commented:
Today I implemented db journaling on an old as400 (V4R3 OS I think)

I was able to log a lot of things but not IP ADDRESS, all action auditing values named in the first link were set up.
I can't understand why IBM people will allow an IP connection through telnet or ftp and won't log it completely and correctly.

We are talking about a $100.000 piece of equipment that won't log the ip from where the connection is stablished!

Is there a CL program or any other way to achieve this?

Thanks in advance for all your suggestions,

Pancho

Pancho,

If you use the command WRKREGINF, find the exit point QIBM_QTG_DEVINIT. This is the exit point called for Telnet access. You can write a CL or RPG program to check the incoming IP address and do something with it.

If found an example of code by searching with QIBM_QTG_DEVINIT
 over the web. See it there :http://archive.midrange.com/midrange-l/200103/msg00068.html

Lewis
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

I would strongly recommend NOT to write an exit program for QIBM_QTG_DEVINIT unless you are certain that you understand the risks.

The exit program outputs a parameter to Allow/Reject the connection. You cannot simply default to either value -- that can effectively allow everyone in the world to connect or block everyone from connecting. Neither alternative is very wise. The program MUST analyze the incoming parameter values and make correct decisions. For example, if you don't understand all of the implications of the "Client password validated" field and how it relates to other values and to various clients, then you should not be assigning the exit program.

...an old as400 (V4R3 OS I think)

As far as V4R3 journalling goes, I don't have many ways to comment. That's so out of date that it's not relevant to anything today. Job IP addressing was made available through journal entries in V5R2, which is also obsolete but reasonably similar to the oldest release that is currently supported (V5R4) and which predates the V5R3 referenced in the question.

Tom
Note that many journal entries will not (and can not) have an IP address associated with them. If I submit an RPG program to a batch job queue and it reads through a file updating each record, the RPG program has no IP address. It's not communicating over TCP/IP.

Tom
Hi Pancho,

Just to clarify my point. I never tryed the exit point QIBM_QTG_DEVINIT cause I never had to. I still believe it could be a good solution for monitoring entries. I never had in mind to create a program that will allow or reject the access. For that case, I would probably suggest you to buy a package.

So I still believe this solution is good for monitoring, but for V4R3, I don't know if it was available or not. I will trust Tom on this, he's good advise.

Lewis
I never had in mind to create a program that will allow or reject the access.

Unfortunately, you have no choice. Your program must return either an 'Accept' or a 'Reject'. It's a required output parm value. You could, of course, just let it return whatever happens to be in memory at the time... but I'm pretty sure nobody would like the result.

Tom

Author

Commented:
Thanks for the given help. Unfortunately my question stills remains unanswered.
Why I need is something similar to the who command under Linux

#who
root     pts/0        Nov  4 11:59 (131.107.2.xxx)
root     pts/1        Nov 10 07:14 (200-55-124-xxx.dsl.prima.net.ar)

This is the kind of log I'm looking for (user, terminal, date, host or ip address in any order)

Thanks,

Pancho
Commented:
I don't think you will find one command that will give you that.

Here are two that together will do it.
WRKACTJOB SBS(QINTER)      this ASSUMES that QINTER is where you interactive jobs run.

Under the "Subsystem/Job" column get the indented job name.

Then use DSPDEVD XXX where XXX is that job name.  If that is an IP device it will report the IP address.

If  you wanted to do some programming you could write your own WHO command.  The APIs are all present for you.

Steve Bowdoin
Bowdoin Consulting

Author

Commented:
I will try your suggestion tomorrow and I'll get back to you.

Pancho
This is the kind of log I'm looking for (user, terminal, date, host or ip address in any order)b

Terminal? What if no "terminal" is involved? Are you only interested in transactions out of connections that are made through terminals or workstations? There will be lots of other possibilities that will need to be filtered out. What events are you actually looking for out of a database journal?

Date? Which date? Do you want the date of the transaction or the date of the connection? (Assuming the transaction was through a connection rather than a batch process or other non-TCP/IP job.)

Host/IP address? Since possibly many or most transactions won't have any relationship to TCP/IP, do you only want entries that were initiated over TCP/IP?

You showed a Linux command result. Can you explain precisely what that command is showing and tell us how it relates to database journal entries? I can't see much connection.

Tom

Author

Commented:
Steve, I have just tested your suggestion without luck

                          Visualizar descr dispositivo                 S1032TQM
                                                             24/11/10  09:22:02
 Descripci n de dispositivo . . . . :   TCP005
 Opci n . . . . . . . . . . . . . . :   *BASIC
 Categor a de dispositivo . . . . . :   *DSP

 Clase de dispositivo . . . . . . . :   *VRT
 Tipo de dispositivo  . . . . . . . :   3179
 Modelo de dispositivo  . . . . . . :   2
 En l nea en IPL  . . . . . . . . . :   *NO
 Controlador conectado  . . . . . . :   QVIRCD0001
 Tipo de idioma de teclado  . . . . :   SSE
 Identificador de caracteres  . . . :   695    1145
 Permitir parpadeo cursor . . . . . :   *YES
 Dispositivo de impresi n . . . . . :   PRT005
 Cola de salida . . . . . . . . . . :   *DEV
 Archivo de impresora . . . . . . . :   QSYSPRT
   Biblioteca . . . . . . . . . . . :     *LIBL
                                                                         M s...
 Pulse Intro para continuar

 F3=Salir   F11=Visualizar palabras clave   F12=Cancelar

                          Visualizar descr dispositivo                 S1032TQM
                                                             24/11/10  09:22:02
 Descripci n de dispositivo . . . . :   TCP005
 Opci n . . . . . . . . . . . . . . :   *BASIC
 Categor a de dispositivo . . . . . :   *DSP

 Nombre ubicaci n dependiente . . . :   *NONE
 Asignado a:
 Nombre de trabajo  . . . . . . . . :   TCP005
   Usuario  . . . . . . . . . . . . :     SOMEUSER
   N mero . . . . . . . . . . . . . :     635378
 Cola de mensajes actual  . . . . . :   QSYSOPR
   Biblioteca . . . . . . . . . . . :     QSYS
 Texto  . . . . . . . . . . . . . . :   Dispositivo creado para S1032TZM.




                                                                          Final
 Pulse Intro para continuar

 F3=Salir   F11=Visualizar palabras clave   F12=Cancelar

Open in new window


The system won't show the ip address of the device/terminal.

Tom, I'm only looking for the interactive sessions info. Not the whole list of processes.
The who command just shows the user logged in the system, the name of the terminal, the date including hour of the session start and the ip address from which the connection was made.

I have a lot of information about jobs but not the IP address from which the jobs were started, even though all connections are made through the IP protocol.

I gues I will have to live with this limitation. Lucky for us God invented all the Un*x like OS.

Pancho

Commented:
Pancho.

In the New Testament...  it looks like this.

I am curious,  can you tell us the OS release?

Steve

 
Display Device Description                  S066C274 
                                                             11/24/10  07:56:44 
 Device description . . . . . . . . :   ECM031B                                 
 Option . . . . . . . . . . . . . . :   *BASIC                                  
 Category of device . . . . . . . . :   *DSP                                    
                                                                                
 Device class . . . . . . . . . . . :   *VRT                                    
 Device type  . . . . . . . . . . . :   3477                                    
 Device model . . . . . . . . . . . :   FC                                      
 Internet address . . . . . . . . . :   192.168.1.184                           
 Online at IPL  . . . . . . . . . . :   *NO                                     
 Attached controller  . . . . . . . :   QVIRCD0003                              
 Keyboard language type . . . . . . :   USB                                     
 Character identifier . . . . . . . :   697    37                               
 Allow blinking cursor  . . . . . . :   *YES                                    
 Print device . . . . . . . . . . . :   *SYSVAL                                 
 Output queue . . . . . . . . . . . :   *DEV                                    
                                                                                
                                                                        More... 
 Press Enter to continue                                                        
                                                                                
 F3=Exit   F11=Display keywords   F12=Cancel

Open in new window

Author

Commented:
Steve, the OS is V5R3

Pancho

Commented:
Pancho

I checked a 4.5 machine.  It displays like yours.

7.1 is the current release.

You are probably stuck on a 170 with a 8 gig load source.

Steve

Author

Commented:
"You are probably stuck on a 170 with a 8 gig load source."

Please explain. Are you refering to the iSeries model?

Commented:
Excuse me.  I got my versions wrong.

Yes. 170 refers to a type 9406 model 170.

There is a point where IBM stops providing new OS for old boxes.  I mis-typed.  I thought you might be on one of those.  There is a Model 170 that had a small load source.  I dont remember the exact point, but you cant upgrade them to the current release.

Upgrading to 6.1 may be an option.  You will need more reasons than displaying the IP for that.  I dont have all the info in front of me, but, on my current CPU 6.1 is a far as it can go.  If you had a 600 then 5.1 is as far as that one can go.

5.4.5 displays the IP.

Steve
hi
here is a quick program to get the IP address. works from V2R3



H DftActGrp(*No)                                                    
H ActGrp(*Caller)                                                   
                                                                    
D ErrorDS         ds                  Qualified Inz                 
D   BytesProv                   10i 0 inz(%size(ErrorDS))           
D   BytesAvail                  10i 0                               
D   MsgId                        7                                  
D   Spare                        1                                  
D   text                        52                                  
                                                                    
D RtvDevd         Pr                  Extpgm('QDCRDEVD')            
D pRecVar                             Like(Recvar)                  
D PLen                          10i 0 Const                         
D Pormat                         8    Const                         
D PDev                          10    Const                         
D Perror                              Like(Errords)                 
                                                                    
D Recvar          ds          1024                                  
D  IPAdr                        15    Overlay(Recvar:878)           
d Format          s             10    Inz('DEVD0600')                         
                                                                              
                                                                              
C     *entry        Plist                                                     
C                   Parm                    devd             10               
C                   Parm                    P_Ip             15               
                                                                              
 /Free                                                                        
      *inlr = *on;                                                            
      RtvDevd(Recvar : %Size(Recvar) :Format:Devd:errords) ;                  
      p_IP=IpAdr ;                                                            
      return ;                                                                
 /end-free                                                                    

Open in new window

Be aware that V5R3 and later bring IPv6. I think there are only something less than a dozen IPv4 address blocks left after two more were handed out a week or so ago. Soon, many new addresses will have to be IPv6 -- no more postponements. Review format 'DEVD0600' in the i 6.1 Info Center for changes. Some changes exist earlier but aren't necessarily documented except in PTF cover letters.

(Déjà Y2K?)

Tom

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial