I've been getting asked lots of good questions about how to configure and monitor the Audit Journal. Guess what? #SQLcandoit

Audit Journal Management.sql

--
-- Subject: Audit Journal Management
-- Author: Scott Forstie
-- Date  : March, 2023
-- Features Used : This Gist uses qsys2.security_info, qsys2.journal_info, qsys2.object_statistics, qsys2.qcmdexc, CTEs, sysibmadm.env_sys_info, and SYSTOOLS.split
--
-- Notes:
-- ===============================================
-- 1) There are many configuration options to consider using when establishing the Audit Journal, this Gist uses SQL to examine some of the most important choices.
-- 2) Its important to have a retention strategy for audit journal - journal receivers
--    a) How many days (or # of receivers) are kept "online"?  This corresponds to the data we can easily query.
--    b) How frequently do we save (and then remove) receivers, moving them to "offline"? 
--

stop;

--
-- How is the Audit Journal (*JRN) configured and storage used detail
--
select JOURNAL_LIBRARY, JOURNAL_NAME,  
       TOTAL_SIZE_JOURNAL_RECEIVERS as TOTAL_SIZE_JOURNAL_RECEIVERS_K, 
       TOTAL_SIZE_JOURNAL_RECEIVERS/1000  as TOTAL_SIZE_JOURNAL_RECEIVERS_M, 
       TOTAL_SIZE_JOURNAL_RECEIVERS/1000000  as TOTAL_SIZE_JOURNAL_RECEIVERS_G, 
       NUMBER_JOURNAL_RECEIVERS, MANAGE_RECEIVER_OPTION, DELETE_RECEIVER_OPTION,
       ATTACHED_JOURNAL_RECEIVER_NAME, ATTACHED_JOURNAL_RECEIVER_LIBRARY 
  from qsys2.journal_info
  where journal_name = 'QAUDJRN' and
        journal_library = 'QSYS';
stop;

--
-- Find all the Audit Journal - journal receivers
--
with attached(jl, jrcv, gen_jrcv) as (
select attached_journal_receiver_library, attached_journal_receiver_name, left(attached_journal_receiver_name, 6) concat '*'
  from qsys2.journal_info
  where journal_name = 'QAUDJRN' and journal_library = 'QSYS'
)
select objname as detached_jrnrcv, a.*
  from attached, table (
      qsys2.object_statistics(jl, '*JRNRCV', gen_jrcv)
    ) as a
    order by objcreated desc;
stop;

--
-- What's the oldest journal receiver?
--
with attached (jl, jrcv, gen_jrcv) as (
    select attached_journal_receiver_library, attached_journal_receiver_name, left(
             attached_journal_receiver_name, 6) concat '*'
      from qsys2.journal_info
      where journal_name = 'QAUDJRN' and
            journal_library = 'QSYS'
  )
  select date(min(objcreated)) as oldest_jrnrcv, sum(objsize) as audjrn_rcv_total_size, 
         VARCHAR_FORMAT(sum(objsize), '999G999G999G999G999G999G999G999')
           as audjrn_rcv_total_size_formatted
    from attached, table (
           qsys2.object_statistics(jl, '*JRNRCV', gen_jrcv)
         );
stop;
 
--
-- How can we delete journal receivers that are older than 7 days?
--
with attached (jl, jrcv, gen_jrcv) as (
    select attached_journal_receiver_library, attached_journal_receiver_name, left(
             attached_journal_receiver_name, 6) concat '*'
      from qsys2.journal_info
      where journal_name = 'QAUDJRN' and
            journal_library = 'QSYS'
  )
  select 'DLTJRNRCV ' concat jl concat '/' concat objname concat' DLTOPT(*IGNINQMSG)' as dltjrnrcv_cmd
    from attached, table (
           qsys2.object_statistics(jl, '*JRNRCV', gen_jrcv)
         ) where objcreated < current timestamp - 7 days;
stop;
 
--
-- How can we delete journal receivers that are older than 7 days?
-- BEWARE... this query will delete journal receivers older than 7 days!
--
with attached (jl, jrcv, gen_jrcv) as (
    select attached_journal_receiver_library, attached_journal_receiver_name, left(
             attached_journal_receiver_name, 6) concat '*'
      from qsys2.journal_info
      where journal_name = 'QAUDJRN' and
            journal_library = 'QSYS'
  )
  select qsys2.qcmdexc('DLTJRNRCV ' concat jl concat '/' concat objname concat' DLTOPT(*IGNINQMSG)') as dltjrnrcv_cmd
    from attached, table (
           qsys2.object_statistics(jl, '*JRNRCV', gen_jrcv)
         ) where objcreated < current timestamp - 7 days;
stop;

--
-- How is my IBM i Audit Journal configured?
--
select AUDIT_JOURNAL_EXISTS, AUDITING_CONTROL, AUDITING_LEVEL, AUDITING_LEVEL_EXTENSION
  from qsys2.security_info;
stop;

--
-- Which auditing options are being used?
-- Note, some of the audit journal option names are 10 characters long, so you cannot count 
-- the auditing level list to be blank separated!
--
select auditing_option
  from qsys2.security_info a, lateral (
         select '*' concat trim(element) as auditing_option
           from table (
               systools.split(input_list => AUDITING_LEVEL, delimiter => '*')
             )
           where length(trim(element)) > 0
       ) b
       order by auditing_option;
stop;

--
-- To see which audit journal entry types correspond to these auditing options, look here:
-- https://www.ibm.com/docs/en/i/7.4?topic=actions-security-auditing-journal-entries
--
stop;