Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dancarlosgabriel/9ad1ff446d25d442afcad1c2b8b5e2f3 to your computer and use it in GitHub Desktop.
Save dancarlosgabriel/9ad1ff446d25d442afcad1c2b8b5e2f3 to your computer and use it in GitHub Desktop.
I've been getting asked lots of good questions about how to configure and monitor the Audit Journal. Guess what? #SQLcandoit
--
-- Subject: Audit Journal Management
-- Author: Scott Forstie
-- Date : March, 2023
-- Features Used : This Gist uses qsys2.security_info, qsys2.journal_info, qsys2.journal_receiver_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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment