VMS Tech Demo 7 - Audit journal reading and reporting

Content:

1. Introduction
2. Setup
3. Reading and storing
4. Simple reports
5. Real reports
6. Extending

Introduction:

The question about how to read the audit journal came up and I tried to put something together using a bunch of existing pieces.

It can be useful as an example of how to read the audit journal.

But it can also be useful as an example of how more general to work with binary files, relational databases and reporting tools. Including how to generate PDF files on VMS.

All the code is tested as working on VMS Alpha. All the code is available for download. But to get it working on your VMS system may still take some effort and require some knowledge about involved technologies.

Setup:

The data flow will look like:

Everything will be build using Java technology.

For reading the audit journal we will use the Record library.

For storing in database we will use JPA.

For scripting we will use Jython (JVM Python).

For reporting (generate PDF file) we will use JasperReports,

What you will need is:

• a SECUTITY.AUDIT$JOURNAL file from a VMS system
• a system to run all this stuff on - it can be a VMS system (and I have run all the examples on a VMS system) but it can also be a Linux or Windows system
• Java (1.5+ is fine, so it does run on VMS Alpha!)
• Jython (*)
• Record library
• VMS Audit library
• JasperReports (*)
• some other Java libraries (*)

*) the easiest way to get all this is to get a copy of my javalib bundle. Note that this is all versions that will run on Java 1.5 on VMS Alpha - if you use Java 1.8 on VMS Itanium or Java 8/11/17 on Linux/Windows then you can get newer versions if you want to.

Reading and storing:

The VMS Audit library comes with support for some of the most common/relevant data structures.

If something relevant for you case is missing please get the source kit and extend it to meet your requirements.

Export to XML or JSON:

Export to XML:

$ java "-Xmx512m" -cp .:vmsaudit.jar:record.jar "dk.vajhoej.vms.audit.tools.ToXml" security.audit$journal z.xml

Output fragment:

<audit-journal file='security.audit$journal'>
    ...
    <audit-record type='Audit' sub-type='Startup'>
        <PID>00000403</PID>
        <Username>SYSTEM</Username>
        <TimeStamp>27-Mar-2023 13:46:18</TimeStamp>
        <System>ARNE1</System>
    </audit-record>
    <audit-record type='Audit' sub-type='Shutdown'>
        <PID>00000442</PID>
        <Username>SYSTEM</Username>
        <TimeStamp>27-Mar-2023 17:01:06</TimeStamp>
        <System>ARNE1</System>
    </audit-record>
    <audit-record type='Audit' sub-type='Startup'>
        <PID>00000403</PID>
        <Username>SYSTEM</Username>
        <TimeStamp>28-Mar-2023 03:16:08</TimeStamp>
        <System>ARNE1</System>
    </audit-record>
</audit-journal>

Export to JSON:

$ java "-Xmx512m" -cp .:vmsaudit.jar:record.jar "dk.vajhoej.vms.audit.tools.ToJson" security.audit$journal z.json

Output fragment:

{
    "file": "security.audit$journal",
    "records": [
...
        {
            "type": "Audit",
            "subtype": "Startup",
            "packets": {
                "PID": "00000403",
                "Username": "SYSTEM",
                "TimeStamp": "27-Mar-2023 13:46:18",
                "System": "ARNE1"
            }
        },
        {
            "type": "Audit",
            "subtype": "Shutdown",
            "packets": {
                "PID": "00000442",
                "Username": "SYSTEM",
                "TimeStamp": "27-Mar-2023 17:01:06",
                "System": "ARNE1"
            }
        },
        {
            "type": "Audit",
            "subtype": "Startup",
            "packets": {
                "PID": "00000403",
                "Username": "SYSTEM",
                "TimeStamp": "28-Mar-2023 03:16:08",
                "System": "ARNE1"
            }
        }
    ]
}

Load into raw database tables:

This database model keeps the original record and packet model.

The database tables look like:

SQL creation script:

CREATE TABLE auditrecord (
    arid INTEGER NOT NULL,
    artype VARCHAR(32),
    arsubtype VARCHAR(32),
    PRIMARY KEY(arid)
);
CREATE INDEX ix_artype ON auditrecord(artype);
CREATE INDEX ix_arsubtype ON auditrecord(arsubtype);
CREATE TABLE auditpacket (
    apid INTEGER NOT NULL,
    aprecordid INTEGER NOT NULL,
    apkey VARCHAR(32),
    apvalue VARCHAR(1024),
    PRIMARY KEY(apid)
);
CREATE INDEX ix_aprecordid ON auditpacket(aprecordid);
CREATE INDEX ix_apkey ON auditpacket(apkey);

The data classes look like:

META-INF/persistence.xml:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   ...
   <persistence-unit name="prod_audit_raw">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>dk.vajhoej.vms.audit.db.raw.AuditDbRecord</class>
      <class>dk.vajhoej.vms.audit.db.raw.AuditDbPacket</class>
      <exclude-unlisted-classes/>
      <properties>
          <!--<property name="hibernate.show_sql" value="true"/>-->
          <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
          <property name="hibernate.connection.url" value="jdbc:mysql://localhost/Test"/>
          <property name="hibernate.connection.username" value="root"/>
          <property name="hibernate.connection.password" value=""/>
          <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>
          <property name="hibernate.connection.pool_size" value="1"/>
      </properties>
   </persistence-unit>
   ...
</persistence>

Load:

$ hibpath = "/javalib/antlr-2_7_6.jar:/javalib/cglib-2_2.jar:/javalib/commons-collections-3_1.jar:/javalib/dom4j-1_6_1.jar:/javalib/hibernate-jpa-2_0-api-1_0_0_final.jar:/javalib/hibernate3.jar:/javalib/javassist-3_12_0_ga.jar:/javalib/jta-1_1.jar:/javalib/slf4j-api-1_6_1.jar:/javalib/slf4j-jdk14-1_6_1.jar"
$ pipe mysql -u root "-D" test < audit_raw.sql
$ java "-Xmx512m" -cp .:vmsaudit.jar:record.jar:'hibpath':/javalib/mysql-connector-java-5_1_36-bin.jar "dk.vajhoej.vms.audit.tools.ToDbRaw" security.audit$journal prod_audit_raw

Load into structured database tables:

This database model is more relational and object oriented.

The database tables look like:

SQL creation script:

CREATE TABLE auditevent (
    aeid INTEGER NOT NULL,
    aetype VARCHAR(32),
    aesubtype VARCHAR(32),
    aetimestamp DATETIME,
    PRIMARY KEY(aeid)
);
CREATE INDEX ix_aetype ON auditevent(aetype);
CREATE INDEX ix_aesubtype ON auditevent(aesubtype);
CREATE INDEX ix_aetimestamp ON auditevent(aetimestamp);
CREATE TABLE auditeventlocal (
    aeid INTEGER NOT NULL,
    aelpid CHAR(8),
    aelusername VARCHAR(32),
    aelsystem VARCHAR(32),
    PRIMARY KEY(aeid)
);
CREATE INDEX ix_aelusername ON auditeventlocal(aelusername);
CREATE TABLE auditeventlocalid (
    aeid INTEGER NOT NULL,
    aeliidname VARCHAR(32),
    aeliidvalue VARCHAR(32),
    PRIMARY KEY(aeid)
);
CREATE INDEX ix_aeliidname ON auditeventlocalid(aeliidname);
CREATE TABLE auditeventlocalremote (
    aeid INTEGER NOT NULL,
    aelrremoteusername VARCHAR(32),
    aelrremotesystem VARCHAR(32),
    aelrterminal VARCHAR(255),
    PRIMARY KEY(aeid)
);
CREATE INDEX ix_aelrremoteusername ON auditeventlocalremote(aelrremoteusername);
CREATE INDEX ix_aelrremotesystem ON auditeventlocalremote(aelrremotesystem);
CREATE TABLE auditeventlocalfieldupdates (
    aeid INTEGER NOT NULL,
    aelfusource VARCHAR(32),
    PRIMARY KEY(aeid)
);
CREATE INDEX ix_aelfusource ON auditeventlocalfieldupdates(aelfusource);
CREATE TABLE auditfieldupdate (
    afuid INTEGER NOT NULL,
    afueventid INTEGER NOT NULL,
    afufieldname VARCHAR(32),
    afuoldvalue VARCHAR(255),
    afunewvalue VARCHAR(255),
    PRIMARY KEY(afuid)
);
CREATE INDEX ix_afueventid ON auditfieldupdate(afueventid);
CREATE INDEX ix_afufieldname ON auditfieldupdate(afufieldname);

The data classes look like:

META-INF/persistence.xml:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   ...
   <persistence-unit name="prod_audit_struct">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <class>dk.vajhoej.vms.audit.db.struct.AuditDbEvent</class>
      <class>dk.vajhoej.vms.audit.db.struct.AuditDbEventLocal</class>
      <class>dk.vajhoej.vms.audit.db.struct.AuditDbEventLocalId</class>
      <class>dk.vajhoej.vms.audit.db.struct.AuditDbEventLocalRemote</class>
      <class>dk.vajhoej.vms.audit.db.struct.AuditDbEventLocalFieldUpdates</class>
      <class>dk.vajhoej.vms.audit.db.struct.AuditDbFieldUpdate</class>
      <exclude-unlisted-classes/>
      <properties>
          <!--<property name="hibernate.show_sql" value="true"/>-->
          <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
          <property name="hibernate.connection.url" value="jdbc:mysql://localhost/Test"/>
          <property name="hibernate.connection.username" value="root"/>
          <property name="hibernate.connection.password" value=""/>
          <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>
          <property name="hibernate.connection.pool_size" value="1"/>
      </properties>
   </persistence-unit>
   ...
</persistence>

Load:

$ hibpath = "/javalib/antlr-2_7_6.jar:/javalib/cglib-2_2.jar:/javalib/commons-collections-3_1.jar:/javalib/dom4j-1_6_1.jar:/javalib/hibernate-jpa-2_0-api-1_0_0_final.jar:/javalib/hibernate3.jar:/javalib/javassist-3_12_0_ga.jar:/javalib/jta-1_1.jar:/javalib/slf4j-api-1_6_1.jar:/javalib/slf4j-jdk14-1_6_1.jar"
$ pipe mysql -u root "-D" test < audit_struct.sql
$ java "-Xmx512m" -cp .:vmsaudit.jar:record.jar:'hibpath':/javalib/mysql-connector-java-5_1_36-bin.jar "dk.vajhoej.vms.audit.tools.ToDbStruct" security.audit$journal prod_audit_struct

Simple reports:

For reporting we will use Python.

It got some advantages:

• few lines of code provide a lot of functionality
• pretty standard in the data/reporting world

Some examples will use plain SQL (Python DB API) and some examples will use ORM (JPA).

All examples will be run using Jython (JVM Python). The plain SQL examples could easily be run with the standard Python implementation.

From the raw database tables using DB API:

from java.lang import System

from com.ziclix.python.sql import zxJDBC

t1 = System.currentTimeMillis()
con = zxJDBC.connect('jdbc:mysql://localhost/Test', 'root', '', 'com.mysql.jdbc.Driver')
c = con.cursor()
c.execute("SELECT ar.arid, ar.artype, ar.arsubtype, ap.apkey, ap.apvalue FROM auditrecord ar JOIN auditpacket ap ON ar.arid = ap.apre
cordid WHERE (ar.artype = 'Login failure' OR ar.artype = 'Breakin') ORDER BY ar.arid")
data = c.fetchall()
f = open('z.2', 'w')
lastid = -1
n = 0
for row in data:
    if row[0] != lastid:
        f.write('%s - %s:\n' % (row[1], row[2]))
        lastid = row[0]
        n = n + 1
    if row[3] == 'TimeStamp':
        f.write('  TimeStamp = %s\n' % (row[4]))
    if row[3] == 'RemoteUsername':
        f.write('  RemoteUsername = %s\n' % (row[4]))
    if row[3] == 'RemoteSystem':
        f.write('  RemoteSystem = %s\n' % (row[4]))
    if row[3] == 'Terminal':
        f.write('  Terminal = %s\n' % (row[4]))
f.close()
c.close()
con.close()
t2 = System.currentTimeMillis()
print("logfail - db (raw) - DB API : %d records in %d milliseconds" % (n, t2 - t1))

Run:

$ define/nolog jython_libs "/vmsjavaauditpath/vmsaudit.jar:/javalib/mysql-connector-java-5_1_36-bin.jar"
$ jython logfail_dbraw_dbapi.py

Output fragment:

Login failure - Remote:
  Terminal = TNA2:
  RemoteUsername = TELNET_C0A8008C
  RemoteSystem = arnepc4
  Terminal = _TNA2:
  Terminal = Host: arnepc4 Port: 49323
  TimeStamp = 29-Jan-2023 09:06:04
Login failure - Local:
  Terminal = OPA0:
  Terminal = _OPA0:
  TimeStamp = 06-Mar-2023 15:51:56
Login failure - Remote:
  Terminal = TNA2:
  RemoteUsername = TELNET_C0A8008C
  RemoteSystem = arnepc4
  Terminal = _TNA2:
  Terminal = Host: arnepc4 Port: 61133
  TimeStamp = 07-Mar-2023 13:57:54
Login failure - Local:
  Terminal = OPA0:
  Terminal = _OPA0:
  TimeStamp = 27-Mar-2023 05:09:40

From the raw database tables using JPA:

from java.lang import System
from java.util.logging import Level
from java.util.logging import Logger
from javax.persistence import Persistence

from dk.vajhoej.vms.audit.db.raw import AuditDbRecord

t1 = System.currentTimeMillis()
Logger.getLogger("org.hibernate").setLevel(Level.OFF)
emf = Persistence.createEntityManagerFactory("prod_audit_raw")
em = emf.createEntityManager()
q = em.createQuery("SELECT DISTINCT ar FROM AuditDbRecord AS ar JOIN FETCH ar.packets ap WHERE (ar.type = 'Login failure' OR ar.type
= 'Breakin') ORDER BY ar.id", AuditDbRecord)
res = q.getResultList()
f = open('z.4', 'w')
n = 0
for ar in res:
    f.write('%s - %s:\n' % (ar.type, ar.subType))
    for ap in ar.packets:
        if ap.key == 'TimeStamp':
            f.write('  TimeStamp = %s\n' % (ap.value))
        if ap.key == 'RemoteUsername':
            f.write('  RemoteUsername = %s\n' % (ap.value))
        if ap.key == 'RemoteSystem':
            f.write('  RemoteSystem = %s\n' % (ap.value))
        if ap.key == 'Terminal':
            f.write('  Terminal = %s\n' % (ap.value))
    n = n + 1
f.close()
t2 = System.currentTimeMillis()
print("logfail - db (raw) - JPA : %d records in %d milliseconds" % (n, t2 - t1))

Run:

$ hibpath = "..:/javalib/antlr-2_7_6.jar:/javalib/cglib-2_2.jar:/javalib/commons-collections-3_1.jar:/javalib/dom4j-1_6_1.jar:/javalib/hibernate-jpa-2_0-api-1_0_0_final.jar:/javalib/hibernate3.jar:/javalib/javassist-3_12_0_ga.jar:/javalib/jta-1_1.jar:/javalib/slf4j-api-1_6_1.jar:/javalib/slf4j-jdk14-1_6_1.jar"
$ define/nolog jython_libs "/vmsjavaauditpath/vmsaudit.jar:/javalib/mysql-connector-java-5_1_36-bin.jar"
$ jython_libs_prefix = hibpath + ":"
$ jython logfail_dbraw_jpa.py

Output fragment:

Login failure - Remote:
  TimeStamp = 29-Jan-2023 09:06:04
  Terminal = _TNA2:
  RemoteUsername = TELNET_C0A8008C
  Terminal = Host: arnepc4 Port: 49323
  RemoteSystem = arnepc4
  Terminal = TNA2:
Login failure - Local:
  TimeStamp = 06-Mar-2023 15:51:56
  Terminal = _OPA0:
  Terminal = OPA0:
Login failure - Remote:
  Terminal = Host: arnepc4 Port: 61133
  RemoteSystem = arnepc4
  Terminal = TNA2:
  TimeStamp = 07-Mar-2023 13:57:54
  Terminal = _TNA2:
  RemoteUsername = TELNET_C0A8008C
Login failure - Local:
  Terminal = _OPA0:
  Terminal = OPA0:
  TimeStamp = 27-Mar-2023 05:09:40

From the structured database tables using DB API:

from java.lang import System

from com.ziclix.python.sql import zxJDBC

t1 = System.currentTimeMillis()
con = zxJDBC.connect('jdbc:mysql://localhost/Test', 'root', '', 'com.mysql.jdbc.Driver')
c = con.cursor()
c.execute("SELECT aetype, aesubtype, aetimestamp, aelrremoteusername, aelrremotesystem, aelrterminal FROM (auditevent ae JOIN auditev
entlocal ael ON ae.aeid = ael.aeid) JOIN auditeventlocalremote aelr ON ae.aeid = aelr.aeid WHERE (aetype = 'Login failure' OR aetype
= 'Breakin') ORDER BY ae.aeid")
data = c.fetchall()
f = open('z.3', 'w')
n = 0
for row in data:
    f.write('%s - %s:\n' % (row[0], row[1]))
    f.write('  TimeStamp = %s\n' % (row[2]))
    f.write('  RemoteUsername = %s\n' % (row[3]))
    f.write('  RemoteSystem = %s\n' % (row[4]))
    f.write('  Terminal = %s\n' % (row[5]))
    n = n + 1
f.close()
c.close()
con.close()
t2 = System.currentTimeMillis()
print("logfail - db (struct) - DB API : %d events in %d milliseconds" % (n, t2 - t1))

Run:

$ define/nolog jython_libs "/vmsjavaauditpath/vmsaudit.jar:/javalib/mysql-connector-java-5_1_36-bin.jar"
$ jython logfail_dbstruct_dbapi.py

Output fragment:

Login failure - Remote:
  TimeStamp = 2023-01-29 09:06:04
  RemoteUsername = TELNET_C0A8008C
  RemoteSystem = arnepc4
  Terminal = Host: arnepc4 Port: 49323
Login failure - Local:
  TimeStamp = 2023-03-06 15:51:56
  RemoteUsername = None
  RemoteSystem = None
  Terminal = _OPA0:
Login failure - Remote:
  TimeStamp = 2023-03-07 13:57:54
  RemoteUsername = TELNET_C0A8008C
  RemoteSystem = arnepc4
  Terminal = Host: arnepc4 Port: 61133
Login failure - Local:
  TimeStamp = 2023-03-27 05:09:40
  RemoteUsername = None
  RemoteSystem = None
  Terminal = _OPA0:

From the structured database tables using JPA:

from java.lang import System
from java.util.logging import Level
from java.util.logging import Logger
from javax.persistence import Persistence

from dk.vajhoej.vms.audit.db.struct import AuditDbEventLocalRemote

t1 = System.currentTimeMillis()
Logger.getLogger("org.hibernate").setLevel(Level.OFF)
emf = Persistence.createEntityManagerFactory("prod_audit_struct")
em = emf.createEntityManager()
q = em.createQuery("SELECT ae FROM AuditDbEventLocalRemote AS ae WHERE (ae.type = 'Login failure' OR ae.type = 'Breakin') ORDER BY ae
.id", AuditDbEventLocalRemote)
res = q.getResultList()
f = open('z.5', 'w')
n = 0
for ae in res:
    f.write('%s - %s:\n' % (ae.type, ae.subType))
    f.write('  TimeStamp = %s\n' % (ae.timeStamp))
    f.write('  RemoteUsername = %s\n' % (ae.remoteUsername))
    f.write('  RemoteSystem = %s\n' % (ae.remoteSystem))
    f.write('  Terminal = %s\n' % (ae.terminal))
    n = n + 1
f.close()
t2 = System.currentTimeMillis()
print("logfail - db (struct) - JPA : %d events in %d milliseconds" % (n, t2 - t1))

Run:

$ hibpath = "..:/javalib/antlr-2_7_6.jar:/javalib/cglib-2_2.jar:/javalib/commons-collections-3_1.jar:/javalib/dom4j-1_6_1.jar:/javalib/hibernate-jpa-2_0-api-1_0_0_final.jar:/javalib/hibernate3.jar:/javalib/javassist-3_12_0_ga.jar:/javalib/jta-1_1.jar:/javalib/slf4j-api-1_6_1.jar:/javalib/slf4j-jdk14-1_6_1.jar"
$ define/nolog jython_libs "/vmsjavaauditpath/vmsaudit.jar:/javalib/mysql-connector-java-5_1_36-bin.jar"
$ jython_libs_prefix = hibpath + ":"
$ jython logfail_dbstruct_jpa.py

Output fragment:

Login failure - Remote:
  TimeStamp = 2023-01-29 09:06:04.0
  RemoteUsername = TELNET_C0A8008C
  RemoteSystem = arnepc4
  Terminal = Host: arnepc4 Port: 49323
Login failure - Local:
  TimeStamp = 2023-03-06 15:51:56.0
  RemoteUsername = None
  RemoteSystem = None
  Terminal = _OPA0:
Login failure - Remote:
  TimeStamp = 2023-03-07 13:57:54.0
  RemoteUsername = TELNET_C0A8008C
  RemoteSystem = arnepc4
  Terminal = Host: arnepc4 Port: 61133
Login failure - Local:
  TimeStamp = 2023-03-27 05:09:40.0
  RemoteUsername = None
  RemoteSystem = None
  Terminal = _OPA0:

Real reports:

For real reports we will use JasperReports. JasperReports is maybe sligtly oldfashioned, but it is a powerful reporting tool.

Basically JasperReports take as input:

• a data source typical a relational database
• a report template in XML format typical with embedded SQL statement

and generate a report from that.

Several output formats including PDF and HTML are supported.

This example will generate a PDF report.

Report template logfail.jrxml:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"
              name="logfail" language="groovy" printOrder="Horizontal" pageWidth="600" pageHeight="850" columnWidth="560" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
        <queryString><![CDATA[
                SELECT aetype,aesubtype,aetimestamp,
                       COALESCE(aelrremoteusername,'N/A') AS aelrremoteusername,COALESCE(aelrremotesystem,'N/A') AS aelrremotesystem,COALESCE(aelrterminal,'N/A') AS aelrterminal
                FROM (auditevent ae JOIN auditeventlocal ael ON ae.aeid = ael.aeid)
                                    JOIN auditeventlocalremote aelr ON ae.aeid = aelr.aeid
                WHERE (aetype = 'Login failure' OR aetype = 'Breakin')
                ORDER BY aetimestamp
        ]]></queryString>
        <field name="aetype" class="java.lang.String"/>
        <field name="aesubtype" class="java.lang.String"/>
        <field name="aetimestamp" class="java.sql.Timestamp"/>
        <field name="aelrremoteusername" class="java.lang.String"/>
        <field name="aelrremotesystem" class="java.lang.String"/>
        <field name="aelrterminal" class="java.lang.String"/>
        <title>
                <band height="100">
                        <staticText>
                                <reportElement x="0" y="25" width="600" height="50"/>
                                <textElement textAlignment="Center">
                                        <font size="24"/>
                                </textElement>
                                <text><![CDATA[Login Failure and Breakin Report]]></text>
                        </staticText>
                </band>
        </title>
        <pageHeader>
                <band height="0"/>
        </pageHeader>
        <columnHeader>
                <band height="20">
                        <staticText>
                                <reportElement x="0" y="0" width="150" height="20"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="12"/>
                                </textElement>
                                <text><![CDATA[Event type]]></text>
                        </staticText>
                        <staticText>
                                <reportElement x="150" y="0" width="150" height="20"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="12"/>
                                </textElement>
                                <text><![CDATA[Time]]></text>
                        </staticText>
                        <staticText>
                                <reportElement x="300" y="0" width="250" height="20"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="12"/>
                                </textElement>
                                <text><![CDATA[Source]]></text>
                        </staticText>
                </band>
        </columnHeader>
        <detail>
                <band height="40">
                        <textField>
                                <reportElement x="0" y="0" width="150" height="20"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="10"/>
                                </textElement>
                                <textFieldExpression class="java.lang.String"><![CDATA[$F{aetype} + " - " + $F{aesubtype}]]></textFieldExpression>
                        </textField>
                        <textField>
                                <reportElement x="150" y="0" width="150" height="20"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="10"/>
                                </textElement>
                                <textFieldExpression class="java.lang.String"><![CDATA[dk.vajhoej.vms.audit.Util.t2s($F{aetimestamp})]]></textFieldExpression>
                        </textField>
                        <textField>
                                <reportElement x="300" y="0" width="250" height="20"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="10"/>
                                </textElement>
                                <textFieldExpression class="java.lang.String"><![CDATA[$F{aelrremoteusername} + " " + $F{aelrremotesystem}]]></textFieldExpression>
                        </textField>
                        <textField>
                                <reportElement x="300" y="20" width="250" height="20"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="10"/>
                                </textElement>
                                <textFieldExpression class="java.lang.String"><![CDATA[$F{aelrterminal}]]></textFieldExpression>
                        </textField>
                </band>
        </detail>
        <pageFooter>
                <band height="50">
                        <textField evaluationTime="Report">
                                <reportElement x="0" y="0" width="250" height="25"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="12"/>
                                </textElement>
                                <textFieldExpression><![CDATA["Generated: " + dk.vajhoej.vms.audit.Util.t2s(new java.util.Date())]]></textFieldExpression>
                        </textField>
                        <textField>
                                <reportElement x="250" y="0" width="250" height="25"/>
                                <textElement textAlignment="Right" verticalAlignment="Top">
                                        <font size="12"/>
                                </textElement>
                                <textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER}]]></textFieldExpression>
                        </textField>
                        <textField evaluationTime="Report">
                                <reportElement x="501" y="0" width="100" height="25"/>
                                <textElement textAlignment="Left" verticalAlignment="Top">
                                        <font size="12"/>
                                </textElement>
                                <textFieldExpression class="java.lang.String"><![CDATA[" of " + $V{PAGE_NUMBER}]]></textFieldExpression>
                        </textField>
                </band>
        </pageFooter>
</jasperReport>

Run:

$ jrpath = "/javalib/commons-beanutils-1_8_0.jar:/javalib/commons-collections-3_1.jar:/javalib/commons-digester-1_7.jar:/javalib/commons-logging.jar:/javalib/groovy-all-1_7_5.jar:/javalib/itext-2_1_7.jar:/javalib/jasperreports-3_7_6.jar:/javalib/jcommon-1_0_15.jar:/javalib/jfreechart-1_0_12.jar"
$ java "-Djava.awt.headless=true" "-Xmx512m" -cp .:vmsaudit.jar:'jrpath':/javalib/mysql-connector-java-5_1_36-bin.jar "dk.vajhoej.vms.util.RepGen" "com.mysql.jdbc.Driver" "jdbc:mysql://localhost/Test" "root" "" logfail.jrxml logfail.pdf

Small fragment of generated PDF file:

This example is only showing a tiny fraction of what JasperReports is capable of. Those interested in utilizing JasperReports should study the documentation.

Note that there is absolutely no magic in my RepGen code - it is easy to do the same in any JVM application.

package dk.vajhoej.vms.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;

public class RepGen {
    public static void main(String[] args) {
        try {
            if(args.length >= 6) {
                Class.forName(args[0]);
                Connection con = DriverManager.getConnection(args[1], args[2], args[3]);
                JasperReport rep = JasperCompileManager.compileReport(args[4]);
                JasperPrint prt = JasperFillManager.fillReport(rep, null, con);
                JasperExportManager.exportReportToPdfFile(prt, args[5]);
                con.close();
            } else {
                System.out.printf("Usage: java -cp ... %s <driver-class> <connection-url> <connection-username> <connection-password> <report-template> <output-pdf>\n", RepGen.class.getName());
            }
        } catch(Throwable ex) {
            ex.printStackTrace();
        }
    }
}

Extending:

Adding audit record or packet types for reading audit journal:

It is easy to add to the VMS Audit library.

First get the source code.

The code is available under Apache 2.0 license so it is not required to make any modifications public available, but you may email me a copy of the modifications, so I can merge them into the code base, so that other can benefit from the modifications.

Adding record type:

Process:

1. Verify that the relevant constants already are in AuditType and AuditSubType
2. Add case labels in asString methods in AuditType and AuditSubType

Adding packet type:

Process:

1. Create a new class AuditPacketXxxxxx that extends AuditPacket
2. Add case label in convertSelector method in AuditPackertProvider
3. Add selector subtype in AuditPacket

AuditPacketXxxxxx should look like:

@Struct
public class AuditPacketXxxxxx extends AuditPacket {
    @StructField(n=2,type=FieldType.FIXSTR,length=-1,encoding="ISO-8859-1")
    private String xxxxxx;
    // + getter and setter
    public String getKey() {
        return "Xxxxxx";
    }
    public String getValue() {
        return xxxxxx.trim();
    }
}

or:

@Struct
public class AuditPacketXxxxxx extends AuditPacket {
    @StructField(n=2,type=FieldType.INT4)
    private int xxxxxx;
    // + getter and setter
    public String getKey() {
        return "Xxxxxx";
    }
    public String getValue() {
        return Integer.toString(xxxxxx);
    }
}

Example of the selector handling:

        ...
        case AuditPacketType.NSA$C_PKT_XXXXXX: return 29;
        ...

        ...
        @SubType(value=29,type=AuditPacketXxxxxx.class),
        ...

Adding support in tools:

The tools ToXml, ToJson and ToDbRaw with the raw database tables will automatically pick up the added record and packet support via the packet key value mechanism.

The tool ToDbStruct and and the structured database tables will require new classes and new tables.

Process:

1. Add data class AuditDbEventLocalXxxxxx that extends AuditDbEventLocal
2. Update convert method in AuditDbFactory to return a AuditDbEventLocalXxxxxx when needed
3. Add CREATE TABLE to database SQL creation script

Example of AuditEventDbLocalXxxxxx class:

@Entity
@Table(name="auditeventlocalxxxxxx")
@PrimaryKeyJoinColumn(name="aeid")
public class AuditDbEventLocalXxxxxx extends AuditDbEventLocal {
	private String yyyyyy;
	// + constructors
	@Column(name="aelxyyyyyy")
	public String getYyyyyyy() {
		return yyyyyy;
	}
	public void setYyyyyy(String yyyyyy) {
		this.yyyyyy = yyyyyy;
	}
}

Example of SQL creation script:

CREATE TABLE auditeventlocalxxxxxx (
    aeid INTEGER NOT NULL,
    aelxyyyyyy VARCHAR(255),
    PRIMARY KEY(aeid)
);
CREATE INDEX ix_aelxyyyyyy ON auditeventlocalxxxxxx(aelxyyyyyy);

Article history:

Other articles:

eve See list of all articles here

Comments:

Please send comments to Arne Vajhøj