Journal Forensics 101

March 6, 2013 Steven Wolk

Note: The code accompanying this article is available for download here.

A good friend of mine, who also happens to be president of our local user’s group, had been encouraging me to present a session at one of their meetings. I resisted for some time, but finally decided this was an exciting prospect. The topic I would choose became clear to me almost immediately. We use journaling in my shop for so many purposes that we simply couldn’t survive without it. I’ve always been very interested in journaling, especially in applications of the technology to forensics and recovery.

It’s Fun To Be A Detective

Using journal data to determine how something happened or how someone was able to accomplish something can be great fun. Whether you like to think of yourself as Bond getting his latest gadgets from Q, Phelps receiving his mission assignment from a device that will self-destruct in five seconds, or even Agent 86 talking into a shoe-phone, you need good data to solve the mystery. The best source of this data for an IBM i IT sleuth frequently happens to be the data in journals.

Naturally there are many other uses for journal data, even if they may not be as much fun. For example, if you need to roll back and undo the results of a job run amok, journals can be your best friend.

What all of these uses have in common is that a technique is required for dumping the journal data and converting it into some usable form. This was exactly why I was concerned about giving the presentation. I had long ago developed tools for doing just this–dumping a journal and then converting the output. All the developers in my shop still used these tools frequently, but the tools themselves were antiquated. They relied on manipulating DDS. Now don’t get me wrong, I’ve always felt that DDS was an elegant interface to define data, but there’s a certain stigma attached to it now. Presenting my DDS-based journal forensics tools to the world felt a bit like airing my dirty laundry for all to see. It became apparent that it was time to redevelop these utilities using more modern techniques.

A Review Of The Basics

Before digging into the details of how these commands work, and how they can make your life much easier, a review of the basics of dumping a journal is in order:

1. Gather information. You will need to know:

a. The file whose journal entries you need to dump.

b. The journal to which this file is journaled. To determine this, perform a DSPFD on the physical file.

c. The journal receivers needed, based on date/time range. Use the WRKJRNA command with F15 to see the list of journal receivers attached to a journal, along with the attach and detach date and time for each receiver.

2. Dump the journal to an output file. Here is some sample code:

DSPJRN JRN(MYJRNLIB/MYJOURNAL)
       FILE((MYDBLIB/MYDBFILE MYMEMBER))
       RCVRNG(MYJRNLIB/STR_RCVR MYJRNLIB/END_RCVR)
       OUTPUT(*OUTFILE)
       OUTFILE(QTEMP/TMPJRNOUT)
       ENTDTALEN(*CALC)

This produces an output file containing all the journal activity fields from record format QJORDJE along with a single field containing the database data. The length of this field is determined automatically by the ENTDTALEN(*CALC). This is known as the Entry Specific Data. It’s very important to specify ENTDTALEN(*CALC), or else the data may be truncated.

3. Break the Entry Specific Data (ESD) into discrete fields. The ESD is a hex representation of a record format. To make it usable:

a. Use DDS or SQL to create a new PF containing the journal activity fields and the database fields.

b. Map the data from the DSPJRN output file to the new PF created in the step above using CPYF FMTOPT(*NOCHK). This maps the ESD to the discrete database fields.

Regardless of the technique used to dump the contents of journals, there are certain prerequisites to consider. You can tune two settings to optimize your forensics ability.

1. STRJRNPF IMAGES(*BOTH)–Use the STRJRNPF command to start journaling on a database file. All records added to and deleted from the file will be recorded in the journal receiver. For updated records, you have a choice. You may record just the updated record (the “after image”), or you may record the original record (the “before image”) as well. Having an audit of what the record looked like both before as well as after the update allows you to quickly and easily determine what fields on the record were changed. Without this, you might need to restore a backup of the file and/or dump journals for an extended time period to learn what the original record looked like. If you have the disk space to spare, recording IMAGES(*BOTH) makes a lot of sense.

2. CRTJRN MINENTDTA(*NONE)–The Minimize Entry Data parameter also pertains only to updates, and provides a useful feature in that it saves space in journal receivers by only recording the changed bytes. However, by not recording the entire record, your forensics work is much more difficult. If disk space is not too much of a problem, specify MINENTDTA(*NONE).

Tools Make It Easy

The tools that I created long ago used DDS to create the new physical file. This required that the original file be created with DDS, and that the DDS must be available on the same system I wanted to dump journals on, which was not always the case. I knew that it was time to retire these old tools and develop something more modern and flexible, and so the DMPJRN and CVTJRNDTA commands were born. See Figures 1 and 2 for screen shots of the prompted commands.

Figure 1. (Click graphic to enlarge.)

DMPJRN begins by reconstructing the DSPJRN command based on the parameters specified. It then passes the reconstructed command to QCMDEXC. This creates a temporary output file from the DSPJRN command, which is then processed by the CVTJRNDTA command.

Figure 2. (Click graphic to enlarge.)

CVTJRNDTA creates the final output file using an SQL join between the DSPJRN temporary output file and the original journaled PF. While CVTJRNDTA is used within DMPJRN, it was intentionally built as a separate command. If you needed more flexible selection criteria when dumping the journal than the DMPJRN command currently supports, you can run the DSPJRN and CVTJRNDTA separately.

The most important part of the CVTJRNDTA command is the SQL statement that creates the output file:

sqlcmd='create table ' + %trim(outlib) + '/' + %trim(outfil) +
       ' as (select joentl, joseqn, jocode, joentt, jodate, +
       jotime, jojob, jouser, jonbr, jopgm, joobj, jolib, +
       jombr, joctrr, joflag, joccid, joincdat, jominesd, +
       jores, ' + %trim(JrnFil) + '.* from ' +
       %trim(dsplib) + '/' + %trim(dspfil) + ', ' +
       %trim(jrnlib) + '/' + %trim(jrnfil) +
       ') with no data';

This SQL statement creates the new output file as a table by joining together the DSPJRN output file with the user’s original journaled file. The journal activity fields are pulled from the DSPJRN output file, including the date and time of the activity, the job information, and more. The database fields are pulled from the original journaled file. This new file contains the best of both worlds: the journal audit fields and the discrete database fields. CVTJRNDTA then copies the data from the DSPJRN temporary output file to the newly created file.

An Example

Here’s a practical example that demonstrates the power of these commands. Try it on your own system.

1. Before you can get your feet wet trying the DMPJRN and CVTJRNDTA commands, you must set up a test environment. If you haven’t already done so, create a library called DMPJRN and restore the downloadable code into it.

2. Create a journal receiver.

CRTJRNRCV JRNRCV(MYLIB/JRNRCV0001)

3. Create a journal.

CRTJRN JRN(MYLIB/JOURNAL) JRNRCV(MYLIB/JRNRCV0001) MINENTDTA(*NONE)

4. Use SQL to create a database table (physical file).

create table customers
  (CustNbr decimal(5, 0) not null with default,
   CustName char(20) not null with default,
   CrdLimit decimal(7,0) not null with default)

5. Start journaling the file to the journal.

STRJRNPF FILE(MYLIB/CUSTOMERS) JRN(MYLIB/JOURNAL)
            IMAGES(*BOTH) OMTJRNE(*OPNCLO)

6. Use SQL to perform some database activities. Follow these steps:

a. Create some customer records.

insert into customers values(1,'John Smith',10000)
insert into customers values(2,'Mary Jones',25000)
insert into customers values(3,'Bob Jensen',7500)

b. Increase all customer’s credit limits by 10 percent.

update customers set CrdLimit = CrdLimit * 1.1

c. Delete customer number 2.

delete from customers where CustNbr=2

A Little Detective Work

Suppose the accounts receivable department asked for details about a customer named Mary Jones. Their paper-based records indicated that she had a $25,000 credit limit. However, she had somehow charged $27,000 in orders. Furthermore, your customer inquiry program no longer showed any record for Mary Jones.

Let’s take a look at what a journal can show by using the DMPJRN command.

Figure 3. (Click graphic to enlarge.)

The DMPJRN command illustrated above creates output file JRNCUST in library MYLIB. Loading this file into Excel helps better visualize a subset of the available fields in MYLIB/JRNCUST.

(Click graphic to enlarge.)

Let’s review some of the columns shown in this example:

JOSEQN–Journal entry sequence number. Assigned by the system, counts sequentially. Note that there may be gaps, as we’re only interested in certain types of journal entries.

JOENTT–Journal entry type. For the purposes of this example:

PT = Record added to file.
UB = Record updated. This is the “before” image, or how the record looked prior to the update.
UP = Record updated. This is the “after” image, or how the record looked following the update.
DL = Record deleted from file.

JODATE/JOTIME–The date and time of the file I/O operation.

JOJOB, JOUSER, JONBR–The job, user, and job number of the job that performed the I/O operation.

CUSTNBR, CUSTNAME, CRDLIMIT–The fields in database file CUSTOMERS.

These journal entries match the I/O operations we performed earlier using SQL.

Entries 3, 4, and 5 are type PT, and show the initial three records being added to the file.
Entries 6 and 7 show an update for customer 1, John Smith. Entry 6 shows a type of UB, meaning this is the before image. Likewise, entry 7 shows a type of UP, indicating that this is the after image. Comparing the three fields in the original file, it becomes apparent that the only field that changed was the credit line, reflecting an increase from 10,000 to 11,000.
Entries 8, 9, 10, and 11 are similar to entries 6 and 7 in that they reflect the before and after images of credit line for Mary Jones and Bob Jensen.
Entry 12 is a type DL, meaning the record was deleted. Which record was deleted? The journal shows that customer 2, Mary Jones, was deleted. It also indicates that she had a credit line of 27,500 at the time her customer record was deleted.

Mystery solved! We can clearly see that Mary Jones’ credit limit was increased from 25,000 to 27,500, and also who deleted her customer record.

The Mystery Is Solved!

You can now easily track the who, what, when, where, and how for your data using your journals and the DMPJRN and CVTJRNDTA commands! I encourage you to run these commands against your own journals and see what you can find. You just might be surprised at how useful the results can be.

Steven Wolk is the CTO for PC Richard & Son, an over 100-year-old family owned and operated chain of 66 appliance and electronics superstores located throughout New York, New Jersey, Connecticut, and Pennsylvania. Steve is looking forward to presenting a very unique session on his DMPJRN and CVTJRNDTA commands at the COMMON 2013 Annual Meeting and Exposition in Austin, Texas. If you enjoyed this article, please attend his session, as there will be guaranteed fun and surprises.

When Who Did What

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot