IBM Support

How to extract data from the History Log and Audit Journal for a Syslog Server

Question & Answer


Question

How to extract data from the History Log and Audit Journal for a Syslog Server.

Answer

With the release of IBM i 7.3 Db2 for i PTF Group level 7 and IBM i 7.2 Db2 for i PTF Group level 19 on October 27, 2017, IBM i now has methods to generate Syslog formatted messages for both the Audit Journal (QAUDJRN) and the History Log (QHST).
The DISPLAY_JOURNAL table function can be used to extract data from the QAUDJRN audit journal and the HISTORY_LOG_INFO table function can be used to extract data from the History Log.
Authorization for HISTORY_LOG_INFO table function: 
  • No authorization needed.
Authorization for DISPLAY_JOURNAL table functions:
  • You must have *USE authority to the journal and to all requested journal receivers.
  • *OBJEXIST authority is required to the journal if object-name is omitted or if object-name specifies an object that no longer exists.
  • If object-name is *ALL, you must be authorized to every object associated with a journal entry.
Create a file with History Log Information:
Using the RUN SQL Scripts tool, run the following SQL Statement:
  
  CREATE TABLE V6CASTIL/SYSLOGHS AS
            (SELECT syslog_facility,
                    syslog_severity,
                    syslog_event
                    FROM TABLE (
                            QSYS2.HISTORY_LOG_INFO(START_TIME => CURRENT DATE, GENERATE_SYSLOG => 'RFC3164')
                        ))
            WITH DATA

 
Note: This SQL Statement might not work on the STRSQL command line interface if your job has a different CCSID than the code page as specified on the 5250 Emulation Session.
Note 2: Modify the time and date on the SQL to the wanted one.
image 4005
The command creates a database file in library V6CASTIL called SYSLOGHS that contains the contents of the history log for the current date.  The data is formatted in Syslog header type RFC3164.
To display the contents of the file, run the following SQL command:
  SELECT * FROM V6CASTIL/SYSLOGHS 
image 4006
The command displays the contents database file SYSLOGHS in library V6CASTIL.
Create a file with Audit Data Information:
Using the RUN SQL Scripts tool, run the following SQL Statement:
  
  CREATE TABLE V6CASTIL/SYSLOGAUD AS
            (SELECT syslog_facility,
                    syslog_severity,
                    syslog_event
                    FROM TABLE (
                            QSYS2.DISPLAY_JOURNAL(
                                'QSYS',
                                'QAUDJRN', STARTING_RECEIVER_NAME => '*CURCHAIN', 
                                STARTING_TIMESTAMP => '2021-03-22 00:00:01',
                                GENERATE_SYSLOG => 'RFC5424')
                        ) AS x
                    WHERE syslog_event IS NOT NULL)
            WITH DATA
 
Note: This SQL Statement might not work on the STRSQL command line interface if your job has a different CCSID than the code page as specified on the 5250 Emulation Session.
Note 2: Modify the time and date on the SQL to the wanted one.
image 8850
The command creates a database file in library V6CASTIL called SYSLOGAUD that contains the contents of the audit journal for the current date.  The data is formatted in Syslog header type RFC5424.
To display the contents of the file, run the following SQL command:
  SELECT * FROM V6CASTIL/SYSLOGAUD 
image 4008
The command displays the contents database file SYSLOGAUD in library V6CASTIL.
More Information on the DISPLAY_JOURNAL table function can be found:
More Information on the HISTORY_LOG_INFO table function can be found:

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHyAAM","label":"Security"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2;7.3;7.4","Line of Business":{"code":"LOB57","label":"Power"}}]

Document Information

Modified date:
22 March 2021

UID

ibm16220292

Page Feedback