02
Sat, Nov
2 New Articles

TechTip: The IBM i Audit Journal Detective

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

 

Use the audit journal and SQL tools to determine what job is accessing an old program.

 

Every whodunnit requires a good detective to solve the case. The IBM i has a built-in detective called security auditing that can be used to help solve many mysteries. Say you're planning to remove a year-old deprecated library from the IBM i when you notice the LAST USED date on a program object (or two) in the library was relatively and unexpectedly recent. At this point, it's time to call in the detective to assist.

 

Of course, you want to verify nothing is using the programs before removing the library, so now you need to investigate what jobs are still using them. Modern systems initiate jobs from sources such as a 5250 app, the job scheduler, client/server applications, web applications, power users using Excel, etc. Therefore, it's increasingly difficult to determine where a stale library list might still be in use or whether there's a hard code somewhere that's causing an old copy of a program to run.

 

So how do you find out who or what is using the program? One way is to delete the old library and see what jobs blow sky high!

 

Another option is to use the auditing journal to create journal entries every time a job calls the program(s) in question. Generally, this journal is supposed to be used for security-related items, but in this case it works to aid with finding what is still using an old program. I see no problem with using the auditing journal for this purpose for short periods of time. One thing you don't want to do is use the auditing journal for auditing data transactions!

 

The audit journal is a special journal called QSYS/QAUDJRN. If this object is not on your system, then you should follow these steps to create it and to start audit journaling. For information about managing the audit journal, see this link.

 

As part of your setup, make sure that the system value QAUDCTL has been assigned *OBJAUD. If you're worried about performance, the amount of overhead this journaling will impose is only as great as what it's asked to record, which in this case is very little.

 

Once auditing is set up and enabled, you can use the following steps to trace all executions of program OLDLIB/MYPGM:

 

CHGOBJAUD OBJ(OLDLIB/MYPGM) OBJTYPE(*PGM) OBJAUD(*ALL)

 

The Change Object Auditing (CHGOBJAUD) command instructs the IBM i that a particular object (in the case of a program) should be audited when it is called.

 

If the program has simple parameters, you can test whether auditing is active by calling the program yourself from the command line:

 

CALL OLDLIB/MYPGM

 

Once something calls the program, use the Display Journal (DSPJRN) command to review the entries recorded in the audit journal. In this case, the ZR journal entry type (object read access) and journal code T (audit trail entry) can be specified to filter through the potentially many journal entries. Here is the command to show the job(s) that accessed the program:

 

DSPJRN JRN(QAUDJRN) JRNCDE((T)) ENTTYP(ZR)

 

The initial screen looks like the text below and shows a summary of the activity (your screen may show more activity depending on what objects you have audited):

 

                           Display Journal Entries

 

Journal . . . . . . :   QAUDJRN         Library . . . . . . :   QSYS

Largest sequence number on this screen . . . . . . : 00000000000000000005

Type options, press Enter.

5=Display entire entry

 

 

Opt   Sequence Code Type Object    Library     Job         Time    

             4   T     ZR                           QPADEV0001 12:15:01

             5   T     ZR                           QZDASOINIT 12:17:39

 

In this case, the QPADEV0001 job name indicates the program was called in a 5250 session. The QZDASOINIT job name in the second audit entry means the program was accessed through a database host server job (ODBC, OLE DB, JDBC, .NET managed provider, etc.). If you have many objects being audited at once, you may have to look through each entry to find out which object the entry is auditing.

 

Put a 5 next to the job audit entry you want to review (for this example, I chose QZDASOINIT) and then press F10 to display the journal entry details. The extended screen looks like this:

 

                       Display Journal Entry Details                

                                                                    

Journal . . . . . . :   QAUDJRN         Library . . . . . . :   QSYS

 

Sequence . . . . . . :   5

Code . . . . . . . . :   T - Audit trail entry

Type . . . . . . . . :   ZR - Object read access

 

Object . . . . . . . :

Type . . . . . . . :

Date . . . . . . . . :   07/31/13

Time . . . . . . . . :   12:17:39

Flag . . . . . . . . :   0

Count/RRN . . . . . :   0

Commit cycle ID . . :   0

Nested commit level :   0

Job . . . . . . . . :   503155/QUSER/QZDASOINIT

User profile . . . . :   MIKE

Ignore APY/RMV . . . :   No

Ref constraint . . . :   No

Trigger . . . . . . :   No

Program . . . . . . :   QZDASOINIT

Library . . . . . :     QSYS

ASP device . . . . :     *SYSBAS

System sequence . . :   11108041005439668225

Thread identifier . :   0000000000000001

Receiver . . . . . . :   AUDRCV0005

Library . . . . . :     DEV

ASP device . . . . :     *SYSBAS

Journal identifier . :   X'00000000000000000000'

Remote address . . . :   172.5.17.85

Address family . . . :   IPv4

Remote port . . . . :   50616

System name . . . . :   V144123

Arm number . . . . . :   1      

Logical unit of work :   *OMITTED

Transaction ID . . . :   *OMITTED

 

I've highlighted information I feel may be useful for figuring out what accessed the program: the date/time, the job name, the user profile, the calling program (usefulness depends on the interface the job is run from), the remote address and port (what computer initiated the request). This should help hunt down who/what is running the program. If it's a 5250 job name and user, then it's relatively easy to find.

 

If the job is something like QZDASOINIT and/or the user is generic, then the job's origin may not be so easy to identify by the audit journal alone. In this case, a database monitor can help by acting as the audit journal's sidekick. If you're unfamiliar with the database monitor, it is a tool that records SQL statements submitted to DB2 for i. You can set up a database monitor using the System i Access GUI or the green-screen Start Database Monitor (STRDBMON) command. Further, database analysis tools are available to help you sort out the information collected by the monitor.

 

If OLDLIB/MYPGM is called using SQL, the database monitor can track it and supply a wealth of information about the job that called the program (similar to what the audit journal does but with the additional benefit of the client registers being populated). Using the database analysis tools in System i Navigator, you can filter all SQL statements recorded in a trace by the program name or the name of an external procedure or function defined for the program. Sometimes information in the ApplicationName client register will return the name of a specific Windows .EXE file submitting the data requests. Otherwise, you just hope that a developer populated these registers with something useful.

 

With respect to a database job running the old program, one other thing that can be helpful is to look in the SYSROUTINES catalog view for external references to the program name. For example, what if SQL external procedure NEWLIB/MYPGM mistakenly referenced the old OLDLIB/MYPGM program? Example:

 

CREATE PROCEDURE NEWLIB.MYPGM

EXTERNAL NAME 'OLDLIB/MYPGM'

LANGUAGE RPGLE;

 

If this library mismatch happens when defining a procedure (or function), the following SQL CALLs will result in calling the OLDLIB version of the RPG program even though the NEWLIB SCHEMA seems to indicate otherwise:

 

CALL NEWLIB.MYPGM;

 

or

 

SET PATH=NEWLIB;

CALL MYPGM;

 

A query can check to see if there is a mismatch between the library specified in the routine's external name and the routine's schema as follows:

 

WITH BASE_DATA AS (

SELECT S.*,POSITION('/' IN EXTERNAL_NAME) AS LIB_DELIMITER

FROM QSYS2.SYSROUTINES S

)

SELECT *

FROM BASE_DATA

WHERE LIB_DELIMITER>1

   AND LEFT(EXTERNAL_NAME,LIB_DELIMITER-1)<>ROUTINE_SCHEMA

   AND SPECIFIC_SCHEMA NOT LIKE 'SYS%'

   AND SPECIFIC_SCHEMA NOT LIKE 'SQLJ'

   AND (SPECIFIC_SCHEMA NOT LIKE 'Q%' OR SPECIFIC_SCHEMA='QGPL')

 

The system catalog views can help hunt down almost anything!

 

Once you've located the culprit and you're done auditing the object, simply shut off the auditing for the object:

 

CHGOBJAUD OBJ(OLDLIB/MYPGM) OBJTYPE(*PGM) OBJAUD(*NONE)

 

The audit journal provides a great deal of information that can be used to trace what is accessing a deprecated program. When necessary, the database monitor and SQL catalogs can also help. Use these tools to hunt down those elusive jobs that have outdated library and program references. Incidentally, the audit journal can track usage of other object types such as data areas, data queues, and user spaces, to name a few.

 

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: