SOX monitoring and journal interpreter

We were acquired by a Japanese company and now have to adhere to certain practices that we refer to as JSOX. We now have all kinds of restrictions and have had to do some development to satisfy the JSOX auditors. For example (items with asterisks required internal development):

Most of our requests flow through tickets in a service desk application.
Progamming requests have to authorized by the I.T. manager before a programmer can work on them (except production support).
*Any changes made to a program have to be documented (even recompiles) in the header and changed lines have to be noted or the existing production program has to be archived before the changes are implemented.
All development has to be tested and documented by the developer before making it accessible to users for testing.
All development has to be approved in the ticket by the users before moving it to production.
*All objects created or modified in production are captured and recorded in a log file (may use exit programs in the CRTxxxxxx commands?).
All changes to production data are supposed to be done through approved menu options or must be documented and attached to the ticket (e.g. DBU audit logs).
*Some of our key files have trigger programs attached that capture changes to defined fields in the file.
*All menu access maintenance is logged.
Menu access is regularly reviewed by the process owners.
*We have daily, monthly, and quarterly check lists in the service desk that have to monitored on a timely basis. The daily checklist is populated with reports like QSYSOPR error messages and job scheduler errors.

I have to smile though when people here suggest "just convert the program to RPGLE". With all the restrictions in our environment, that can be a timely process.

----------------------------------------------------------------------

message: 1
date: Fri, 20 Oct 2017 08:25:05 -0400
from: Darren Strong <darren@xxxxxxxxx>
subject: [WDSCI-L] SOX monitoring and journal interpreter

Thanks to SOX we're asked to develop a system to monitor activity by
developers on the production system (An IBM i). First, has anyone else
done something like this, and in an overview, how did you do it?

For my attempt, I'm summarizing file journal entries based on some
criteria, like user, and generating a summary report/spreadsheet with a
concatenated key. Then from that, I want to allow an auditor to drill into
that summary to get the detail of file changes. For now, I'm copy/pasting
this "key" into another report program that is going to report change by
change, what happened in that file for that user, job, etc.

Anyway, I'm using the DISPLAY_JOURNAL SQL table function, and I have this
huge and varying format ENTRY_DATA column. This is where the actual file
data is. I'd like to split this into the file's columns on the
spreadsheet, which I could do if it was all character data, but I'm having
trouble figuring out how to split numerics of varying size and format into
something in an RPG variable. If I use %subst to break a chunk of the
buffer out, what kind of field would I feed that into, because the
datatypes will vary so much?


___________________________________
Darren Strong
Dekko